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.
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:
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).
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.
The cells contents will be changed to UPPER CASE.