Print Range value inside MsgBox without loop in VBA

Question

I have a problem with Excel vba that I want to print range value inside message box like

MsgBox "Sheets("sheet1").Range("A1:D1")

I know it is possible with loop like with cell like

for i=1 to 4
MsgBox "Sheets("Sheet1").Cells(1,i)
next i

Is there any way that I can be able to perform this Operation with out loop in VBA

thanks

Solution

You do not need a loop:

Sub test()
    Dim r As Range, ary
    Set r = Sheets("Sheet1").Range("A1:D1")
    ary = Application.Transpose(Application.Transpose(r.Value))
    MsgBox Join(ary, " ")
End Sub

Or:

Sub test()
    Dim r As Range, ary
    Set r = Sheets("Sheet1").Range("A1:D1")
    With Application
         MsgBox Join(.Transpose(.Transpose(r)), " ")
    End With
End Sub

EDIT#1:

If you have Excel 365 you could use:

Sub test365()
    Dim r As Range, ary
    Set r = Sheets("Sheet1").Range("A1:D1")
    With Application
         MsgBox .TextJoin(" ", True, r)
    End With
End Sub