Capitalize first letter of each word using VBA

Question

How to format words in Excel sheets using VBA such that first letter is capitalized followed by small letters in each word?

Answer 1

PROPER function should help. See this for details.

Using VBA

Sub Demo()
    Dim ws As Worksheet
    Dim rng As Range, cel As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet3")  'change Sheet3 to data sheet
    With ws
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For Each cel In .Range("A1:A" & lastRow)
            cel.Offset(0, 1).Value = Application.Proper(cel.Value)
        Next
    End With
End Sub

Using Formula

=PROPER(A1)

Answer 2

This answer is the top Stack Overflow hit on Google for this question:

"vba code to capitalise the first letter of a word"

This code works in Office 365 Excel on Windows 10:

Public Function fnProperCase(sWord As String) As String
    fnProperCase = StrConv(sWord, vbProperCase)
End Function

Here is the Excel with a test suit; row 5 has all three outputs; test data in col 1, =fnProperCase() in col 2 and =PROPER() in col 3

Below are various other answers on Stack Overflow and elsewhere:

StrConv

Capitalise first letter of words without changing currently Capitalised

Making wrong code look wrong