Simple way to sum contents of an array? 2

[Chats]

Sorry if this is a daft question...

To sum the values stored in an array, I currently use a For...Next loop. I can't help but think there must be a smarter and better way?

I am using Excel 2000. Thanks for any better suggestions!

Ade


Private Sub test()
Dim abc(0 To 2) As Integer
Dim x As Integer


abc(0) = 5
abc(1) = 10
abc(2) = 20

For n = 0 To 2
x = x + abc
Next n

MsgBox x

End Sub

 

[BrainB]

Try :-

x = Application.WorksheetFunction.Sum(abc)

Regards
BrianB
Use CupOfCoffee to speed up all windows applications.
It is easy until you know how.
================================

 

[EBGreen]

Or to avoid instantiating an application object, try something like:

MsgBox Eval(Join(abc, "+"))

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Chats]

BrianB - thanks, this works & is neat...

TomThumbKB - When I try your code I get an error "sub or function not defined" - VBA is not recognising 'Eval'

Could you tell me where I am going wrong?

 

[TonyJollans]

Hi Chats,

Try Eval[red]uate[/red] instead of Eval.

I'm not sure that using Worksheet functions involves any instantiation, and Evaluate belongs to the Application anyway, but TomThumb's solution is still a neat one.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Chats]

Hmmm, still getting an error, this time I get "Invalid Procedure call or argument"

my code is

Private Sub test()
    Dim abc(0 To 2) As Integer
    Dim x As Integer
    
    
    abc(0) = 5
    abc(1) = 10
    abc(2) = 20


    
    x = Evaluate(Join(abc, "+"))
    MsgBox x
    
End Sub

Can anybody spot where I am going wrong with this?

 

[TonyJollans]

Hi Chats,

The [blue]Join[/blue] and [blue]Evaluate[/blue] functions work with [blue]String[/blue] arguments. You cannot do it with Integers, which may, of course, mean its unsuitable for your requirements.


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[Chats]

Thanks everyone so far for help on this, I now have a new level of complexity which I hope somebody can help with.

Going back to Brian's suggestion

x = Application.WorksheetFunction.Sum(abc)

I got the code in my original post to work fine. Now for the extra complication - I now wish to have the array abc as a 2 dimensional array, and sum the values only in the first column. Here is my code

Private Sub test()
    Dim abc(0 To 2, 0 To 9) As Integer
    
    Dim x As Integer
   
    abc(0, 0) = 5
    abc(1, 0) = 10
    abc(2, 0) = 20
    abc(2, 1) = 100
 
    
    x = Application.WorksheetFunction.Sum(abc)

    MsgBox x

End Sub

What happens is that I get an answer of 135 (which is correct as the code goes), what I want is a way to just sum up abc(0,0), abc(1,0), abc(2,0) - but exclude the value for abc(3,1) - this would give me a value of 35.

I would actually like my array in the real application to be a variant, with numeric data in the first column, and text in the second column, and have a formula which sums up the values in the first column, without being upset by the text in the second column.

I am now seriously out of my depth - can anybody help?

Thanks

Ade