Passing variables in macros

E

El Bee

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
 
H

Harald Staff

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
 
E

El Bee

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top