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
UCasein 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
UPPERfunction 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
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Error 52: Fix Bad File Name or Number in VBAExcel Run-time error 52, “Bad file name or number,” usually means VBA tried to access a file using…
- VBA Code to Sort Data by Multiple ColumnsTo sort Excel rows by multiple columns with VBA, use the worksheet’s Sort object, add one sort field…
- VBA MsgBox New Line: Add Line Breaks to MessagesTo add a new line in a VBA MsgBox, join the message text with vbCrLf or vbNewLine where…
- VBA Range Index: How Cells, Item, and Offsets WorkIn VBA, a range index identifies a cell by its row and column position, starting at 1. For…
- VBA Send Email From a Different Address in OutlookYes. In classic Outlook for Windows, VBA can send a message from another address when that address is…
- VBA Error Handling: On Error, Cleanup, and Debugging TipsVBA error handling lets a macro respond when a statement fails instead of stopping unexpectedly. Use On Error…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.