assign constant values to multidimensional array in VBA

[XLdude]

my objective is to create VBA Function equivalent of this formula:

=HLOOKUP(lookvalue,{-90,-84,-72,-61,-50,-39,-28,-17,-6,6,17,28,39,50,61,72,84;472,460,440,416,386,350,305,260,215,170,125,89,59,35,15,3,1},2,TRUE)

as you can see contains a 2x17 array

VBA code so far:

Dim U1(2, 17) As Variant 
Function ChartNum(Atlas As String, ra As Date, dec As Variant) As String 

The next step is to assign the values of the 2x17 array

I see from VBA help the function to assign a 1 dimensional array is

 
U1 = Array(-90, -84, -72, -61, -50, -39, -28, -17, -6, 6, 17, 28, 39, 50, 61, 72, 84) 

the problem is I need to assign values to all the dimensions, with 'something' like this hypothetical code:

 
U1(1, x) = Array(-90, -84, -72, -61, -50, -39, -28, -17, -6, 6, 17, 28, 39, 50, 61, 72, 84) 
U1(2, x) = Array(472,460,440,416,386,350,305,260,215,170,125,89,59,35,15,3,1) 

it seems like it should be possible to assign values to the "n-th" row or dimension with a statement, since it can be done at the 1 dim level without a loop.
how do I do this WITHOUT using a loop? (preferred)
how do I do this WITH a loop?

Thanks in advance.
Dave

[Dave Hawley]

Hi XlDude

Why not simply use the 2 functions in a VBA Function by preceding them with WorksheetFunction?

[XLdude]

Thanks for the quick response Dave.
--Sorry, I don't understand your comment.

if you were suggesting this---

Application.WorksheetFunction.HLOOKUP(lookvalue,{-90,-84,-72,-61,-50, _ 
-39,-28,-17,-6,6,17,28,39,50,61,72,84; _ 
472,460,440,416,386,350,305,260,215,170,125,89,59,35,15,3,1},2,True) 

it gives a "Compile Error-Invalid character" because of the {
The worksheet cell function is okay with that format of the array, but VBA won't work.

Perhaps I should clarify--
The key thing I am trying to figure out is how to initialize the values in the 2x17 array, in VBA

for example here is what I first tried:

Dim U1(2, 17) As Variant 
Function ChartNum(Atlas As String, ra As Date, dec As Variant) As String 
     
     ' intialize the values of the 2x17 array
    U1= array(-90,-84,-72,-61,-50,-39,-28,-17,-6,6,17,28,39,50,61,72,84;472,460,440,416,386,350,305,260,215,170,125,89,59,35,15,3,1) 
     '
     '  lookup a key parameter using the dec variable
    keyparam= Application.WorksheetFunction.HLookup(dec, U1, 2, True) 
     ' ...other calculations follow
     '

But, what does not work above is the line for
U1 = array (...row one values... ; ...row two values...)
which produces a Compile Error because of the ";" .
The ";" character is okay syntax for the array in the actual worksheet cell function to separate the row one values from row two values.

Is there a way to initialize all values in an X by Y array?
ie. without looping through 2x17=34 lines of code using
myarray(x,y) = value
to set each value individually ???
Hopefully that makes it clearer.

Thank you for your help.
-Dave
[ps: 100 hacks book is Great!]

[jindon]

Hi
You need to create multi-dim-array

Sub test() 
    Dim LUarray(), i As Integer, ii As Integer, x, y, z, lookvalue 
    lookvalue = 20 
    x = Array(-90, -84, -72, -61, -50, -39, -28, -17, -6, 6, 17, 28, 39, 50, 61, 72, 84) 
    y = Array(472, 460, 440, 416, 386, 350, 305, 260, 215, 170, 125, 89, 59, 35, 15, 3, 1) 
    Redim LUarray(1, UBound(x)) 
    For i = 0 To 1 
        If i = 0 Then 
            For ii = 0 To UBound(x): LUarray(i, ii) = x(ii): Next 
            Else 
                For ii = 0 To UBound(y): LUarray(i, ii) = y(ii): Next 
                End If 
            Next 
            MsgBox Application.HLookup(lookvalue, LUarray, 2) 
        End Sub 

[XLdude]

Excellent!

I pasted the code and I stepped through the logic - I understand.
Very efficient method.

Further, I modified it to accomodate a 3rd array...works great

Thank you very much!
-Dave

[norie]

No need to loop.

Sub test1() 
    lookvalue = 20 
    x = Array(-90, -84, -72, -61, -50, -39, -28, -17, -6, 6, 17, 28, 39, 50, 61, 72, 84) 
    y = Array(472, 460, 440, 416, 386, 350, 305, 260, 215, 170, 125, 89, 59, 35, 15, 3, 1) 
    z = Array(x, y) 
    MsgBox Application.HLookup(lookvalue, z, 2) 
End Sub 

[XLdude]

Fantastic.
That was the solution I was originally pursuing.
Thanks to Norie.

Also, the looping method offered by jindon is a good demonstration and I recommend any newbies try that code, step through it while watching Locals variables window - very educational. Be sure to expand the array variables as they are poplulated. I learned a lot from that one.

Before I saw Norie's solution,
I suspected that all that looping was calculation and time intensive and I came up with a 3rd alternative, which has no looping and uses only statements-

3rd Alternative Method:

Sub test3() 
    Dim x, y, lookvalue, valindex 
    lookvalue = 20 
    x = Array(-90, -84, -72, -61, -50, -39, -28, -17, -6, 6, 17, 28, 39, 50, 61, 72, 84) 
    y = Array(472, 460, 440, 416, 386, 350, 305, 260, 215, 170, 125, 89, 59, 35, 15, 3, 1) 
    valindex = Application.Match(lookvalue, x, 1) 
    MsgBox Application.Index(y, 1, valindex) 
End Sub 

Thanks everyone.
-Dave