Declaring a universal variable

[AndrewKent]

Hi folks,

I know this is probably possible but I do not know how.

I have three macros, lets say, Macro1, Macro2 and (yep, you guessed it), Macro3. Now, Macro1 "calls" Macro2 and Macro3. The problem I am having is that I want to declare a variable within Macro1 that can be used by ALL these macros. So if I said...

ActiveRow = Worksheets("Data").Range("A1:" & Range("A65536").End(xlUp).Offset(1, 0).Row & "").Row

...then I want to be able to use ActiveRow within all my other macros that I am calling without having to declare it again in each one.

Does this make any sense?

Cheers,

Andy

 

[xld]

Declare it as a public variable before any procedures.

 

[pavin]

Declare it as Global / Public

Use Public when you want to use the variable in one Module and different Macros
Use Global when you want to use the variable in different Modules in the same workbook.

In the module on the top of the code window. Just declare it as

Public ActiveRow as String

 

[AndrewKent]

So I would put Global ActiveRow as String outside of my macros within the module? So if I declared

ActiveRow = Worksheets("Data").Range("A1:" & Range("A65536").End(xlUp).Offset(1, 0).Row & "").Row[code]

as part of macro1, it would work in all following macros?

Andy

 

[pavin]

Suppose you have a code as following: declare it on the top of the window. You can test this by copying the following code and paste it in a new module. Let me know if you are not clear

Global CellByCol As String, CellByRow As String, Te As String
 
Sub SRnge()
    Range("A1").Select
    Selection.End(xlDown).Select
    CellByCol = ActiveCell.Row
    Selection.End(xlUp).Select
    Selection.End(xlToRight).Select
    CellByRow = ActiveCell.Address
    Selection.End(xlToLeft).Select
    CellByRow = Mid(CellByRow, 2, InStrRev(CellByRow, "$") - 2)
    'MsgBox "A1:" & CellByRow & CellByCol
    'Range("A1:" & CellByRow & CellByCol).Select
End Sub

 

[xld]

Declare it as Global / Public

Use Public when you want to use the variable in one Module and different Macros
Use Global when you want to use the variable in different Modules in the same workbook.

In the module on the top of the code window. Just declare it as

Public ActiveRow as String

Global and Public are the same. Indeed if you look up Global in VBA help, you get Public.

Use Private when you want to use the variable in one Module and different Macros

 

[Colin Legg]

Public vs. Global - this came up on another recent thread.

There seems to be few syntatical differences between them.

One difference is you can't use the Global keyword (declaring module level variables or methods etc...) in class modules, but you can use Public.

Also, in standard code modules, you can't declare routines using Global, but you can use Public. (As a side, if you try to use Global when declaring a function the VBE automatically strips it out, but Public is retained).



So I can't see any use for Global?

 

[xld]

Public vs. Global - this came up on another recent thread.

There seems to be few syntatical differences between them.

One difference is you can't use the Global keyword (declaring module level variables or methods etc...) in class modules, but you can use Public.

Also, in standard code modules, you can't declare routines using Global, but you can use Public. (As a side, if you try to use Global when declaring a function the VBE automatically strips it out, but Public is retained).



So I can't see any use for Global?

If I have any public/global variables, I always use Global. It seems better describing IMO.

 

[kadeo]

I know this thread's an old one but it came up while I was looking for (there isn't much!) information about this and I thought I'd add what I've found for the benefit of others. According to Chip Pearson's awesome website, the main difference between Public and Global is to do with accessing variables declared as either from within other projects. N.B. Global can only be used for variables and not procedures; excel will automatically remove Global and treat a procedure as Public

Both Public and Global in a code module will produce the same results within the project, that is, either can be called from anywhere (including object modules like Class Modules, Userforms, ThisWorkbook etc) within the project. Either can also be called from another project.

However if Option Private Module is used at the top of the module, this restricts Public variables within that module from being "seen" by other projects. Global variables cannot be restricted in this way and so cannot be declared in modules that use Option Private Module. I presume it would generate a compiler error.

As mentioned in an above post Global cannot be used in an object module. Likely for a similar reason that it can't be used in a code module that uses Option Private Module; how they interact with other projects.

A more detailed explanation can be found in the two paragraph's on Project Scope and Global Scope on Chip's website here.

Effectively it looks like Public will do everything Global can do but has added flexibility when it comes to interaction from other projects. I read somewhere else that Global and Dim come from older versions of VB and were probably only kept for backwards compatibility (and old programmers who find change challenging! lol). It seems it would make more sense to ditch them entirely as neither offer anything that Public and Private don't.