VBA Make String Uppercase with UCase: Examples and Tips

VBA & macros · Updated November 18, 2026

To make a string uppercase in VBA, use the built-in UCase function: assign UCase("hello") to a variable and it returns HELLO. For a value you know is not Null, UCase$ is the string-returning form; for an Excel worksheet cell, the equivalent worksheet function is UPPER.

Use UCase to convert a string

UCase takes a string expression and returns a version with lowercase letters changed to uppercase. Existing uppercase letters, digits, punctuation, and spaces are left as they are. The function does not change its input variable in place: assign the returned value if you want to keep the result.

Sub ShowUppercaseExample()
    Dim sourceText As String
    Dim upperText As String

    sourceText = "Quarterly report"
    upperText = UCase(sourceText)
    MsgBox upperText
End Sub

The message box displays QUARTERLY REPORT. This short example works in Excel VBA as well as other VBA hosts, although the host determines where the code runs and how you start it. You can also use the function directly in an assignment, such as customerCode = UCase(customerCode), to replace the contents of a string variable with its uppercase version.

What UCase and UCase$ return

VBA provides both UCase and UCase$. The first returns a Variant containing a string; the dollar-sign form returns a String. For ordinary text held in a String variable, either form is suitable. The dollar sign is part of the function name, not a decoration to add to the output. In practice, choose the form that fits the type of value your code expects, and use UCase when working with a Variant that might hold a special value such as Null.

Neither form changes the original string behind the scenes. VBA strings are values, so the result has to be assigned, returned, or passed elsewhere. For example, calling UCase(sourceText) without using its result does not update sourceText. This is a frequent source of confusion when converting an existing variable.

Convert text from a cell or variable

In a macro, read a cell value, convert it, and then decide where the result should go. The following example reads a cell into a String variable and writes the uppercase result to another cell:

Sub UppercaseOneCell()
    Dim sourceText As String
    Dim upperText As String

    sourceText = CStr(Worksheets("Sheet1").Range("A2").Value)
    upperText = UCase$(sourceText)
    Worksheets("Sheet1").Range("B2").Value = upperText
End Sub

If A2 contains north region, B2 receives NORTH REGION. The example uses CStr to convert the cell value to text before calling UCase$. This is useful when the cell might contain a number or another value that VBA can convert to text. For data that can contain Excel errors or Null values, check the value before converting rather than assuming every cell contains ordinary text.

For a formula-based result that updates when the source changes, with the text to convert in A2, enter =UPPER(A2) in B2. It returns the uppercase version of A2 while leaving the original value untouched. This is an Excel worksheet formula, not a VBA statement. Use it when you want a visible formula in the sheet; use VBA when you need to automate a process or write converted values into cells.

Uppercase a selected range with a macro

If you want to change cell contents in place, a macro can loop through the selected range. This example converts text constants, while leaving formulas, numbers, blank cells, and error values alone:

Sub UppercaseSelectedTextConstants()
    Dim cell As Range

    If TypeName(Selection) <> "Range" Then Exit Sub

    For Each cell In Selection.Cells
        If Not cell.HasFormula Then
            If Not IsError(cell.Value2) Then
                If VarType(cell.Value2) = vbString Then
                    cell.Value2 = UCase$(CStr(cell.Value2))
                End If
            End If
        End If
    Next cell
End Sub

To use it in Excel for Microsoft 365 on Windows, select the cells, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and paste the procedure. Return to Excel, select the cells you want to process, and run the macro from Developer > Macros. If the Developer tab is hidden, show it through File > Options > Customize Ribbon. On Mac, the Visual Basic Editor shortcut is commonly Option+F11 (some keyboards also require Fn); the Developer tab or Tools > Macro > Visual Basic Editor are alternatives. Excel for the web does not run VBA macros.

The macro uses Value2 to read and write cell values, and checks HasFormula so formulas are not replaced by their current results. It handles a multi-cell selection, including separated areas. It changes the selected text values permanently, however, and does not preserve a formula that produces text. Save a copy of the workbook before running it: VBA changes cannot be reversed with Ctrl+Z on Windows or Command+Z on Mac.

Choose between VBA, UPPER, and formatting

The best method depends on whether you need a formula result, a one-time edit, or a repeatable automation:

  • Use UCase in VBA when a macro or procedure needs an uppercase string as part of its logic, such as preparing a label or normalizing a code for comparison.
  • Use the worksheet UPPER function when you want a result in another cell that recalculates as the source changes.
  • Use a macro that writes values when a repeatable workflow needs to replace selected text. Check first whether formulas and original capitalization need to be retained.
  • Use cell formatting only when the goal is visual emphasis. A font or number-format change does not convert the underlying text, and standard Excel formatting does not provide a general uppercase display setting for arbitrary text.

For a one-off conversion, a worksheet formula or an edit outside VBA is often simpler than building a macro. VBA is most useful when the conversion belongs inside a larger routine, must be applied to a changing set of cells, or needs custom rules such as skipping formulas.

Common problems and edge cases

The variable did not change: make sure the returned value is assigned. Use text = UCase$(text), not just UCase$(text) on its own. Likewise, if your procedure receives a value by value, assigning to its local parameter will not necessarily change the caller’s variable; use the returned result or design the procedure to return a String.

A cell shows uppercase but the original is still lowercase: a formula such as =UPPER(A2) displays a transformed result in its own cell. It does not overwrite A2. To replace the source, use a carefully scoped macro or copy the results and paste values, after preserving a copy if the original matters.

Unexpected errors from cell values: an Excel cell can contain an error value, and a Variant can contain Null. Do not send every possible Variant directly to UCase$. Check for errors with IsError, and account for Null if your data source can produce it. IsNull can test for Null; decide explicitly whether to keep it, replace it with an empty string, or handle it another way. A worksheet blank, an empty string, and Null are not interchangeable in every VBA operation.

Some non-English letters behave differently: case conversion for accented and other language-specific characters can depend on the character and the Windows or Office locale. VBA strings support Unicode text, but conversion rules are not a substitute for language-aware text processing in every locale. If a workbook must handle names or identifiers across languages, test representative characters in the target environment and avoid treating uppercase conversion as a universal way to identify equivalent text.

Numbers do not become text labels automatically: digits have no uppercase form. If a cell contains the number 123, converting its displayed value is not a meaningful case change. If you need a text representation of a number, define the intended format first; date and numeric display formats can affect how a value should be represented as text.

Macro safety and workbook setup

A macro that edits a range can affect more cells than intended if the wrong selection is active. Before running the example, confirm the selection, test on a duplicate workbook, and make sure the code skips anything you need to preserve. The sample deliberately leaves formulas and non-text values untouched, but it still changes text constants in place. It also processes every cell in the selection, so avoid selecting entire worksheets unless that is genuinely required.

Workbooks containing VBA should be saved in a macro-enabled format such as .xlsm; a standard .xlsx file cannot store VBA project code. Excel may display a security warning when opening a macro-enabled file. Run macros only from files and sources you trust, and do not lower macro security broadly just to run a single conversion. On Mac, the VBA editor and macro support are available in desktop Excel, but ribbon layouts and keyboard shortcuts can differ. Excel for the web can open a workbook containing macros, but it cannot execute the VBA code.

Frequently Asked Questions

What is the VBA function for uppercase?
Use UCase or its String-returning form UCase$. For example, UCase$("hello") returns HELLO.

Does UCase change the original variable?
No. It returns a converted value. Assign that value back, such as text = UCase$(text), if you want to replace the variable’s contents.

How do I make an entire Excel range uppercase?
Loop through the intended cells and assign UCase$ results to text values, or use =UPPER(A2) in a helper column. Decide whether formulas should be skipped or replaced before editing cells in place.

What is the difference between VBA UCase and Excel UPPER?
UCase is a VBA function used in code. UPPER is a worksheet function entered in a cell. Both convert lowercase letters to uppercase, but they are used in different parts of Excel and produce results in different ways.

Can UCase convert numbers to uppercase?
Digits have no uppercase form, so converting a numeric value does not change the digits. If you want a number represented as text, convert or format it deliberately before applying text operations.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.