Results 1 to 15 of 19
Thread: VBA Arrays (Excel 97)

20040315, 20:16 #1
 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?

20040315, 21:17 #2
 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.

20040315, 22:29 #3
 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 builtin 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.

20040316, 01:11 #4
 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

20040316, 14:11 #5
 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.

20040316, 14:20 #6
 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.jkpads.com
Professional Office Developers Association

20040316, 14:54 #7
 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?

20040316, 15:08 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20040322, 19:03 #9
 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?

20040322, 21:02 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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?

20040322, 21:08 #11
 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.

20040322, 21:20 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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

20040323, 13:16 #13
 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.

20040323, 13:47 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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?

20040323, 17:41 #15
 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.