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.

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


--
isabelle

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.

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

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.

hi Ronald,

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

--
isabelle


Le 2011-11-04 05:02, kittronald a écrit :
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.

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.