Type Mismatch VBA array

Question

I keep getting a type mismatch error when I try to run my code. I'm trying to store number in an array and then calculate their sum.

Sub Exercise()

    Dim Sum As Integer

    Dim A(1 to 5) As Integer
    A(1) = 35
    A(2) = 71
    A(3) = 42
    A(4) = 53
    A(5) = 109

    Sum = 0
    Sum = (Sum + A)

    For A = 1 To 5
    Next Sum

    MsgBox Sum

End Sub

grovesNL · Accepted Answer · 2014-11-12 01:49:43Z

4

I believe you meant to do this:

Sub Exercise()
    Dim Sum As Integer
    Dim i As Integer
    Dim A(1 To 5) As Integer

    A(1) = 35
    A(2) = 71
    A(3) = 42
    A(4) = 53
    A(5) = 109

    Sum = 0
    For i = 1 To 5
        Sum = (Sum + A(i))
    Next i

    MsgBox Sum
End Sub

Notice the differences. You set Sum = 0 prior to the loop, then use an iteration variable i to loop through the values of A by calling them using A(i). This adds each A(i) to the running total defined by Sum.

answered Nov 12, 2014 at 1:49

grovesNL

6,1152 gold badges22 silver badges32 bronze badges

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

Comments

Mathieu Guindon · Accepted Answer · 2014-11-12 01:47:50Z

1

You can't use A as your loop counter, it's your array.

You probably intend to do this:

Dim i As Integer
For i = 1 To 5
    Sum = Sum + A(i)
Next

Note that Sum is already initialized to 0, so this line is superfluous:

Sum = 0

And the line that tries to assign Sum = (Sum + A) can just be removed, you can't add an Integer with an array.

answered Nov 12, 2014 at 1:47

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Collectives™ on Stack Overflow

Type Mismatch VBA array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related