Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    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
    Posts
    31,707

    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
    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
    Posts
    8,267

    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
    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
    Posts
    10,539

    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
    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 HELP
    Replies: 4
    Last Post: August 27th, 2008, 05:44
  2. Assign Listbox Values To Two Dimensional Array
    By Richnl in forum EXCEL HELP
    Replies: 5
    Last Post: February 13th, 2007, 17:33
  3. Sort Array Assign Values To Controls
    By prsthlm in forum EXCEL HELP
    Replies: 3
    Last Post: November 13th, 2006, 19:15
  4. Dynamic Array: Multidimensional
    By neis in forum EXCEL HELP
    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