Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Arrays (Excel 97)

    I'm new to VBA and I wanted to know what the most efficient way of summing the contents of a named array based on the constraints (text and or numeric) of more than one other named array?

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Arrays (Excel 97)

    There is no programmatic semantic connection between two different arrays.

    Your program logic would need to establish any application specific connection between the arrays.

    In some cases, it may be easier to use a multidensional array to make processing such connections easier.

    As I recall, John Walkenbach's Power Programming book has some discussion of dealing witharrays.

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA Arrays (Excel 97)

    If I needed to do that and couldn't use Excel's built-in tools, I probably would use an ADO recordset. You can create a "disconnected" recordset even without a preexisting database, stuff your data into that, and use a variety of powerful tools to work with recordsets that simply are not available for arrays.

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

    Re: VBA Arrays (Excel 97)

    We need more information before answering this question.

    1- What do you mean by "named array"? Is this a VBE array variable (ie. DIM MyArray(100) as long), or are you talking about a named range of cells on a worksheet?

    2- Could you be a little more specific about what cinstraints you are talking about? A small example might help.

    3- Does the sum need to be done in VBA, or can it be done on the worksheet if the arrays are really named ranges of cells.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Arrays (Excel 97)

    1. Just a named column (or range) of cells. For example: AccruedInterest. And AccruedInterest is one column with say 500 rows (or cells).

    2. Suppose that I have three columns: AccruedInterest, MonthDays and Category. Category is text and AccruedInterest and MonthDays are both numeric. I want to sum AccruedInterest given that MonthDays is a positive number and Category is equal to a letter, say "c". I've done this with a function on a worksheet, but I'm thinking that I can make it more mallable if I do it in VBA.

    3. I've done this calcualtion in worksheet. However, there are other things that I want to do with this and other data that I have and I would prefer a VBA type program. The functions that I have written in the worksheet have become somewhat cumbersome.

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

    Re: VBA Arrays (Excel 97)

    So this function would do:

    =SUMPRODUCT(AccruedInterest*(Category="c")*(MonthD ays>0))

    I fail to see what is cumbersome here.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Arrays (Excel 97)

    Thanks. I was responding to a request for a small example.

    There are other data that I will be working with and I was hoping that eventually I could add, for example, a pulldown menu to run particular reports off of my data without having the functions directly on the data worksheet.

    I initially used a nested "if" statement to do what you did, but yours is obviously better.

    Are you telling me that what I want to do can't be done in VBA?

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

    Re: VBA Arrays (Excel 97)

    Your original question was about "the most efficient way of summing the contents of a named array based on the constraints" - that is the formula posted by Pieterse. But it can be done in VBA, for example:

    Function AddConditional() As Double
    Dim i As Long
    Dim dblResult As Double
    For i = 1 To Range("AccruedInterest").Count
    If Range("Category")(i) = "c" And _
    IsNumeric(Range("Monthdays")(i)) And _
    Range("MonthDays")(i) > 0 Then
    dblResult = dblResult + Range("AccruedInterest")(i)
    End If
    Next i
    AddConditional = dblResult
    End Function

    I'll leave it to you to make this more generic.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Arrays (Excel 97)

    Thanks, I really appreciate the help.

    Suppose I have a named range that is say 100 X 1 vector, or one column with 100 rows (or cells), and I have data entered into <100 of the cells. If I wanted to use a function to do calculations on the entered data and transfer the output to another column vector, how could I constrain the new vector to the same number of entries as the original vector?

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

    Re: VBA Arrays (Excel 97)

    Can you be more specific? Does the "input vector" consist of a number of filled cells, followed by empty cells, or are there gaps in between?

  11. #11
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Arrays (Excel 97)

    The vector would have a number of entries followed by empty cells; no gaps. Thanks.

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

    Re: VBA Arrays (Excel 97)

    You could use something like this:

    Dim rng As Range
    Dim i as Long
    Set rng = Range("AccruedInterest").Cells(1)
    Set rng = Range(rng, rng.End(xlDown))
    For i = 1 To rng.Count
    ' Do something with rng(i)
    Next i

  13. #13
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Arrays (Excel 97)

    Thanks, but that doesn't seem to work. I named an range "AccruedInterest" and entered some values in the range. However when I performed a function routine on the values in AccruedInterest, zeros were placed in the empty cells corresponding to the empty cells in AccruedInterest. I wanted those cells to be blank, like the unused cells in AccruedInterest.

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

    Re: VBA Arrays (Excel 97)

    In your previous reply, you stated
    <hr>The vector would have a number of entries followed by empty cells; no gaps<hr>
    Now it appears that there are gaps?

  15. #15
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Arrays (Excel 97)

    Sorry, I guess I misunderstood your question. There would be no gaps in the data, but the data would not necessarily fill the entire initial range. I thought that you meant that there may be spaces in between the data points and not at the end.

    If the range was one column X ten rows, there may be 5 data points inputted into the cell. What I was looking to do is to be able to use the inputted data to create another range that matched the size of the entered data and not necessarily the size of the original range.

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
  •