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



## JenniferMurphy (Monday at 11:39 PM)

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


----------



## Rick Rothstein (Tuesday at 12:03 AM)

Assuming your array is one-dimensional, is named Arr and your text never has the pipe symbol (|) in it...

Arr = Split(Ucase(Join(Arr, "|")), "|")


----------



## JenniferMurphy (Tuesday at 1:54 AM)

Rick Rothstein said:


> Assuming your array is one-dimensional, is named Arr and your text never has the pipe symbol (|) in it...
> 
> Arr = Split(Ucase(Join(Arr, "|")), "|")



The array is the result of loading a range from the active sheet. Even though the range is a string of cells on one rwo, the resulting VBA array has 2 dimensions.

```
Dim arr as Variant
arr = range("C5:C20").value2
```

Your expression gets a Run-time error "5".


----------



## Peter_SSs (Tuesday at 2:43 AM)

What about ..


```
Dim arr As Variant
arr = Evaluate("upper(C5:C20)")
```


----------



## JenniferMurphy (Tuesday at 3:09 AM)

Peter_SSs said:


> What about ..
> 
> 
> ```
> ...


Wow! That is slick! And arr is now a 1-dimensional array, which is easier to work with. I was getting 2-dimensional arrays before.

Thank you! 👏👏👍👍😍😍


----------



## mohadin (Tuesday at 3:09 AM)

Hi

```
Dim arr as Variant
arr = range("C5:C20").value2
```

Should be 

```
arr = Application.Transpose(Range("C5:C20").Value2)
```
Then As 
Rick Rothstein mentioned​

```
Arr = Split(Ucase(Join(Arr, "|")), "|")
```
Will do the job with no errors


----------



## JenniferMurphy (Tuesday at 3:21 AM)

mohadin said:


> Hi
> 
> ```
> Dim arr as Variant
> ...


When I do that, transpose changes it from a 1x4 to a 4x1 array. Rick's expression still gets an error for me.

Peter's solution does everything perfectly.


----------



## Peter_SSs (Tuesday at 3:31 AM)

JenniferMurphy said:


> And arr is now a 1-dimensional array, which is easier to work with. I was getting 2-dimensional arrays before.


Still a 2-dimensional array for me.


----------



## JenniferMurphy (Tuesday at 3:37 AM)

Peter_SSs said:


> Still a 2-dimensional array for me.
> 
> View attachment 82410


Curious. Here's three lines from my Immediate wondow:


```
arr=evaluate("upper(D4:G4)")
?ubound(arr,1)
 4
?ubound(arr,2)
```

That last instruction gets a "subscript out of range" error.


----------



## RoryA (Tuesday at 3:52 AM)

You changed the range from a column to a row.


----------



## JenniferMurphy (Monday at 11:39 PM)

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 (Tuesday at 3:53 AM)

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 (Tuesday at 4:16 AM)

JenniferMurphy said:


> 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 (Tuesday at 4:17 AM)

RoryA said:


> 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 (Tuesday at 4:19 AM)

mohadin said:


> Try this
> 
> ```
> Sub test()
> ...


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


----------



## JenniferMurphy (Tuesday at 4:20 AM)

Peter_SSs said:


> Rory has provided the explanation.
> To give a choice:
> 
> ```
> ...


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


----------



## RoryA (Tuesday at 6:10 AM)

JenniferMurphy said:


> 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 (Tuesday at 11:20 AM)

RoryA said:


> 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 (Tuesday at 11:37 AM)

I couldn't agree more.


----------

