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.