Hi,

I am working on project, and wanted to define a list of variables which
should be called by the macro. Is it possible to define the all
variables in one sheet, say Input, and when macro execute it takes the
necessary values from this sheet. The idea is when we use this Excel
File for other project, I just need to change the variables on this
Input sheet...and the macros remain still valid. Till I used to change
these variables in the macro before running them. Any thoughts how to
do it and how to call these values in Macro.

Many thanks...any help highly appreciated!!!

Regards,
Kishore

The macro can obtain the values of the cells in the worksheet and assign them
to the variables in the macro.

In the example below, a equals to the value of cell A1 in sheet "Input". And
b equals to the value of cell A2. The macro then add up the two values and
display it in a messagebox.

Sub example()
Dim a
Dim b
With Worksheets("Input")
a = .Range("A1").Value
b = .Range("A2").Value
End With
MsgBox a + b
End Sub

Regards,
Edwin Tam

http://www.vonixx.com



"kishore" wrote:

Hi,

I am working on project, and wanted to define a list of variables which
should be called by the macro. Is it possible to define the all
variables in one sheet, say Input, and when macro execute it takes the
necessary values from this sheet. The idea is when we use this Excel
File for other project, I just need to change the variables on this
Input sheet...and the macros remain still valid. Till I used to change
these variables in the macro before running them. Any thoughts how to
do it and how to call these values in Macro.

Many thanks...any help highly appreciated!!!

Regards,
Kishore

call a standard module Globals
Use constands to hold sheet names and properties to retrurn sheet values
eg I have two name dranges, "main.date" and "main.server" on my sheet called
"main"

In the Globals module I have two constands with these two names and read
only properties to fetch them:
Option Explicit
' module: Globals
' Purpose : Return sheet data
Private Const BUSINESS_DATE As String = "main.date"
Private Const SERVER_NAME As String = "main.server"
Public Property Get BusinessDate() As Date
BusinessDate = CDate(Names.Item(BUSINESS_DATE).RefersToRange.Valu e)
End Property
Public Property Get ServerName() As String
ServerName = Names.Item(SERVER_NAME).RefersToRange.Value
End Property

In another module, called Module1, I show how to use the properties:
Option Explicit
Sub demo()
MsgBox "using" & ServerName & " for " & BusinessDate
End Sub
As you can see, properties, like functions, can be used as if they are
variables.

HTH




"kishore" wrote:

Hi,

I am working on project, and wanted to define a list of variables which
should be called by the macro. Is it possible to define the all
variables in one sheet, say Input, and when macro execute it takes the
necessary values from this sheet. The idea is when we use this Excel
File for other project, I just need to change the variables on this
Input sheet...and the macros remain still valid. Till I used to change
these variables in the macro before running them. Any thoughts how to
do it and how to call these values in Macro.

Many thanks...any help highly appreciated!!!

Regards,
Kishore

Thanks Guys, It works!!

Regds