Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: assign constant values to multidimensional array in VBA

  1. #1
    Join Date
    27th January 2005
    Location
    USA, Oregon
    Usergroup
    Registered Users
    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?

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Usergroup
    Administrators
    Posts
    31,885

    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. #3
    Join Date
    27th January 2005
    Location
    USA, Oregon
    Usergroup
    Registered Users
    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.

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Usergroup
    OzMVP
    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. #5
    Join Date
    27th January 2005
    Location
    USA, Oregon
    Usergroup
    Registered Users
    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. #6
    Join Date
    14th July 2004
    Usergroup
    OzMVP
    Posts
    10,549

    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. #7
    Join Date
    27th January 2005
    Location
    USA, Oregon
    Usergroup
    Registered Users
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Array Constant From Cell Values
    By ertuoc in forum Excel General
    Replies: 4
    Last Post: August 27th, 2008, 05:44
  2. Assign Listbox Values To Two Dimensional Array
    By Richnl in forum Excel General
    Replies: 5
    Last Post: February 13th, 2007, 17:33
  3. Sort Array Assign Values To Controls
    By prsthlm in forum Excel General
    Replies: 3
    Last Post: November 13th, 2006, 19:15
  4. Dynamic Array: Multidimensional
    By neis in forum Excel General
    Replies: 1
    Last Post: July 20th, 2005, 13:37
  5. Replies: 3
    Last Post: June 12th, 2004, 19:40

Bookmarks

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