CODEWITHSUNDEEP
vbams-word
Beaker McChemist
Beaker McChemist

Reputation: 55

How to sum a list of numbers in vba?

I have a vba function in my Word macro that returns a comma separated list of numbers as a string. For example; 

var = (1.5, 2, 3, 5)

How would I add them up to find the sum?

Upvotes: 0

Views: 1745

Answers (2)

QHarr
QHarr

Reputation: 84465

replace "," with "+" and use Evaluate formula on string. Note: Have to create reference to Excel instance which is a little heavy handed to say the least! I can't find a word equivalent of Evaluate function which seems a little odd:

Option Explicit
Public Sub test()
    Dim inputStr As String, oXL As Object
    inputStr = "1.5, 2, 3, 5"
    With CreateObject("Excel.Application")
    MsgBox .Evaluate(Replace(inputStr, ",", "+"))
       .Quit
    End With
End Sub

Upvotes: 2

41686d6564
41686d6564

Reputation: 19641

You need to split the string into a string array, convert each substring into a number using Val, CDbl, or CSng (based on your requirements) and then add the numbers.

Here's an example:

Dim inputStr As String
inputStr = "1.5, 2, 3, 5"
Dim arr() As String
arr = Split(inputStr, ",")

Dim total As Single     ' Or `Double` if you need more accuracy.
For Each subStr In arr
    total = total + Val(subStr)     ' Use `Val` if you want to ignore non-numeric values.
    'total = total + CSng(subStr)   ' Use `CSng` to break when finding non-numeric values.
    'total = total + CDbl(subStr)   ' Use `CDbl` if you need more accuracy.
Next

MsgBox total

Upvotes: 1

Related Questions