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.

 

[WarPigl3t]

This is tough to answer in such a way that it will make sense. Here goes. A global variable must be static. Like the number 7 or the word "hello". It can be anything, just like a regular variable. The difference between a variable and a global variable is that a variable value can change. For example the variable "myVariables = 5" could start out equaling 5, but maybe later in the code you make it equal 7. Global variables are not allowed to change. And that's where you'll run into a problem because you want yo have a global variable equal to a calculating date. When I see calculating date, I imagine that the date is not a single static date and so it cannot be a global variable. But there is hope. You can pass variables to other functions and subroutines that are not global. I wouldn't know how to do that between workbooks, but at least know you know what to research. "Pass variables between workbooks"

 

[vidyanand]

Thanx for the reply. If we declare it as function, can it be accessed accross workbooks or any another way to access function declared in another module just like we call the procedures from another workbook? I have declared it as public function in Personal workbook but I can't use it in another workbook.

 

[ZVI]

Global variables are not allowed to change.

What you've mentioned is for global constant not for global variables.
Global variables are changeable.

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

 

[WarPigl3t]

ZVI yes I was referring to a constant variable. My bad. I got my thought process mixed up. Its 5am and I habemt slept.

 

[ZVI]

Thanx for the reply. If we declare it as function, can it be accessed accross workbooks or any another way to access function declared in another module just like we call the procedures from another workbook? I have declared it as public function in Personal workbook but I can't use it in another workbook.

There are some possibilities to access global function of one workbook from another.
1. Put global functions into the hidden PERSINAL.XLSB - reed more about it on this site: Copy your macros to a Personal Macro Workbook
2. Put global functions into the XLAM AddIn, read this site - Creating VBA Add-ins to Extend and Automate Microsoft Office Documents
3. Use Application.Run method, read this page of Ron De Bruin's site - How do I use Application.Run in Excel

 

[ZVI]

I got my thought process mixed up. Its 5am and I habemt slept.

This state of almost sleeping is known to me too. But usually someone here also check it to fix my bad

 

[ZVI]

3. Use Application.Run method, read this page of Ron De Bruin's site - How do I use Application.Run in Excel

Example of calling external function with parameters via Run method
Put this code to the Module1 of Book1.xlsm

' Function in Module1 of Book1.xlsm
Function MySum(Num1, Num2) As Double
  MySum = Num1 + Num2
End Function

Call it from the module of another workbook like this:

Sub Test()
  Dim a As Double, b As Double, v As Double
  a = 2
  b = 3
  v = Application.Run("Book1.xlsm!MySum", a, b)
  MsgBox v
End Sub

There is also 4d method by setting up reference to the unique name of external VBA project via VBE - Tools - References.

 

[vidyanand]

I tried to call the function I stored in Personal.xlsb with following code

Dim shname As String
shname = Application.Run("C:\Users\Vidyanand\AppData\Roaming\Microsoft\Excel\XLSTART\Personal.xlsb!nwshtnm")

But it is giving me eror '1004' saying the Macro may not be available in this workbook or all macros may be disabled.

my code in Personal. xlsb is

Public Function nwshtnm() As String


If Day(Date) > 15 Then
mth = Left(MonthName(Month(Date - 32)), 3)
yy = Right(Year(Date - 32), 2)
nwshtnm = mth & " " & yy
Else
mth = Left(MonthName(Month(Date - 29)), 3)
yy = Right(Year(Date - 29), 2)
nwshtnm = mth & " " & yy
End If




End Function

Could you please help. What I have done wrong here?

 

[ZVI]

Please add to nwshtnm this top line:
Dim mth, yy

And then call nwshtnm like this:

Sub test()
  Dim shname As String
  shname = Application.Run("Personal.xlsb!nwshtnm")
  MsgBox shname
End Sub

Check that extension of the Personal workbook is XLSB, change it in the code to XLSM if required