Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: assign constant values to multidimensional array in VBA

1. I agreed to these rules
Join Date
27th January 2005
Location
USA, Oregon
Posts
13

## assign constant values to multidimensional array in VBA

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:
VB:
```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
VB:
```
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:
VB:
```
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?

Dave
Last edited by XLdude; October 31st, 2005 at 10:40.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: assign constant values to multidimensional array in VBA

Hi XlDude

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

3. I agreed to these rules
Join Date
27th January 2005
Location
USA, Oregon
Posts
13

## Re: assign constant values to multidimensional array in VBA

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

if you were suggesting this---
VB:
```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:
VB:
```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.

-Dave
[ps: 100 hacks book is Great!]

Excel Video Tutorials / Excel Dashboards Reports

4. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

## Re: assign constant values to multidimensional array in VBA

Hi
You need to create multi-dim-array
VB:
```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

```

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
27th January 2005
Location
USA, Oregon
Posts
13

## Re: assign constant values to multidimensional array in VBA [Solved]

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

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: assign constant values to multidimensional array in VBA [Solved]

No need to loop.
VB:
```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

```

Excel Video Tutorials / Excel Dashboards Reports

7. I agreed to these rules
Join Date
27th January 2005
Location
USA, Oregon
Posts
13

## Re: assign constant values to multidimensional array in VBA

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:

VB:
```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

Excel Video Tutorials / Excel Dashboards Reports

There are currently 2 users browsing this thread. (0 members and 2 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno