Easy Object variables question

craigyg

Board Regular
Joined
Dec 14, 2005
Messages
114
When I declare an object variable in a subroutine using the dim statement, can I use that object variable throughout the module or only in that subroutine. What about if I use the public statement?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Where/when you can use variables is all down to scope.

That's basically determined by where/how you declare them.

Try a search for scope in the Excel VBA help for more detailed information.

If you want more specific advice post back with more information on what you actually want to do.
 
Upvote 0
Hi,

some more info
http://www.cpearson.com/excel/variables.htm
http://www.cpearson.com/excel/DeclaringVariables.htm

declaring on top of the module
variable a will be recognized in the enire module
run aa
Code:
Dim a as string

Sub aa()
a = "string"
bb
End Sub

Sub bb
MsgBox a
End Sub

public ==> variable lives as long as workbook is open
Code:
Public a As String

Sub aa()
a = "ert"
End Sub

Sub bb() 'can be in other module
MsgBox a
End Sub
some experts will tell you to avoid public variables
you can use instead "calling procedures with arguments"
like this
Code:
Sub runthis()
Call macro(1, 3)
Call macro(2, 5)
Call macro(99, 101)
End Sub

Sub macro(var1 As Integer, var2 As Integer)
MsgBox var1 & " + " & var2 & " = " & var1 + var2
End Sub

'or just use
'macro 1, 3
kind regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,216,175
Messages
6,129,299
Members
449,499
Latest member
HockeyBoi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top