Trying to run this code but is giving me an error according to my point of view it should work but unable to understand what is the actual issue.

I hope someone can help.

Error is Compile error: Expected Function or Variable

on this line rng.Value = makeUpper(rng.Value)

Sub Uppercasecells()
    Dim rng As Range

    For Each rng In Selection
        rng.Value = makeUpper(rng.Value)
    Next rng
End Sub

Sub makeUpper(rng As Range)
    Dim v As Long, vUPRs As Variant
    With rng
        vUPRs = .Value2
        For v = LBound(vUPRs, 1) To UBound(vUPRs, 1)
            vUPRs(v, 1) = UCase(vUPRs(v, 1))
        Next v
        .Cells = vUPRs
    End With
End Sub

CodePudding user response：

• makeUpper is a subroutine and does not return anything, unlike a Function.
• makeUpper expects a Range but you're passing it rng.Value.
• vUPRs will not be an array if rng is a single cell.
• Note that you only loop through the first dimension (rows) of vUPRs (left that way if that's intended).

Rewritten:

Sub Uppercasecells()
    makeUpper Selection
End Sub

Sub makeUpper(rng As Range)
    Dim v As Long, vUPRs As Variant

    With rng
        If .CountLarge = 1 Then
            ' create array if rng is a single-cell
            ReDim vUPRs(1 to 1, 1 to 1)
            vUPRs(1, 1) = .Value2 
        Else
            vUPRs = .Value2
        End If

        For v = LBound(vUPRs, 1) To UBound(vUPRs, 1)
            vUPRs(v, 1) = UCase(vUPRs(v, 1))
        Next v

        .Value2 = vUPRs
    End With
End Sub

If you want to loop through the second dimension of vUPRs, then:

    Dim v as Long, w As Long, UPRs as Variant
    ...
        For v = Lbound(vUPRs, 1) to UBound(vUPRs, 1)
            For w = LBound(vUPRs, 2) to UBound(vUPRs, 2)
                vUPRs(v, w) = UCase(vUPRs(v, w))
            Next
        Next