Announcement

Collapse
No announcement yet.

assign constant values to multidimensional array in VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    Code:
    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
    Code:
    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:
    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
    Last edited by XLdude; October 31st, 2005, 11:40.

  • #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?

    Comment


    • #3
      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---
      Code:
      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:
      Code:
      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!]

      Comment


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

        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

        Comment


        • #5
          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

          Comment


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

            No need to loop.
            Code:
            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
            Boo!

            Comment


            • #7
              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:

              Code:
              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

              Comment

              Working...
              X