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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Forum statistics

Threads
1,137,060
Messages
5,679,387
Members
419,824
Latest member
Mercy kiara

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
Top