PDA

View Full Version : [SOLVED] How to keep the value of a variable between 2 Sub (same module)



ValerieT
10-23-2013, 07:02 AM
Hello

I want to define a value to some variable (columns positioning) and keep this value for all subs I have in a module
I thought I could use " Static".. but I can't find how it work... I tried to put it everywhere :-)

In this example, I want the value of Col_Name (defined in Sub My_Columns) being available in Sub My_Nameprocess(). I know I could store it into a cells, but is there another simple way?


Static Sub My_Columns()
Static Col_Name as integer
Static Col_ID as integer
For i = 1 To 35
If Cells(1, i) = "Name" Then
Col_Name = i
Exit For
End If
Next i
If Col_Name = 0 Then
MsgBox ("Column ""Name"" not found, check your column header")
End If
End Sub



Sub My_Nameprocess()
For i = 1 To Endlign
If Cells(i, Col_Name) = 0 Then
....
.....
End If
Next i
End Sub



Thanks in advanced!

Kenneth Hobs
10-23-2013, 07:23 AM
What is the scope of use? Is it current session use or current and future use?

For the first case, I generally just pass the value or object to the other Sub(s) or Functions. Or, I Dim it as Public in a Module. For the 2nd case, storing in a cell is one of 5 methods that I can think of offhand.

SamT
10-23-2013, 07:39 AM
Declare the variable at the top of the module, before any subs


Dim Col_Name As Long

Sub My_Columns()
Dim Found As Range

Set Found = Rows(1).Find("Name")
If Found Is Nothing Then
MsgBox ("Column ""Name"" not found, check your column header")
Else
Col_Name = Found.Column
End If

End Sub

Sub My_Nameprocess()
With Columns(Col_Name)
For i = 1 To Endlign
If Cells(i) = 0 Then
....
.....
End If
Next i
End Sub

ValerieT
10-23-2013, 07:46 AM
I think I did that already (both suggestion of PUBLIC before any other sub), but COl_Name keep = 0 in Sub My_Nameprocess...

I'll try again, and by the way, thanks for the nicest
Found = Rows(1).Find("Name")

ValerieT
10-23-2013, 07:55 AM
I apologizes to every one, I must be very tired. It works perfectly with Public (my first try) if you THINK about running the 1st macro !!!

(5:00 PM, South of France, temperature warmer than summer time, and Italian restaurant during lunch time = No more brain available)

Thans everyone!

Aussiebear
10-25-2013, 12:34 AM
Perfectly understandable.