Very Important 'Dim' Question (Dim Entire Workbook)?

[indi visual]

Is there a way I can create a worksheet UDF, or some kind of function that would Dim all the Subs for the workbook?


I have about 40 Dims, and I need them to work for all of the subs.


Right now I'm copying & pasting all of the Dim data for each sub (which is lengthy and excessive), is there a way to condense this project so that all the subs reference all of my Dims for the entire workbook (by placing all of the Dims at the top in one spot for instance)?


Sub Dim_For_All_Other_Subs()

Dim Item01 As String

Dim Item02 As String etc...


Item01 = Range("A1").Value

Item02 = Range("A2").Value etc...


End Sub

 

[Luke M]

Put the Dim's at very top of module to define for all subs within that module.


Dim xName as String

Dim xNumber as Integer


Sub FirstSub()

'stuff

End Sub


Function Chandoo()

'more stuff

End Sub


Further reading:

http://www.cpearson.com/excel/DeclaringVariables.aspx

 

[indi visual]

So based on your response and the reference link, my code should look like...


Dim xName as String

Dim xNumber as Integer


Option Explicit

Function Chandoo()

xName = Range("A1").Value

xNumber = Range("A2").Value

End Sub


Sub FirstSub()

MsgBox = xName & xNumber

End Sub


Sub SecondSub()

MsgBox = xName & xNumber

End Sub

 

[Luke M]

yea, looks pretty good. I'd suggest putting "option explicit" as first thing, since it tells any future code-readers what to expect about your variables.

[pre]

Option Explicit
Dim xName As String
Dim xNumber As Integer

Function Chandoo()
xName = Range("A1").Value
xNumber = Range("A2").Value
End Function

Sub FirstSub()
MsgBox xName & xNumber
End Sub

Sub SecondSub()
MsgBox xName & xNumber
End Sub

[/pre]

 

[indi visual]

Perfect thanks Luke : )

 

[indi visual]

Okay I know I'm super close, but for one reason or another when I test the code above (the latest one you provided) my MsgBox is empty?

 

[kchiba]

You must run the Function first, to get the data into the variables

 

[dan_l]

This seems to work ok:


Dim test1 As Integer

Dim test2 As Integer


Sub testa()

test1 = Range("test1").Value

test2 = Range("test2").Value


MsgBox test1 + test2


Call testb


End Sub


Sub testb()

MsgBox test1 & " " & test2


End Sub