Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jan 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function Returning an Array? (Excel 2003)

    Hello all,

    I have this code which is nearly optimised (well - optimised relative to my beginner's framework...). The last task before reaching (relative) esthetic perfection is to "functionalise" (if such a word exists) the following piece of code which I run repeatedly throughout:

    ReDim Array(n) As Single
    For i = 0 To UBound(Array)
    Array(i) = Range("Array_InputRange").Offset(0, i).Value
    Next i

    Ideally I would like to define a function called, say, Pop_Array, which takes as its argument the array's input range, and populates the array directly, so that the above line of code would become simply:

    ReDim Array(n) as Single
    Array = Pop_Array("Array_InputRange")

    (You can assume n has been previously calculated to correspond to the number of cells in "Array_InputRange".)

    Does anybody know how to do this? I know there is an Excel-defined function called Array, but I don't seem to be able to use that for my ends.

    Thanks in advance,

    Alexander

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function Returning an Array? (Excel 2003)

    Welcome to Woody's Lounge!

    You don't need a function. Simply declare your variable as a variant:

    Dim varArray As Variant
    varArray = Range("Array_InputRange")

    Note: the result will ALWAYS be a two-dimensional array, even if the range consists of only one column or one row. To retrieve an element of the array, you must provide a row index and a column index:

    varArray(2, 1)

  3. #3
    New Lounger
    Join Date
    Jan 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Returning an Array? (Excel 2003)

    Thanks a lot Hans that looks great.

    One more question (probably very naive): doesn't a Variant array consume more memory than a Single array? Is there no way to avoid using a Variant?

    Thanks in advance

    Alexander

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function Returning an Array? (Excel 2003)

    Yes, storing a variant involves some overhead, because information about the actual data type has to be stored alongside the data themselves. This also means that processing a variant is theoretically slower than processing a typed variable with the same contents, because the VBA interpreter has to check the data type. But with the amount of memory and the processor speed in today's PCs, you probably won't notice the difference unless you are working with very large arrays.

    If you want to avoid variants, you can do something like this:

    Sub PopArray(sngArray() As Single, oRange As Range)
    ReDim sngArray(1 To oRange.Rows.Count, 1 To oRange.Columns.Count)
    Dim i As Long
    Dim j As Long
    For i = 1 To oRange.Rows.Count
    For j = 1 To oRange.Columns.Count
    sngArray(i, j) = oRange.Cells(i, j)
    Next j
    Next i
    End Sub

    Note that PopArray is not a function, but a procedure that modifies the sngArray argument passed to it. Example:

    Sub TestIt()
    Dim sngTest() As Single
    PopArray sngTest, Range("A1:A4")
    Debug.Print sngTest(2, 1)
    Erase sngTest
    End Sub

    Since sngtTst and sngArray are declared as Single arrays, you are repsonsible for supplying a range that contains only numeric values (or blanks). Text values will cause a Type Mimatch error.

  5. #5
    New Lounger
    Join Date
    Jan 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Returning an Array? (Excel 2003)

    Hans - thanks!

    One last question: does modifying sngArray in PopArray mean this will be recognised in the main sub?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function Returning an Array? (Excel 2003)

    Yes. Arguments in procedures and functions in VBA are by reference unless you explicitly specify them as by value.

    By reference means that the procedure or function works with the "original" of the variable. By value means that the procedure or function uses a local copy, so that the original will not be modified.

    To specify the way a argument is passed, use ByRef or ByVal. If omitted, ByRef is assumed.

  7. #7
    New Lounger
    Join Date
    Jan 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Returning an Array? (Excel 2003)

    Thanks Hans, I have implemented this and my code is now as streamlined as I can see it being.

    Regards,

    Alexander

  8. #8
    New Lounger
    Join Date
    Jan 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Returning an Array? (Excel 2003)

    Why is it array arguments can only be ByRef?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function Returning an Array? (Excel 2003)

    Why? I don't know. If you click Help in the error message, you'll get some information, but not a reason.

    Variants to the rescue again: you can specify a ByVal Variant argument, and pass an array into it:

    Sub Caller()
    Dim arr(1 To 4) As Integer
    arr(3) = 5
    Debug.Print arr(3)
    Process arr
    Debug.Print arr(3)
    End Sub

    Sub Process(ByVal arr As Variant)
    arr(3) = 9
    Debug.Print arr(3)
    End Sub

    Running Caller will result in
    5
    9
    5

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Returning an Array? (Excel 2003)

    There is no explanation from MS as Hans said. However, as an old programmer, I will take a guess. When parameters are passed by value, then the value itself must be pushed onto the processor stack. Since arrays can be of different sizes, and can contain very large numbers of values, this would create all kinds of programming problems and potential for error conditions like stack overflow.
    Legare Coleman

  11. #11
    New Lounger
    Join Date
    Jan 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Returning an Array? (Excel 2003)

    Hans, Legare -

    Thanks a lot for all your help. I have lots of offshoot questions at this point, none of them practical, so I guess I should just educate myself!

    Regards,

    Alexander

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function Returning an Array? (Excel 2003)

    You're welcome to ask if and when you want. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Returning an Array? (Excel 2003)

    As a (getting) old programmer, I'd tend to agree with your explanation Legare. Back in the 60s or whenever it happened, the great software disaster initiated a major rethink about programming and programming languages. One of the problems identified was programmers passing copies of arrays (often unnecessarily) to functions, creating huge drains on memory resources. If you start thinking multi-dimensional arrays being copied by routines that are called recursively, it's not hard to envisage the results. The solution was to rewrite programming languages to enable the passing of array variables only by reference, and this appears to have "stuck", with good reason.

    Other things identified as contributing to the "disaster" were the use of global variables, undeclared variables (like Hans always says, set "Option Explicit" as the default), untyped variables (purists would frown on the Variant type allowed in VB, and certainly at typeless script language implementations) and the indiscriminant use of "Go To" statements, often as "Get out of Jail cards".

    <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    Alan

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function Returning an Array? (Excel 2003)

    I know little/ nothing of the VB .NET language, but I seem to recall the mention of it implementing RTTI (runtime type identification) like C++ has provided for aeons. It certainly represents a better programming practice than the way Variants are dealt with in VB. Do you (or anybody else) know any references to this feature?

    Alan

Posting Permissions

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