Passing argument to another Sub
 

Hello,

Is it possible to pass an argument to another procedure without calling the
procedure?

In my worksheet i have two procedures. One manipulates the range, the other
gives the user an option to undo the resluts of the first procedure.
Here's the code that im using now. It works well, but requires me to keep
extra blank worksheet to save the range to:

Sub CommandButton1_Click ()
Dim StoredValue(1 To 17) As Long
For i = 1 To 17
StoredValue(i) = Sheets("Schedule of Values").Cells(i+13, 3).Value
Next i

For i = i To 17
Sheets("Temp").Cells(i, 3).Value = StoredValue(i)
Next i

[Instructions]
End Sub

Sub CommandButton2_Click ()

Dim TempValues(1 To 17) As Long
For i = 1 To 17
TempValues(i) = Sheets("Temp").Cells(i, 3).Value
Next i

For i = 1 To 17
Sheets("Schedule of Values").Cells(i+13, 3).Value = TempValues(i)
Next i

End Sub


There's gotta be a way to pass the first array to the second
button-triggered sub!

Passing argument to another Sub
 

The button click event doesn't take an argument, so if you want to pass it
some value, you have to store that somewhere.

--
HTH

Bob Phillips

"Roman" wrote in message
...
Hello,

Is it possible to pass an argument to another procedure without calling
the
procedure?

In my worksheet i have two procedures. One manipulates the range, the
other
gives the user an option to undo the resluts of the first procedure.
Here's the code that im using now. It works well, but requires me to keep
extra blank worksheet to save the range to:

Sub CommandButton1_Click ()
Dim StoredValue(1 To 17) As Long
For i = 1 To 17
StoredValue(i) = Sheets("Schedule of Values").Cells(i+13, 3).Value
Next i

For i = i To 17
Sheets("Temp").Cells(i, 3).Value = StoredValue(i)
Next i

[Instructions]
End Sub

Sub CommandButton2_Click ()

Dim TempValues(1 To 17) As Long
For i = 1 To 17
TempValues(i) = Sheets("Temp").Cells(i, 3).Value
Next i

For i = 1 To 17
Sheets("Schedule of Values").Cells(i+13, 3).Value = TempValues(i)
Next i

End Sub


There's gotta be a way to pass the first array to the second
button-triggered sub!

Passing argument to another Sub
 

I'm not sure I understand all of your code, but here is my suggestion:

1. At the top of your module declare a public array without dimensioning it.
Place this string at the top : "Public StoredValueArray() as Variant"

2. In your first routine, modify it to read:

Sub CommandButton1_Click ()
Dim StoredValue(1 To 17) As Long
For i = 1 To 17
StoredValue(i) = Sheets("Schedule of Values").Cells(i+13, 3).Value
Next i
StoredValueArray = StoredValue
End Sub

3. Then use the public name in your other routines.

Good Luck,
VBA Dabbler

"Roman" wrote:

Hello,

Is it possible to pass an argument to another procedure without calling the
procedure?

In my worksheet i have two procedures. One manipulates the range, the other
gives the user an option to undo the resluts of the first procedure.
Here's the code that im using now. It works well, but requires me to keep
extra blank worksheet to save the range to:

Sub CommandButton1_Click ()
Dim StoredValue(1 To 17) As Long
For i = 1 To 17
StoredValue(i) = Sheets("Schedule of Values").Cells(i+13, 3).Value
Next i

For i = i To 17
Sheets("Temp").Cells(i, 3).Value = StoredValue(i)
Next i

[Instructions]
End Sub

Sub CommandButton2_Click ()

Dim TempValues(1 To 17) As Long
For i = 1 To 17
TempValues(i) = Sheets("Temp").Cells(i, 3).Value
Next i

For i = 1 To 17
Sheets("Schedule of Values").Cells(i+13, 3).Value = TempValues(i)
Next i

End Sub


There's gotta be a way to pass the first array to the second
button-triggered sub!

Passing argument to another Sub
 

"Bob Phillips" wrote:

The button click event doesn't take an argument, so if you want to pass it
some value, you have to store that somewhere.

Ok, thx

Passing argument to another Sub
 

"VBA Dabbler" wrote:

I'm not sure I understand all of your code, but here is my suggestion:

1. At the top of your module declare a public array without dimensioning it.
Place this string at the top : "Public StoredValueArray() as Variant"

2. In your first routine, modify it to read:

Sub CommandButton1_Click ()
Dim StoredValue(1 To 17) As Long
For i = 1 To 17
StoredValue(i) = Sheets("Schedule of Values").Cells(i+13, 3).Value
Next i
StoredValueArray = StoredValue
End Sub

3. Then use the public name in your other routines.

Good Luck,
VBA Dabbler

Tried this. Getting an error message saying that arrays cannot be declared
as public variables.

Passing argument to another Sub
 

I do this all the time and have no problems. Do you have the proper
reference libraries turned on?

At what point in your code are your getting the error message?

Here is some code that I just wrote and tested - it does effectively the
same thing:

Option Explicit
Option Base 1
Public Demonstration() As Variant


Sub test()
Dim TempArray() As Variant
TempArray = Array(1, 2, 4, 10)
Demonstration = TempArray
Call ListArray
End Sub

Sub ListArray()
Dim i As Integer
For i = 1 To UBound(Demonstration)
MsgBox Demonstration(i)
Next i
End Sub


Let me know how this works.

Regards,
VBA Dabbler


"Roman" wrote:



"VBA Dabbler" wrote:

I'm not sure I understand all of your code, but here is my suggestion:

1. At the top of your module declare a public array without dimensioning it.
Place this string at the top : "Public StoredValueArray() as Variant"

2. In your first routine, modify it to read:

Sub CommandButton1_Click ()
Dim StoredValue(1 To 17) As Long
For i = 1 To 17
StoredValue(i) = Sheets("Schedule of Values").Cells(i+13, 3).Value
Next i
StoredValueArray = StoredValue
End Sub

3. Then use the public name in your other routines.

Good Luck,
VBA Dabbler

Tried this. Getting an error message saying that arrays cannot be declared
as public variables.

Passing argument to another Sub
 

It worked! Thanks a lot VBA Dabbler!

i had my code in a wrong place. After i moved it to a module it worked. My
buttons subs now only call the procedures from the module.

"VBA Dabbler" wrote:

I do this all the time and have no problems. Do you have the proper
reference libraries turned on?

At what point in your code are your getting the error message?

Here is some code that I just wrote and tested - it does effectively the
same thing:

Option Explicit
Option Base 1
Public Demonstration() As Variant


Sub test()
Dim TempArray() As Variant
TempArray = Array(1, 2, 4, 10)
Demonstration = TempArray
Call ListArray
End Sub

Sub ListArray()
Dim i As Integer
For i = 1 To UBound(Demonstration)
MsgBox Demonstration(i)
Next i
End Sub


Let me know how this works.

Regards,
VBA Dabbler


"Roman" wrote:



"VBA Dabbler" wrote:

I'm not sure I understand all of your code, but here is my suggestion:

1. At the top of your module declare a public array without dimensioning it.
Place this string at the top : "Public StoredValueArray() as Variant"

2. In your first routine, modify it to read:

Sub CommandButton1_Click ()
Dim StoredValue(1 To 17) As Long
For i = 1 To 17
StoredValue(i) = Sheets("Schedule of Values").Cells(i+13, 3).Value
Next i
StoredValueArray = StoredValue
End Sub

3. Then use the public name in your other routines.

Good Luck,
VBA Dabbler

Tried this. Getting an error message saying that arrays cannot be declared
as public variables.