VBA Set value accessible in other subs

[jmpmolegraaf]

Hello everybody,

So far you've all been of great help and now I want to figure out something else.

I have a lot of cases were I set a value/range in one sub but actually need it in another sub again. See example below. How can make set values/ranges/etc available in multiple subs?

Thanks!


Sub Example ()

set r = range ("A1")

End Sub


Sub Example2 ()

' i want to use r here

End Sub

 

[patel45]

[COLOR=#ff0000]Dim r[/COLOR] ' before Sub
Sub Example ()
set r = range ("A1")
End Sub

Sub Example2 ()
' i want to use r here
End Sub

 

[jmpmolegraaf]

it's not the Dim part that's a problem.. i always define them before the subs start.

but the value/range of "r" seems to be reset when leaving sub 1 while needing it in sub 2.

 

[patel45]

paste here the whole code or better a link to sample file

 

[jmpmolegraaf]

Hello Patel, example below:

Option Explicit


Dim wsNLD As Worksheet
Dim wsGP As Worksheet

Sub NonListedPTDiscounts()


Set wsNLD = ThisWorkbook.Sheets("Non-Listed Discounts")
Set wsGP = ThisWorkbook.Sheets("GP Matrix")

etc etc

End Sub

----------

Sub NonListedPDiscounts()


Set wsNLD = ThisWorkbook.Sheets("Non-Listed Discounts")
Set wsGP = ThisWorkbook.Sheets("GP Matrix")



etc etc

End Sub

-------


It seems that I have to set these worksheets again in the next sub. Both subs are in the same module and their dimensions are determined "before" the first sub.

 

[patel45]

paste here the whole code or better a link to sample file

 

[HaHoBe]

Hi, jmpmolegraaf,

maybe try it like this and initialize the variables in their own routine:

Dim wsNLD As Worksheet
Dim wsGP As Worksheet

Sub MainInitialize()
Set wsNLD = ThisWorkbook.Sheets("Non-Listed Discounts")
Set wsGP = ThisWorkbook.Sheets("GP Matrix")
End Sub

Private Sub NonListedPTDiscounts()
If wsNLD Is Nothing Then MainInitialize
Debug.Print wsNLD.Name
MainExit
NonListedPDiscounts
End Sub

Private Sub NonListedPDiscounts()
If wsGP Is Nothing Then MainInitialize
Debug.Print wsGP.Name
MainExit
End Sub

Sub MainExit()
Set wsNLD = Nothing
Set wsGP = Nothing
End Sub

The variables have been set as local variables for a module, if the code is in different modules you would need to declare them Public in a standard module.

@patel45:
2 answers with identical test - I´m impressed on your will to post ...I mean on the will to help (your posting account to get up).

Ciao,
Holger

 

[jmpmolegraaf]

Thanks a lot Holger.. that looks promising.

To keep things clean and easy to understand, I want to create one MasterSub in which I "set" the main things and then I call all the various subs for each tasks that have to be done.