To capitalize a string in Excel VBA, use the UCase function to convert all letters to uppercase (e.g., UCase("hello") returns “HELLO”), or use StrConv with the vbProperCase argument to capitalize the first letter of each word (e.g., StrConv("hello world", vbProperCase) returns “Hello World”). For capitalizing only the first letter of a string, combine UCase with Left and Mid.
Why Capitalization Matters in VBA
Capitalizing strings is a common data-cleaning task in Excel. You might need to standardize names, addresses, product codes, or user input before storing them in a worksheet or comparing them with other data. VBA provides several built-in functions to handle case conversion, each with different behavior. Understanding these functions helps you write cleaner, more reliable macros that produce consistent output.
The Core Functions: UCase, LCase, and StrConv
VBA offers three primary functions for case conversion:
UCase(string)– Converts all letters to uppercase. Example:UCase("Excel")returns “EXCEL”.LCase(string)– Converts all letters to lowercase. Example:LCase("Excel")returns “excel”.StrConv(string, vbProperCase)– Converts the first letter of each word to uppercase and all other letters to lowercase. Example:StrConv("hELLO wORLD", vbProperCase)returns “Hello World”.
These functions work on any string expression, including variables, cell values, or literal text. They are case-insensitive in the sense that they ignore the original casing and apply a uniform rule.
How to Capitalize the First Letter Only
Sometimes you only want the first character of a string capitalized, leaving the rest unchanged. There is no single built-in function for this, but you can combine existing ones:
Function CapitalizeFirst(s As String) As String
If Len(s) = 0 Then Exit Function
CapitalizeFirst = UCase(Left(s, 1)) & Mid(s, 2)
End Function
This function takes the first character with Left(s, 1), converts it to uppercase, and concatenates it with the remainder of the string (Mid(s, 2)). For example, CapitalizeFirst("vBA") returns “VBA”.
Using StrConv for Proper Case (Title Case)
The StrConv function with vbProperCase is the most common way to capitalize each word in a string, which is useful for names, cities, or titles. Note that it also lowercases the rest of each word. For example:
Dim city As String
city = StrConv("new york", vbProperCase)
' city now equals "New York"
Be aware that vbProperCase treats any non-letter character as a word separator. So “O’Brien” becomes “O’Brien” (the apostrophe splits the word, capitalizing the ‘B’). If you need to handle such cases specially, you may need a custom function.
Applying Capitalization to a Range of Cells
To capitalize all cells in a range, loop through the cells and assign the converted value. Here is an example that converts column A to proper case:
Sub CapitalizeColumnA()
Dim rng As Range
Dim cell As Range
Set rng = Range("A1:A10") ' adjust as needed
For Each cell In rng
If Not IsEmpty(cell.Value) Then
cell.Value = StrConv(cell.Value, vbProperCase)
End If
Next cell
End Sub
This macro checks for empty cells to avoid errors. Always save a copy of your workbook before running a macro, as changes cannot be undone with Ctrl+Z.
Common Problems and Misconceptions
One common mistake is assuming UCase affects only the first letter. It actually uppercases the entire string. Another misconception is that StrConv with vbProperCase will preserve existing capitalization; it does not—it forces a specific pattern. Also, note that StrConv is locale-aware; it may behave differently on systems with different language settings, particularly for characters like accented letters.
Another frequent issue is trying to use worksheet functions like PROPER inside VBA without the Application.WorksheetFunction prefix. You can use Application.WorksheetFunction.Proper as an alternative to StrConv, but it has similar limitations.
Comparing VBA Capitalization with Excel Worksheet Functions
Excel’s worksheet functions UPPER, LOWER, and PROPER do the same basic jobs as UCase, LCase, and StrConv(...vbProperCase). However, there are differences:
PROPERin Excel capitalizes the first letter of each word and lowercases the rest, just likeStrConv.- Worksheet functions can be used directly in cells without VBA, but VBA functions are faster when processing many cells in a loop.
- In VBA, you can call worksheet functions via
Application.WorksheetFunction.Proper, butStrConvis native and often more efficient.
For a one-off conversion, you might use a worksheet formula. For automating a task, VBA is more flexible.
Performance and Best Practices
When capitalizing large ranges, avoid writing to individual cells one by one if possible. Instead, read the range into an array, modify the array, and write it back in one operation. This can be significantly faster. Here is an example:
Sub CapitalizeRangeFast()
Dim dataArray As Variant
Dim i As Long, j As Long
dataArray = Range("A1:C100").Value
For i = 1 To UBound(dataArray, 1)
For j = 1 To UBound(dataArray, 2)
If Not IsEmpty(dataArray(i, j)) Then
dataArray(i, j) = StrConv(dataArray(i, j), vbProperCase)
End If
Next j
Next i
Range("A1:C100").Value = dataArray
End Sub
This reduces the number of interactions between VBA and the worksheet, improving performance.
Frequently Asked Questions
How do I capitalize the first letter of every word in a cell using VBA?
Use StrConv(cell.Value, vbProperCase). For example, StrConv("hello world", vbProperCase) returns “Hello World”. Assign the result back to the cell.
Can I use the Excel PROPER function inside VBA?
Yes, you can use Application.WorksheetFunction.Proper(string). It works similarly to StrConv with vbProperCase, but note that PROPER may treat certain characters differently; for most text, the results are identical.
Why does StrConv capitalize the letter after an apostrophe?
Because vbProperCase treats any non-letter character as a word separator. So in “O’Brien”, the apostrophe splits the word, and the ‘B’ is capitalized. If you need to avoid that, you would need a custom function that handles apostrophes specially.
Is there a way to capitalize only the first letter of a string and leave the rest unchanged?
Yes, use the custom function shown above: UCase(Left(s, 1)) & Mid(s, 2). This does not change the rest of the string.
Does UCase work with non-English characters?
Yes, UCase and LCase work with accented characters in most cases, but the behavior may depend on the system’s locale. For example, UCase("é") typically returns “É”. For full Unicode support, consider using the StrConv function with the appropriate locale ID.
Can I undo a macro that changes cell values?
No, macros cannot be undone with Ctrl+Z. Always save a backup copy of your workbook before running any macro that modifies data.
Related
- Excel Staff Schedule Template: Free Options & How to Build One
- Excel Spreadsheet Templates for Tracking Time
- Excel Spreadsheet Timesheet Template: How to Make One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find Last Row: Reliable Methods and ExamplesIn VBA, find the last row in a particular column with ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. To find the last row…
- VBA Dictionary Values: How to Change an Item SafelyYes. In VBA, change a Dictionary value by assigning a new value to its key, for example dict("North")…
- Excel VBA: Import a CSV Into a WorksheetTo import a CSV file into an Excel worksheet with VBA, use Application.GetOpenFilename to choose the file, open…
- Loop Through a Named Range in VBA: Practical ExamplesTo loop through every cell in an Excel named range in VBA, assign the range to a Range…
- Excel VBA Error 75: Fix Path/File AccessExcel VBA run-time error 75, “Path/File access error,” means a file operation could not access the path or…
- Excel VBA Copy and Paste Formulas Between Cells SafelyTo copy a formula to another cell with Excel VBA, use Copy followed by PasteSpecial Paste:=xlPasteFormulas. This pastes…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.