Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    I want to be able to enter a custom function on a spreadsheet with a given range as the argument e.g.
    <font face="Georgia">=myFunction(A1:A10)</font face=georgia>

    I would like to be able to read the contents of the given range into a VBA array.
    Any suggestions <img src=/S/question.gif border=0 alt=question width=15 height=15>

    (I'm)stuck

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    The other way around is well described on Chip Pearson's excellent website, see this page
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    Try <pre>Function MyFunc(rng As Range) As Variant
    ReDim FuncArray(rng.Cells.Count)
    For i = 0 To UBound(FuncArray) - 1
    FuncArray(i) = rng.Cells(i + 1).Value
    Next
    '.....
    'MyFunc = ....
    End Function</pre>


    As a range of cells is effectively an array, do you need to convert ?

    Andrew C

  4. #4
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    Thanks, I will try that.

    As for needing to convert, I don't know, I'm in uncharted waters here. I'm coming at it from this direction because my core code is an iteration.

    The loop starts with the initial data being copied and some 'constants' derived from this copied data. Each value in this copy is compared to the 'constants' and depending on the outcome left alone or updated. The 'constants' are then recalc'd from this updated data and the starting and ending value of the 'constants' are compared. If the convergence is not enough it goes round the loop again. Otherwise it exits, there is also a check to prevent it getting stuck if the thing doesn't converge.

    That is to say I want to use the values in my selected range but I don't want to mess with them on my sheet.

    (not sure if I'm still) stuck

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    Ta, lots to <img src=/S/munch.gif border=0 alt=munch width=19 height=17> on there!

    (probably not) stuck

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

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    You might consider using a For Each statement:

    <pre>Public Function MyFunc(oRange As Range) As Variant
    Dim oCell as Range, lSum As Long
    For Each oCell In oRange
    lSum = lSum + oCell.Value
    Next oCell
    MyFunc = lSum
    End Function
    </pre>

    Legare Coleman

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    Hi,
    You can just assign the range to a variant - e.g.:
    <pre>Function testarray(rngInput As Range)
    Dim myArray
    myArray = rngInput
    testarray = UBound(myArray)
    End Function
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    Rory,

    I had assumed your approach would work, and indeed UBound(myArray) and LBound(myArray) do return the correct values as though the elements of rngInput were assigned to myArray. Yet using a loop to get individual element values returns a subscript out of range error for any value of the loop counter.

    Andrew

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    Andrew,
    Sorry, I should have mentioned that the assignment creates a 2 dimensional, 1 based array. The Ubound and Lbound work as is because they default to the value for the first dimension (rows)- the second dimension is the number of columns. So you need to use myArray(row, column) to return any of the values.
    My apologies, I should have specified that in my post.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    After assigning it to the range, myArray is doubly dimensioned. Try testarray = myArray(1, 1). Chip Pearson has some good info, see http://www.cpearson.com/excel/returnin.htm
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    Rory,

    I see that now. Thanks.

    Andrew

  12. #12
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    AH! (need a light bulb smilie here) This helps to explain my problem in <post#=196648>post 196648</post#>. There I couldn't make a copy of an array by simply saying:
    MyArray2 = MyArray1

    I subsequently discovered it was because I had already declared MyArray2 as an array. This was giving a 'can't assign to an array' error but if I didn't explicitly declare it as an array, it copied no problem.

    Another step up the ladder.

    (more stumbling around than) stuck

  13. #13
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    Thank you Legare, your idea did the job.

    I've wrapped it round a Do While loop so I can check the contents of each cell:
    For Each oCell in oRange
    Do While oCell.Value <> ""
    ....
    Loop
    Next

    This is fine as far as it goes but I'd prefer the test condition on my loop to only be true if the cell contents is a number, i.e. ignore empty cells or cells that contain text.

    I've tried:
    While IsNumeric(oCell) = True

    and some variations on that theme but haven't fallen over the correct syntax yet. Can anybody point me in the right direction?

    (I'm) stuck (again)

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    The While ... Wend loop will end on the first non-numeric cell. It would help to see the relevant part of your code, but do you want to do something like:

    For Each oCell in Range
    if IsNumeric(oCell.Value) Then <do whatever>

    ... etc. (BTW, ".Value" is implied if you don't use it, but I always use it to remind myself what I'm doing.)
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: passing a range of cells to a VBA array (XL97 sr2 on Win 2000 sp2)

    I always use the .Value because I haven't found Excel to be 100% consistent in properly applying its defaults.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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