Global Vs. Public Variable.

[Evagrius Ponticus]

Hello,

Can anyone please explain the difference between declaring a variable as global or as public. Aren't they both available to the entire project including forms? Thank you.

 

[Colin Legg]

Yeah, with that declaration statement in a code module, if you select 'Global' and press F1 it even opens up the "Public Statement" help topic in the VBA helpfile. As far as I can see they both do exactly the same thing scope-wise in VBA, but Global can't be used in class modules.

Some Excel developers do use Global. If I recall correctly, I *think* xld said on another thread in here that he uses Global because he finds that the word 'Global' is a better descriptor.

 

[Evagrius Ponticus]

Thanks Colin - I have to say though I agree with XLD - to me Global sounds more descriptive of the scope of the variable.

 

[Colin Legg]

I found that other thread. I also mentioned a couple of other small points where global can't be used -

http://www.mrexcel.com/forum/showthread.php?t=388495

 

[Michael Simpson]

Could I jump into this discussion ? I'm trying to do something similar inasmuch as I would like to define a variable in one place (if possible) so that it has the same value everywhere.
In my naivety (?), I defined the following in Thisworkbook

Option Base 1
Option Explicit
Public tech_sheet As String
Private Sub Workbook_Activate()
tech_sheet = "Technical Sheet"     ' Define the name of the technical sheet
End Sub

Now, I run a userform that contains the following code

Private Sub userform_Initialize()
code ....
call fill_room_combo
end sub
Private Sub fill_room_combo()

Dim rOldList As Range
Dim number_of_rows As Long

' Remove any duplicates in the country column (A) on sheet 2
' and sort the results. Re-populate the country combo after that
Call ThisWorkbook.SortAndRemoveDupes("J")

'Set range variable to list we want
Set rOldList = Sheets(tech_sheet).Range("J1", Sheets(tech_sheet).Range("J65536").End(xlUp))

and it falls over on the reference to tech_sheet.

Do I really have to duplicate the definition for tech_sheet as public in any module/userform that might use it ?

 

[mikerickson]

If you put the declaration
Public tech_sheet As String
in a Normal module (outside of and before any procedures), tech_sheet will be available to any sub in the workbook, with the value set in the Workbook_Activate routine.

 

[kadeo]

I found that other thread. I also mentioned a couple of other small points where global can't be used -

Declaring a universal variable

I updated that thread with some extra information on the differences too. Thought I should link to that here as well since I found this thread first. Declaring Universal Variables

 

[vimilac]

If I have code that opens another workbook and then executes an application.run statement to show a userform in that other book, can I pre-fill some of the fields within that userform with variables that have been declared as "public" in the first book (the one with the code that executes opening another workbook's userform)?