VBA How to display array in message box

Question

VBA

Hi I have been trying create an array then display in a msgbox but keep getting this error:

'Invalid procedure call or argument'

I have used the Join function on another array and it works

Sub rangearray()

Dim array1 As Variant

array1 = Range("a1:z1")

MsgBox Join(array1, vbCrLf)

End Sub

You need to provide more information. Also, please tag the question so we can understand which language you're talking about. — user4454229
– user4454229, Commented Jan 23, 2015 at 11:23

Caio César S. Leonardi · Accepted Answer · 2015-01-26 11:25:04Z

15

I've just tested it on a simple way on VB6 and it worked with this:

Dim arr(3) As String

arr(1) = "Test"
arr(2) = "Test 2"
arr(3) = "Test 3"

MsgBox Join(arr, vbCrLf)

are you certain that your function 'Range("a1:z1")' it's actually returning an array object to the variant 'array1'?

EDIT: you can't pass a multi-dimensional array on Join function, it has to be an One-dimensional to work properly. So, if the expression array1(1,1) returns an value, that's your problem.

edited Jan 26, 2015 at 11:25

answered Jan 23, 2015 at 11:30

Caio César S. Leonardi

5367 silver badges14 bronze badges

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

3 Comments

Aadil Over a year ago

I tried your script and it worked, I don't what the issue is with using range?

Caio César S. Leonardi Over a year ago

i don't know either what that function is returning, but the point is, put a break point on the MsgBox line. Then, check the value of 'array1', it should be an array, because the "Join" function is expecting an array.

Caio César S. Leonardi Over a year ago

you can't pass a multi-dimensional array on Join function, it has to be an One-dimensional to work properly. So, if the expression array1(1,1) returns an value, that's your problem.

Collectives™ on Stack Overflow

VBA How to display array in message box

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related