Use VBA’s UCase function to convert a string to uppercase; for a result explicitly typed as a String, use UCase$. For example, UCase$("Quarterly Report") returns QUARTERLY REPORT.
How UCase Works
UCase takes a string expression and returns its letters in uppercase. It does not change the original variable unless you assign the returned value back to that variable. This makes it useful both for creating a separate uppercase copy and for replacing a value in place.
In the VBA editor, a simple example is:
Sub ShowUppercaseText()
Dim sourceText As String
Dim upperText As String
' Convert the text and display the result in the Immediate window.
sourceText = "Quarterly Report"
upperText = UCase$(sourceText)
Debug.Print upperText
End Sub
Running this procedure prints QUARTERLY REPORT in the Immediate window. The original variable, sourceText, still contains Quarterly Report; the converted text is stored separately in upperText. To replace the original value, write sourceText = UCase$(sourceText) instead.
Uppercasing changes letter case, not the underlying meaning of text. It leaves spaces and ordinary punctuation in place, so UCase$(" North-2 ") returns NORTH-2 . It is not a trim, cleanup, or comparison function: leading and trailing spaces remain unless you remove them separately.
UCase and UCase$: What Is the Difference?
VBA provides both UCase and UCase$. Both perform uppercase conversion. The distinction is the declared return type: UCase returns a Variant containing a String, while UCase$ returns a String. In ordinary code where the input and destination are strings, either often produces the same visible text.
The dollar-suffixed form can make your intention clearer when the result is definitely text. It also avoids returning a Variant when a String is what the procedure expects. That does not make it a different kind of uppercase conversion, and choosing it does not fix invalid or unexpected input. If your source is a Variant that may contain a number, an error value, or Null, decide how to handle that value before converting it.
For example, a procedure whose argument is already declared as String can use UCase$ directly. If the input comes from a worksheet cell, its value is a Variant, so a direct conversion may require a check or an explicit conversion such as CStr. Explicit conversion is not a substitute for checking error values: passing a worksheet error to CStr can raise a runtime error.
Convert a String in a VBA Procedure
To make a reusable conversion routine, accept a string argument and return the converted string. A function is handy when several procedures need the same operation:
Function UppercaseText(ByVal value As String) As String
' Return an uppercase copy of the supplied text.
UppercaseText = UCase$(value)
End Function
Because the parameter is declared ByVal, the procedure works with a copy of the argument rather than changing the caller’s variable. The function’s result is the uppercase text. You can call it from another VBA procedure, for example by assigning UppercaseText("west region") to a String variable.
Use the function only when its input is text. If a variable can hold different data types, validate it first or define a deliberate conversion policy. Automatically converting numbers into strings may be useful in some applications, but it can also produce results that depend on formatting or regional settings. Uppercase conversion itself does not decide how numbers, dates, or errors should be represented.
Convert Text in Worksheet Cells with a Macro
A VBA macro can convert text in selected cells, but assigning the result back to a cell changes its contents. The following example converts only cells containing text and skips formulas, errors, blanks, and numeric values. Skipping formulas helps prevent the macro from replacing a formula with its current displayed result.
Sub UppercaseSelectedTextCells()
Dim cell As Range
' Convert text constants without replacing formulas or other values.
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
Select the cells you want to process before running the macro. The code reads each cell’s value and writes uppercase text back into that same cell. It does not change the cell’s formatting, but it does replace the original text, so the prior capitalization is not preserved in that cell. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z.
To add and run the procedure in Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, paste the code, and return to Excel. Select the target cells, then use Developer > Macros, choose UppercaseSelectedTextCells, and select Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. On a Mac, the Visual Basic Editor can be opened from Developer > Visual Basic; the keyboard shortcut may be Option+F11 or require the Fn key, depending on the keyboard and system settings.
The macro expects a worksheet range to be selected. It is intended for text constants, not formulas or every possible cell value. If you want to convert only one column, select that column’s used cells rather than the whole worksheet. For important data, preserve the original in a separate column or workbook before making changes.
VBA UCase Versus Excel’s UPPER Formula
Excel worksheet formulas and VBA functions solve related but different tasks. The VBA function UCase runs in VBA code. The worksheet function UPPER is entered in a cell and calculates as part of the worksheet. Use a formula when you want the converted result to update as the source changes; use a macro when you need VBA to perform an operation as part of a larger procedure or when you intend to replace values.
With source text in A2, enter this formula in B2:
=UPPER(A2)
For example, if A2 contains Quarterly Report, B2 displays QUARTERLY REPORT. The formula leaves A2 unchanged. Copy the formula down to handle additional rows. If you later need fixed uppercase values rather than formulas, copy the results and use Paste Special > Values; this replaces formulas in the destination with their calculated results.
Unlike a VBA macro, a normal worksheet formula does not require enabling macros. Excel for the web supports worksheet formulas such as UPPER, but it cannot run VBA macros. In desktop Excel, the formula is also a useful choice when readers need to see the transformation directly in the sheet.
What Happens to Spaces, Punctuation, and Other Characters?
Uppercase conversion targets letters. Digits, spaces, hyphens, and punctuation are not removed or rearranged. For example, text such as "order #a-104" becomes "ORDER #A-104". If the goal is to standardize names, codes, or imported data, consider whether trimming spaces or removing unwanted characters is a separate requirement; UCase does neither.
Case conversion can depend on language and locale for some characters, so do not assume every letter in every writing system maps in a simple one-to-one way. If your data includes accented letters or text from multiple languages, check the results in the environment where the workbook will be used. For identifiers restricted to English letters and digits, the results are usually more predictable, but that is a property of the data rule, not a guarantee that uppercase conversion validates the identifier.
Uppercase text may also be useful for case-insensitive comparisons, but converting both values is only one way to compare text. VBA’s Option Compare setting affects how string comparisons are performed in a module. If a comparison must follow a particular rule, choose and document that rule rather than assuming that changing letter case handles every linguistic or locale issue.
Common Problems and Safer Choices
- The value did not change: Calling
UCase$(text)calculates a result but does not updatetextby itself. Assign the result to a variable or cell. - A formula was replaced: Writing to a formula cell overwrites its formula. Use a worksheet formula in another column, or make the macro skip cells where
HasFormulais True. - A type mismatch occurred: Check whether a Variant contains an error or another non-text value before converting it. Use
CStronly when converting that value to text is appropriate. - Blank cells or spaces remain:
UCasedoes not trim whitespace. Use a separate trimming step only if removing those spaces is part of the intended data cleanup. - The macro cannot be undone: Excel’s Undo history does not reverse VBA edits. Save a backup or work on a duplicate sheet first.
- The macro does not run: Confirm that the workbook is macro-enabled when necessary and that macro security permits the procedure. Do not enable macros from an untrusted workbook just to convert text.
For a reversible workflow, put converted output in a new column or use UPPER rather than overwriting the source. For a one-time transformation, a macro can be convenient, but preserve a copy before it edits data.
Frequently Asked Questions
What is the VBA function to make text uppercase?
Use UCase or UCase$. For example, UCase$("hello") returns HELLO. The dollar-suffixed version returns a String.
Does UCase change the original variable?
No. It returns a converted value. Assign that result back, as in text = UCase$(text), if you want the variable itself to hold the uppercase text.
Can I use UCase directly in an Excel cell?
No. UCase is a VBA function, not a standard worksheet formula. In a worksheet cell, use Excel’s UPPER function, such as =UPPER(A2).
Can UCase convert an entire range?
Not by itself; it converts a string expression. Use a loop in a VBA macro to process cells, or use an UPPER formula in a neighboring column. Check cell types and preserve formulas if the macro writes results back.
Will UCase remove spaces or punctuation?
No. It changes letter case and leaves spaces and punctuation in place. Use separate functions or code for trimming or character cleanup, and only do so when that behavior is wanted.
Related
- Conditional Formatting: Use More Than One Rule in Excel
- table() in RStudio: How It Counts and Displays Data
- Data Validation and Reconciliation in Excel Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Automatically Add Comments to Excel Cells with VBAYes. In desktop Excel, VBA can automatically add a note to a cell with Range.AddComment; in current Microsoft…
- Excel VBA Range Using Cells: Syntax, Examples, and TipsIn Excel VBA, use Cells(row, column) to identify a cell by its row and column numbers, and pass…
- Assign a Macro to a UserForm Command Button in ExcelIn Excel for Microsoft 365 on Windows, you do not assign a UserForm command button through the worksheet’s…
- VBA InputBox With a Drop-Down List: Your OptionsNo: VBA’s built-in InputBox does not include a drop-down list, and Application.InputBox does not add one. To let…
- Macro Recorder for Windows: Options and How to ChooseA macro recorder for Windows captures repeated actions and replays them, but Windows does not provide one universal…
- VBA UserForm Design: Build a Practical Excel FormTo design a VBA UserForm in Excel, open the Visual Basic Editor, choose Insert > UserForm, add controls…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.