Can you create a global variable ?

[kittronald]

Is there a way to make a variable in one macro available to other macros
?

For example, how could the variable "i" in Macro_1 be available to
Macro_2 ?

Sub Macro_1()

Dim i As Integer

With Sheet2.ComboBox1
For i = 0 To .ListCount - 1
Sheet2.Range("Test") = .List(i)
Macro_2

End Sub


Sub Macro_2()

Dim sFilename As String

sFilename = "C:\Temp\" & Sheet2.Range("Data_Type").Text & " " &
i & ".txt"
ActiveWorkbook.SaveAs sFilename, xlText

End Sub


In Macro_2, a file will be output with names like the following
depending on the number of iterations Macro_1 goes through:

C:\Temp\Sales 0.txt

C:\Temp\Sales 1.txt



- Ronald K.

 

[isabelle]

hi Ronald,


yes you can, if you declared this variable "Public"

__________________________________________________________________
Public i As Integer

Sub Macro_1()
Dim i As Integer
With Sheet2.ComboBox1
For i = 0 To .ListCount - 1
Sheet2.Range("Test") = .List(i)
Macro_2
Next
End With
End Sub

Sub Macro_2()
Dim sFilename As String
sFilename = "C:\Temp\" & Sheet2.Range("Data_Type").Text & " " & i & ".txt"
ActiveWorkbook.SaveAs sFilename, xlText
End Sub
____________________________________________________________________

 

[isabelle]

sorry, i forgot to erase (Dim i As Integer) on macro_1, please remove it

 

[kittronald]

Isabelle,

Thanks for getting up early or not sleeping !

Do I put the following in Macro_1 or in the code of one of the
worksheets ?

Public i As Integer


- Ronald K.

 

[GS]

kittronald explained :

Isabelle,

Thanks for getting up early or not sleeping !

Do I put the following in Macro_1 or in the code of one of the worksheets
?

Public i As Integer


- Ronald K.

I do not advise you do this! Rather, declare an argument for Macro_2
and pass "i" to it.

Macro_1()
Dim i As Integer
With Sheet2.ComboBox1
For i = 0 To .ListCount - 1
Sheet2.Range("Test") = .List(i)
Macro_2 CStr(i) '//***
Next 'i
End With 'Sheet2.ComboBox1
End Sub

Sub Macro_2(Ndx As String)
Dim sFilename As String
sFilename = "C:\Temp\" _
& Sheet2.Range("Data_Type").Text _
& " " & Ndx & ".txt"
ActiveWorkbook.SaveAs sFilename, xlText
End Sub

 

[kittronald]

Garry,

So placing CStr(i) after Macro_2's name passes the variable by reference
(since it won't be modified) and converts it to a string value at the same
time.

Since only one argument is being passed, "Ndx" is used to refer to the
"i" value within the parentheses.

Is that correct ?


- Ronald K.

 

[GS]

kittronald used his keyboard to write :

Garry,

So placing CStr(i) after Macro_2's name passes the variable by reference
(since it won't be modified) and converts it to a string value at the same
time.

Since only one argument is being passed, "Ndx" is used to refer to the
"i" value within the parentheses.

Is that correct ?


- Ronald K.

Your intellisense should verify that the value of Ndx matches the value
of i for each iteration of your loop. So then, the 1st round passes 0
(zero) to Macro_2. The 2nd round passes 1, the 3rd round passes 2,
...and so on. Just hold your mouse over each of the variables in break
mode and you'll see what each value is and when it changes to the next
increment of your loop.

 

[isabelle]

hi Ronald,

put it in the top of the module, outside the macro

 

[kittronald]

Isabelle,

Merci ... again !

Now I know how to make a variable available to everything.

In this case, it might be safer to pass the variable as an argument from
Macro_1 to Macro_2 since only those modules would use it.

And I might forget and use the "i" variable elsewhere which would cause
a problem.




- Ronald K.

 

[kittronald]

Garry,

It appears to be working as expected.

Thanks !



- Ronald K.

 

[isabelle]

hi Ronald,

in this case, you must use "private" instead of "Public"

Private i As Integer

and also you should use another variable name


--
isabelle




Le 2011-11-06 03:51, kittronald a écrit :

 

[GS]

isabelle wrote :

hi Ronald,

in this case, you must use "private" instead of "Public"

Private i As Integer

and also you should use another variable name

As per my example to Ronald, 'i' is declared in Macro_1 in the normal
fashion. Macro_2 accepts 'i' as an arg named 'Ndx' in its declaration
section. Not sure why you're suggesting a module-level definition for
this counter since it really serves no practical purpose at that scope,
IMO.