Can you create a global variable ?
 

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.

Can you create a global variable ?
 

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

Can you create a global variable ?
 

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


--
isabelle

Can you create a global variable ?
 

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.

Can you create a global variable ?
 

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

Can you create a global variable ?
 

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.

Can you create a global variable ?
 

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.

--
Garry

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

Can you create a global variable ?
 

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.

Can you create a global variable ?
 

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.

Can you create a global variable ?
 

Garry,

It appears to be working as expected.

Thanks !



- Ronald K.

Can you create a global variable ?
 

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 :
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.

Can you create a global variable ?
 

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


--
isabelle

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.

--
Garry

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