How to Use a Worksheet Function in a Macro: Proper Case Names

In some cases, VBA does not have a specific function, but the Excel Worksheet does.

This Excel Visual Basic (VBA) code example demonstrates:

1. How to call that Worksheet Function from a Macro.
2. In this particular example, how to call the Worksheet Function =PROPER() from VBA.
3. The function to downcase parts of surnames that normally appear in lower case
4. How to get a macro to replace data in a cell in the worksheet.
5. How to select the cell one row down on the worksheet, like the “down arrow” on the keyboard.
6. A While…Wend loop to repeat the macro until the end of the data.

Question from Nico:

I want to write a VBA macro that will use the =PROPER() function on a selected worksheet cell for people’s names and surnames.

Answer

Sub NameCase()  ' Mixed Case for Names.                     ' New:  ' RIR 120917
  Dim Tex$
  While ActiveCell.Text > "" ' Repeat until blank cell
    Tex$ = ActiveCell.Text ' Get cell contents
    Tex$ = Application.WorksheetFunction.Proper(Tex$)   ' Proper Case
    Tex$ = Replace$(Tex$, " Van ", " van ")
    Tex$ = Replace$(Tex$, " Der ", " der ")
    Tex$ = Replace$(Tex$, " De ", " de ")
    Tex$ = Replace$(Tex$, " Le ", " le ")
    ActiveCell.Formula = Tex$ ' Replace cell contents
    ActiveCell.Offset(1, 0).Select    ' Move 1 row down
  Wend
End Sub

Excel has no “Proper” function, so we use code to call the worksheet’s function using Application.WorksheetFunction.Proper().

The “Replace$” lines take care of special cases like “van der Merwe”, “de Buisson”, and “le Roux”.

The line ActiveCell.Formula = Tex$ replaces the data in the current cell in the worksheet.

The line ActiveCell.Offset(1, 0).Select moves the cell selector (active cell) one row down in the worksheet.

This version runs down a vertical list until it hits a blank cell.  Take out the While…Wend lines if you want it to run once only.

Notes

Excel borrows from an earlier spreadsheet, Lotus 1-2-3, which itself owed much to VisiCalc.

Excel Visual Basic derives from earlier versions of Basic, like BASIC-A and QuickBasic.

Because of their differing origins and purposes, it is not surprising that the functions in VBA differ from those in the spreadsheet.  There are also functions with the same names, which behave differently or have different arguments.

Read more in our next post…