Creating Global Variables

[vidyanand]

Hello, how can I create variable which can be accessed by all the codes in different workbooks. For example if I declare public function calculating date & now I want to access this variable in different workbook code. When I try to access such variable in different workbook module, VBA has given me compilation error.

 

[vidyanand]

Thanx it works even without Dim mth, yy. Perhaps I should not given path for default Personal workbook. Is this general way to call any macro/ function stored in Personal workbook?

 

[Sahil123]

Hi ZVI - with Public Variables, am i right in saying that All the subs would need to be in 1 module?

If i wanted to put all the public variables in another module to make it look clean and store all variables out the way if you like, i would need to change public to global for other modules to access them?

Thanks

 

[ZVI]

Thanx it works even without Dim mth, yy. Perhaps I should not given path for default Personal workbook. Is this general way to call any macro/ function stored in Personal workbook?

Good to know it works now!
Dim mth, yy is just for good style to always declare variables.
You can even skip workbook's name in Run, but in this case be sure the name of your function is unique and not exists in other workbooks.
And yes Run is general way to call macro from another workbook.

 

[ZVI]

Hi ZVI - with Public Variables, am i right in saying that All the subs would need to be in 1 module?

If i wanted to put all the public variables in another module to make it look clean and store all variables out the way if you like, i would need to change public to global for other modules to access them?

Thanks

Hi Sahil,

No, public variables/Subs/Functions can be stored in different standard modules.
But do not place it into worksheet's module nor in workbook's module (ThisWorkbook) because those are actually class modules and requires full path to their public variables/Subs/Functions.
Saying "standard" I mean those created via VBE - Insert - Module

Regards,

 

[Sahil123]

Hi,

So if i put public x as long in module 1

x in module to will not need to be declared as this will also be long?

 

[ZVI]

Hi,

So if i put public x as long in module 1

x in module to will not need to be declared as this will also be long?

Public variables are declared only in one place with it types.
Don't redeclare it anywhere, just use it in any module of workbook.
And yes, if type of the public variable is declared then variable is always of that type.

 

[DocAElstein]

Hi

Hi,

So if i put public x as long in module 1

x in module to will not need to be declared as this will also be long?

Basically yes, I think so , but i guess ZVI wil confim that.
_ .........................

I am mainly popping by with a couple of comments:

_1)
I was ( am ? ) fully in agreement with this:

.......
How to use global variables:
1. Define global variables only in standard module which is created via VBE - Insert - Module.
In the top line of the created Module1 (but below the Option Explicit) type:
Dim MyGlobalVar1
2. Use it in any module like this:

Sub LetMyGlobalVar()
  MyGlobalVar1 = Now
End Sub
 
Sub GetMyGlobalVar()
  MsgBox MyGlobalVar1
End Sub

_.........In fact i thought i had done that a lot before.

_ But......Today i was playing around with global variable of this nature and experienced problems:
If i follow the instructions of ZVI ( putting something like

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Dim[/color] MyGlobalVar1

in a normal module ) and then in another normal module the other stuff, it does not work!?! ( This puzzles me particularly as if I change to lower case a few letters like so__ Myglobalvar1___ , then VBA changes them back to Upper case as in __ MyGlobalVar1___suggesting it has some “memory” of my global variable, ( which incidentally remains if I delete the defining line!!!!!! ( and still remains if i close and open the Workbook !?! ) ) )
_ ...However the codes do not work, erroring that the Variables MyGlobalVar1 is not defined.

I got over the problem by trial and error by changing the code defining the Global Variable to

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Public[/color] MyGlobalVar1

I am not quite sure what the problem is, but if you encounter a similar problem you may want to try the Public bit instead of Dim

_..............................
_2)
Be very careful when You are editing / changing global variables. I would recommend you save, close and reopen a Workbook before running any code after changing / editing anything involving Global variables. If you do not do this , I find that Excel crashes frequently ( badly! ) . I expect it can get confused easily as to which variable exist or do not exist or which do or do not have something in them. – Remember that Global variables “live on” after a code finishes. ( _.....There may be occasions when this is wise to do after you have finished using a Global variable...

[color=blue]Sub[/color] GetMyGlobalVar()
  MsgBox MyGlobalVar1
[color=blue]Set[/color] MyGlobalVar1 = [color=blue]Nothing[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

_ .....)

Alan

 

[DocAElstein]

Hi
So I googled a bit... I think this is it. Please let me know if i am wrong:

If I want to declare Global variables to be used in Modules in a Workbook, then I could put this in a normal Module(those created via VBE - Insert – Module ) :

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Public[/color] MyLongGlobyToo [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Dim[/color] MyLongGlobyOne [color=blue]As[/color] Long

_ .. then for example this will all work in that module

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Public[/color] MyLongGlobyToo [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Dim[/color] MyLongGlobyOne [color=blue]As[/color] Long


[color=blue]Sub[/color] TestyingMeGlobyHere()
[color=blue]Let[/color] MyLongGlobyToo = 1 + 1
[color=blue]Let[/color] MyLongGlobyOne = (1 + 1) - 1
[color=blue]End[/color] [color=blue]Sub[/color]

_ ...................................

In another module this would work ( it can be a Normal Module (those created via VBE - Insert – Module ) or a worksheet's module or in workbook's module ( ThisWorkbook ) )

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] TestyingMeGlobyThere()
[color=blue]Let[/color] MyLongGlobyToo = 1 + 1
        [color=darkgreen]'    Let MyLongGlobyOne = (1 + 1) - 1[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

(- . but note that wierdy thing that if write__ mylongglobyone __ anywhere in this second module then VBA will correct it to __MyLongGlobyOne __ )

_ ...................

I can put all this in a Sheet Module ( say Sheet1 )

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Public[/color] MyGlobySht_1 [color=blue]As[/color] [color=blue]Long[/color]
[color=blue]Dim[/color] MyLongGlobySht_1 [color=blue]As[/color] Long


[color=blue]Sub[/color] TestyingMeGlobySht_1()
[color=blue]Let[/color] MyLongGlobyToo = 1 * 1
[color=blue]Let[/color] MyLongGlobySht_1 = 1 / 1
[color=blue]End[/color] [color=blue]Sub[/color]

_..........................
In another module this would work ( it can be a Normal Module (those created via VBE - Insert – Module ) or a worksheet's module or in workbook's module ( ThisWorkbook ) )

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] TestyingMeGlobySht_1()
[color=blue]Let[/color] Worksheets("Sheet1").MyGlobySht_1 = 1 * 1
        [color=darkgreen]'    Let Worksheets("Sheet1").MyLongGlobySht_1 = 1 / 1[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

_..........................

I think that is all in agreement with what ZVI, just the bit in #4 I think should have read....

................
How to use global variables:
1. Define global variables only in standard module which is created via VBE - Insert - Module.
In the top line of the created Module1 (but below the Option Explicit) type:
Public MyGlobalVar1
............................

, ( presuming that

......
2. Use it in any module like :.....

_ .. means ...... ( it can be a Normal Module (those created via VBE - Insert – Module ) or a worksheet's module or in workbook's module ( ThisWorkbook ) )


Alan