CODEWITHSUNDEEP
vbaexcelvariablespublic
Ricardo Rosas
Ricardo Rosas

Reputation: 57

VBA : Using Public variables for other subs

Excel-VBA. I want to create a variable that I will use in a couple of other subs later. Following other questions on this I declared the variable publicly

Public Counter As Integer

Then, I proceeded to give a value to the variable using a sub

Sub Count()
Set sh2= ActiveWorkbook.Sheets("Sheet2")
Set sh1 = ActiveWorkbook.Sheets("Sheet1")
Dim rng As Range
Dim trng As Range
Set trng = sh2.Range("A4:HV4")

For Each rng In trng
    If rng.Value = "Name" Then
    Counter = Range(rng, rng.End(xlToLeft)).Columns.count
    End If
Next rng

If I try to test on the same sub which value count has e.g. 

sh1.range("B1").value = counter

I get the right number.

However, once I try calling it on another sub, the value is 0!

Sub Test()

Range("F1").Value = counter

end sub

I have also tried calling the Count sub on my test sub, but also no result.

Any ideas? Thanks!

Upvotes: 1

Views: 2337

Answers (2)

Ricardo Rosas
Ricardo Rosas

Reputation: 57

So, I solved it by simply calling the function before my new sub!

Sub Test()
Call count()
Range("F1").Value = counter

end sub

Upvotes: -1

cyboashu
cyboashu

Reputation: 10433

@ShaiRado already pointed it out in the comments. Below are some details around that :

Public types doesn't have visibility through out the VBA project, atleast not without their Object reference. So, If you declare a variable as Public inside a worksheet object\ UserForm \ Class, you need to use full reference for that variable. e.g if Counter is declared inside Sheet1 then use Sheet1.Counter.

Global variables on the other hand are accesible through out the VBA project. You dont need to call their parent object names for refernce. Just one catch here, Global variables can only be declared inside Standard Modules. object\ UserForm \ Class, they don't allow global variable declaration inside them.

In case of requirements for Project wide access, always declare Global.

Another thing is Option Explicit, if you had it in your code, instead of unexpected output/behavior you woud have got the error Variable not defined with codde highlihgted at

Range("F1").Value = counter

That would have made debugging much easier. :)

Upvotes: 2

Related Questions