Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing variables in macros
I thought I had this figured out; but apparently not.
I have variables I'm passing between subs and they are not being returned. here's the code: =========================================== Public Next_Prog, Last_Prog, Start_add As Variant Public ProgName, Levels As String ----------------------------------------------------------------------- Range("A2").Select Prog_row = ActiveCell.Row Selection.End(xlDown).Select Last_Prog = ActiveCell.Row Range("A2").Select ProgName = ActiveCell.Value Levels = ActiveCell.Offset(0, 1) ActiveCell.Offset(1, 0).Select Next_Prog = ActiveCell.Address Range("D4").Select Row_nbr = ActiveCell.Row Start_add = ActiveCell.Address Selection.End(xlDown).Select Last_row = ActiveCell.Row Range(Start_add).Select Do While ActiveCell.Row <= Last_row If ActiveCell.Value = ProgName Then .... do statements End If Call Set_Prg_Name Loop --------------------------------------------- Sub Set_Prg_Name() Range(Next_Prog).Select If ActiveCell.Row Last_Prog Then Else ProgName = ActiveCell.Value Levels = ActiveCell.Offset(0, 1) ActiveCell.Offset(1, 0).Select Next_Prog = ActiveCell.Address Range(Start_add).Select End If <the var value changes here but is not <transfered back when returning to the <previous calling sub. End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing variables in macros
Hi
See if this makes sense to you: Sub Test() Dim A As Long, B As Long A = 10 MsgBox "A is " & A Call ByRefMacro(A) MsgBox "A is " & A B = 10 MsgBox "B is " & B Call ByValMacro(B) MsgBox "B is " & B End Sub Sub ByRefMacro(ByRef X As Long) X = X * 2 MsgBox "ByRefMacro says " & X End Sub Sub ByValMacro(ByVal X As Long) X = X * 2 MsgBox "ByValMacro says " & X End Sub HTH. Best wishes Harald "El Bee" wrote in message ... I thought I had this figured out; but apparently not. I have variables I'm passing between subs and they are not being returned. here's the code: =========================================== Public Next_Prog, Last_Prog, Start_add As Variant Public ProgName, Levels As String ----------------------------------------------------------------------- Range("A2").Select Prog_row = ActiveCell.Row Selection.End(xlDown).Select Last_Prog = ActiveCell.Row Range("A2").Select ProgName = ActiveCell.Value Levels = ActiveCell.Offset(0, 1) ActiveCell.Offset(1, 0).Select Next_Prog = ActiveCell.Address Range("D4").Select Row_nbr = ActiveCell.Row Start_add = ActiveCell.Address Selection.End(xlDown).Select Last_row = ActiveCell.Row Range(Start_add).Select Do While ActiveCell.Row <= Last_row If ActiveCell.Value = ProgName Then .... do statements End If Call Set_Prg_Name Loop --------------------------------------------- Sub Set_Prg_Name() Range(Next_Prog).Select If ActiveCell.Row Last_Prog Then Else ProgName = ActiveCell.Value Levels = ActiveCell.Offset(0, 1) ActiveCell.Offset(1, 0).Select Next_Prog = ActiveCell.Address Range(Start_add).Select End If <the var value changes here but is not <transfered back when returning to the <previous calling sub. End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing variables in macros
I'm confused. When I posted this question it said there was an error. I
thought this ment the message would not be posted. It's now going to appear 3 times. Sorry for all this. "El Bee" wrote: I thought I had this figured out; but apparently not. I have variables I'm passing between subs and they are not being returned. here's the code: =========================================== Public Next_Prog, Last_Prog, Start_add As Variant Public ProgName, Levels As String ----------------------------------------------------------------------- Range("A2").Select Prog_row = ActiveCell.Row Selection.End(xlDown).Select Last_Prog = ActiveCell.Row Range("A2").Select ProgName = ActiveCell.Value Levels = ActiveCell.Offset(0, 1) ActiveCell.Offset(1, 0).Select Next_Prog = ActiveCell.Address Range("D4").Select Row_nbr = ActiveCell.Row Start_add = ActiveCell.Address Selection.End(xlDown).Select Last_row = ActiveCell.Row Range(Start_add).Select Do While ActiveCell.Row <= Last_row If ActiveCell.Value = ProgName Then .... do statements End If Call Set_Prg_Name Loop --------------------------------------------- Sub Set_Prg_Name() Range(Next_Prog).Select If ActiveCell.Row Last_Prog Then Else ProgName = ActiveCell.Value Levels = ActiveCell.Offset(0, 1) ActiveCell.Offset(1, 0).Select Next_Prog = ActiveCell.Address Range(Start_add).Select End If <the var value changes here but is not <transfered back when returning to the <previous calling sub. End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing Variables | Excel Discussion (Misc queries) | |||
Passing variables from one sub to another | Excel Programming | |||
Passing Variables | Excel Programming | |||
passing variables | Excel Programming | |||
Passing Variables | Excel Programming |