UPPER CASE

Users are often faced with numerous text processing function which are for the average user a total nightmare. Since we are aware of that we decide that we are going to show you in a series of postsĀ a wide spread of subroutines that you can use in Excel to make your life easier. At a later stage we will pack all of these sub routines that we are showcasing into a workbook that you can download and use at your convinience.

We will start here with the routine #1 to change the content of the selected cells to UPPER CASE and also add a section where we will show how to add the codes to VBA in order to work with them.

Sub Upper_case() ' change to upper case
Dim selected_range As Range
Set selected_range = Range(Selection.Address)

Dim old_value As String
For Each cell In selected_range
    old_value = vbNullString: old_value = cell.Value
    cell.Value = UCase(old_value)
Next
End Sub

Open up the VBA editor by pressing alt+F11.

Vba editor

The routine needs to be copied and pasted into a module in Visual Basic Editor which you will add by right clicking on Microsoft Excel Objects/Insert/Module, see image:

Add module to VBA

Paste the code in the right hand side of the screen on the white background area and save CTRL+S or click on the floppy disk from the menu, make sure to save the workbook as .xlsm (macro enabled workbook).

Paste the code snippet in VBA

Now to test the routine, add some text on the workbook that you want to convert to UPPER CASE, select the range of cells and hit alt+F8 which will bring up the VBA sub routines that are in the workbook and click on Upper_case and click RUN.

Run the sub routine

The cells contents will be changed to UPPER CASE.