Is there a way to upper case a VBA array without a loop?

[JenniferMurphy]

Is there a way to convert a string array to upper case in a single statement? Or do I have to do it in a loop?

Thanks

 

[mohadin]

Try this

Sub test()
Dim arr As Variant
arr = Split(UCase(Join(Application.Transpose(Range("C5:C20").Value2), "|")), "|")
Range("F5").Resize(UBound(arr)) = Application.Transpose(arr)
End Sub

 

[Peter_SSs]

Curious.

Rory has provided the explanation.
To give a choice:

Dim arr As Variant

'1-d array from column
arr = Application.Transpose(Evaluate("upper(C5:C20)"))

'1-d array from row
arr = Evaluate("upper(D4:G4)")

 

[JenniferMurphy]

You changed the range from a column to a row.

So I did. Crap. IMHO, that should not make one d*amned bit of difference. If C5:C20 is a 16x1 array, then C5:X5 should be a 1x22 array. This is what is so maddening about Excel and VBA.

arr=evaluate("upper(C5:C20)")
?ubound(arr,1)
 16
?ubound(arr,2)
 1
arr=evaluate("upper(C5:X5)")
?ubound(arr,1)
 22
?ubound(arr,2)
'Error subscript out of range

In any case, Peter's solution still works on my row range, but I will have to deal with a 2-dimensional array.

 

[JenniferMurphy]

Try this

Sub test()
Dim arr As Variant
arr = Split(UCase(Join(Application.Transpose(Range("C5:C20").Value2), "|")), "|")
Range("F5").Resize(UBound(arr)) = Application.Transpose(arr)
End Sub

Thanks. That probably works, but Peter's solution is easier for me to grasp. Thanks

 

[JenniferMurphy]

Rory has provided the explanation.
To give a choice:

Dim arr As Variant

'1-d array from column
arr = Application.Transpose(Evaluate("upper(C5:C20)"))

'1-d array from row
arr = Evaluate("upper(D4:G4)")

Perfect. I've moved the solution to here. Thanks.

 

[RoryA]

If C5:C20 is a 16x1 array, then C5:X5 should be a 1x22 array

They are.

The issue only actually arises with the other functions being applied.

 

[JenniferMurphy]

They are.

The issue only actually arises with the other functions being applied.

Ok, then if evaluate("upper(C5:C10)") is a 6x1 array then evaluate("upper(C5:H5)") should be a 1x6.

 

[RoryA]

I couldn't agree more.