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

Answer 1

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.