Hi

I have a value in workbook which I would like to use a lot of times
without typing in DIM Var as string then Var =
Sheets("Sheet1").range("A1").value in every single sub that I have.

How could I refer to this figure and save time/ my fingers from typing
exactly the same process?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=553325

if you Dim the variable at the top of the module (in the General section) it
will be visible to all sub/functions in that module, and if you declare it
"Public" it will be visible in the subs of the sheets/workbook, etc.

"funkymonkUK" wrote:


Hi

I have a value in workbook which I would like to use a lot of times
without typing in DIM Var as string then Var =
Sheets("Sheet1").range("A1").value in every single sub that I have.

How could I refer to this figure and save time/ my fingers from typing
exactly the same process?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=553325

You could try:

Public Function ThisValue() as String
ThisValue=Sheets("Sheet1").range("A1").value
End Function

You will still have to type ThisValue may times!

Charlie Wrote:
if you Dim the variable at the top of the module (in the General
section) it
will be visible to all sub/functions in that module, and if you declare
it
"Public" it will be visible in the subs of the sheets/workbook, etc.
[/color]

How do I do the "Public" one?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=553325

i tried putting the following on the top of one of my Modules

Public Const noStores As Integer = Sheets("menu").Range("c7").Value

and within my proceduce

I have

sub test

msgbox noStores
End

but it does not pick up a value for noStores and says Compile error :
constant expression required

where am i going wrong?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=553325

can anybody help me?


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=553325