Excel VBA worksheet sum function returns error when one of values is text

Question

I get this error: "Run-time error '1004'"" Unable to get the Sum property of the Worksheetfunction class

I have a code that looks into multiple comboboxes in a userform and sums their values that the user selects. The code was working fine with only numbers in the combobox list. I recently added one text option into the list and I called it "Not applicable". However, once I run the code I get the above error.

I believe the error is because I introduced the text option, and the sum function in VBA wouldn't recognize it.

Is there anyway to solve this issue?

Thanks

Here is the code I used:

Dim totalscore As Double
totalscore = WorksheetFunction.Sum(frmQA.ComboBox3.Value, frmQA.ComboBox4.Value, frmQA.ComboBox5.Value, frmQA.ComboBox6.Value, frmQA.ComboBox7.Value, frmQA.ComboBox8.Value, frmQA.ComboBox9.Value)

Scott Holtzman · Accepted Answer · 2017-02-28 14:18:39Z

3

Assuming the text option is available in ComboBox3 adjust this part of the sum formula:

frmQA.ComboBox3.Value

To this:

Iif(IsNumeric(frmQA.ComboBox3.Value),frmQA.ComboBox3.Value,0)

If they all have the text option you can do this:

totalscore = totalscore + Iif(IsNumeric(frmQA.ComboBox3.Value),frmQA.ComboBox3.Value,0)
totalscore = totalscore + Iif(IsNumeric(frmQA.ComboBox4.Value),frmQA.ComboBox4.Value,0)
...

answered Feb 28, 2017 at 14:18

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

exlover Over a year ago

Hi Scott thanks your iif(Isnumeric) command was very helpful here is the code that I used

exlover Over a year ago

Dim totalscore As Double totalscore = WorksheetFunction.Sum(IIf(IsNumeric(frmQA.ComboBox3.Value), frmQA.ComboBox3.Value, 0), IIf(IsNumeric(frmQA.ComboBox4.Value), frmQA.ComboBox4.Value, 0), IIf(IsNumeric(frmQA.ComboBox5.Value), frmQA.ComboBox5.Value, 0), IIf(IsNumeric(frmQA.ComboBox6.Value), frmQA.ComboBox6.Value, 0), IIf(IsNumeric(frmQA.ComboBox7.Value), frmQA.ComboBox7.Value, 0), IIf(IsNumeric(frmQA.ComboBox8.Value), frmQA.ComboBox8.Value, 0), IIf(IsNumeric(frmQA.ComboBox9.Value), frmQA.ComboBox9.Value, 0))

Scott Holtzman Over a year ago

glad it worked. please mark as answered so others know going forward.

exlover Over a year ago

how to do that?

Scott Holtzman Over a year ago

there's a grey check mark right below the downvote arrow (also grey). click that @exlover

Comintern · Accepted Answer · 2017-02-28 15:06:12Z

0

It may be better to verify whether the entry made, by the user, is a text or numeric value before processing is done.

From a cursory glance at the code - I can surmise that the "text value" may go along the lines of '123 (see apostrophe mark).It's possible, but that would be a conscientious effort by the end user.

So,therefore IMHO, it would be better to run a validation check before the calculation (as suggested by Scott Holtzman).

"Not Applicable" - either use the number zero (0) in the code or somehow get your VBA to map "Not Applicable" and change this entry to zero(0) value.

Pseudo code example:

IF frmQA.ComboBox3.Value = "NOT Applicable" then

        frmQA.ComboBox3.Value = 0
END IF

The above suggestion can be placed in a worksheet event, perhaps

Best of luck

Regards

RDFS

edited Feb 28, 2017 at 15:06

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

answered Feb 28, 2017 at 14:46

RDFS

215 bronze badges

Comments

exlover · Accepted Answer · 2017-03-01 16:03:57Z

0

Here is the answer modified as suggested by Scott

Dim totalscore As Double
totalscore = WorksheetFunction.Sum(IIf(IsNumeric(frmQA.ComboBox3.Value), frmQA.ComboBox3.Value, 0), IIf(IsNumeric(frmQA.ComboBox4.Value), frmQA.ComboBox4.Value, 0), IIf(IsNumeric(frmQA.ComboBox5.Value), frmQA.ComboBox5.Value, 0), IIf(IsNumeric(frmQA.ComboBox6.Value), frmQA.ComboBox6.Value, 0), IIf(IsNumeric(frmQA.ComboBox7.Value), frmQA.ComboBox7.Value, 0), IIf(IsNumeric(frmQA.ComboBox8.Value), frmQA.ComboBox8.Value, 0), IIf(IsNumeric(frmQA.ComboBox9.Value), frmQA.ComboBox9.Value, 0))

answered Mar 1, 2017 at 16:03

exlover

671 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

Excel VBA worksheet sum function returns error when one of values is text

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related