Excel VBA Tutorial, Tips and Examples

Excel Text Formula


Very often you work with data from different sources in Excel and the format of your spreadsheet needs additional work for presentations or data analysis. In most cases you will need to work with text - the content of your excel spreadsheet. For example, if you have downloaded data in excel you might end up with text which has a wrong text format and you need to convert the text in your excel spreadsheet from lowercase to proper case or from uppercase to lowercase in order to create a more user friendly spreadsheet. 

There are easy to use excel functions that you can use in your excel formulas by using functions such as LOWER, UPPER and PROPER to change the case of your text. For example if you want to change the case of your text in cell A1 you can use the formula =UPPER(A1) to change your case to all UPPERCASE such as FIRST NAME. Next you can use the =LOWER(A1) formula to change the case of your text to all lowercase such as first name or you can use the excel proper function in formula =PROPER(A1) to change the text to proper case such as First Name.

Another very useful and handy excel text formula is using "&" to join or add text together. This works like the + sign when you add numbers. For example if you have text in cell A1 and cell B1 you can use the following formula in cell C1: =A1&B1. For example, if you have first name text in A1 and last name in B1 you use the formula to have the first name and last name together in cell C1. In such a case you want to have space between the tect so use the following formula: =A1&" "&B1.

The following excel macro example uses the above mentioned text functions and formulas and loops through an excel database with the following structure: First Name data in Column A, Last Name data in Column B. The goal is to join the First Name and Last Name together and at the same time to create database with proper case and text format.

Sub Add_Text_and_Format_Text

       Range("C1").Select

       Do

              ActiveCell.FormulaR1C1 = "=PROPER(RC[-2])&"" ""&PROPER(RC[-1])"

              ActiveCell.Offset(1, 0).Select

       Loop Until ActiveCell.Offset(0, -2) = ""

End Sub

Excel VBA Sheets

 Sometimes your excel workbook contains many files and it would be nice to create a menu with all of your sheets and sheet names in your excel file. For example, you can create an excel sheet called menu where you can list all of your excel worksheets and access them from there. However doing that manually when you have many sheets in your excel workbook is time consuming.

You can use Excel VBA Macro to create a list of all worksheets in one place. The following macro loops through all excel sheets in your workbook and creates a list of your sheets in Column A in the active sheet:

Sub List_All_Sheet_Names_in_Column_A()

       For Each wsh In ThisWorkbook.Worksheets

              Range("A65536").End(xlUp).Offset(1, 0) = wsh.Name

       Next wsh

End Sub

As you can see this VBA code loops through the sheets and lists the name of each sheet in column A of your active sheet by using End(xlUp).Offset(1,0) to find the next blank cell in Column A.

So the previous macro example creates a list of all sheets in your excel workbook. However you can use the next Excel VBA Macro example to create the same list of all worksheets in your workbook and also add a hyperlink to each sheet name. By doing that you can simply click the hyperlink and go to the excel sheet with one click.

Sub List_All_Sheet_Names_in_Column_A_with_Hyperlinks()

       For Each wsh In ThisWorkbook.Worksheets

              Range("A65536").End(xlUp).Offset(1, 0).Select

              ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=wsh.Name & "!A1", TextToDisplay:=wsh.Name

       Next wsh

End Sub

This excel macro example loops through all worksheets in your workbook, list each sheet name to the list in Column A of your active sheet and adds a hyperlink to each sheet name.

Excel VBA Sheet

 Excel VBA allows you to work with excel worksheets and manipulate excel sheets with excel macro. The frequently used worksheet requirements by excel developers are to delete sheet, name sheet, rename sheet and create or add new worksheet.

The following excel macro uses (Sheets.Add) to create or add a new sheet in excel and then uses the (.Name) code to give a name to the new sheet:

Sub Add_and_Name_New_Sheet

       Sheets.Add.Name = "The Worksheet Name"

End Sub

You can also rename an existing excel worksheet by using the following excel macro:

Sub Rename_Existing_Sheet

       Sheets(“Old Name”).Name = "New Name"

End Sub

In some cases you can work with the same sheet updating its content such as creating reports, tables or charts. You can delete the current content and then update the new content but in cases when you have a lot of content it might be useful and more efficient to create a new sheet with the same name and delete the old worksheet. In many cases this code would work faster and more efficient.

Here is the excel VBA code example:

Sub Delete_and_Create_the_Same_Sheet

       Application.DisplayAlerts = False

       Sheets(“Sheet Name”).Delete

       Sheets.Add.Name = " Sheet Name"

       Application.DisplayAlerts = True

End Sub

This excel macro example uses the Application.DisplayAlerts to avoid the popup messages when your excel macro deletes the sheet.

Excel VBA Msgbox

Message Box is used to inform excel users about anything such as information about running your excel vba application or any other action such as reminders or gathering user input with your application.

Here is a simple message box macro:

Sub Create_Msgbox()

       MsgBox "Process Completed."

End Sub

This excel vba code can be used to inform the user that excel has finished with a certain action such as running your excel vba code. This message box shows the message "Process Completed" and the user clicks the Ok button to close the message box.

Message Box is a very handy tool for gathering user input and excellent tool for decision making by your excel application user. For example, your excel vba code can run to a certain point where the action will depend whether the user wants to continue with one action or another. In such a case the user makes the decision what to do next with your application.

Excel vba msgbox is perfect and the simplest tool to do this. Take a look at the following excel macro example:

Sub Create_Msgbox_For_User_Input()

       If MsgBox("Do you want to update the cell A1 value now?", vbOKCancel, "Update A1") = vbCancel Then Exit Sub

       Range("A1").Value = 7

End Sub

This simple excel macro can be run at a certain point or during certain event and the excel application user has to make the decision whether to update the value in cell A1 or not. This msgbox shows the Ok and Cancel buttons so if the user clicks the Ok button the value in cell A1 will be updated and if the user clicks the Cancel button the value in cell A1 will not be updated. You can simply replace the text in the macro based on your needs and you can replace the action part which is currently updating the value in cell A1 with any other excel vba action you need to perform if the user clicks the msgbox Ok button. 

Excel VBA Delete Blank Row

Sometimes you have data in Excel with blank cells. For example, let's say you have a simple database in excel worksheet with data from column A to column D. The first row of your database has titles for each column and your data starts from cell A2 and goes all the way to cell D100. However you need to prepare report showing only the information with all the required data from column A which means that your database for this report is not useful if there is no data in column A.

You can create a copy of your worksheet for the report and use the following macro to delete all rows without data in Column A:

Sub Delete_Rows_with_Blank_Cells()

       Range(Range("a65536").End(xlUp), Range("A2")).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub

How this excel vba macro works?

This excel macro is looking for every blank cell in Column A and deletes the entire row where the respective cell in column A is blank. The macro uses the End(xlUp) to find the last nonblank cell in Column A but you can replace this with another fixed range value based on your needs.

Another simple approach to deleting rows with blank cells in column A is without using excel VBA. Simply use the filter tool and sort the data from cell A2 and you can easily select and delete all rows with blank cells in column A.

Excel VBA Loop

Excel loops can help you perform repetitive tasks with a simple excel vba macro. This keeps your excel vba code simple and at the same time your vba code runs more efficiently. Excel loops are used when you need to run same functions or calculations in a range of cells. Instead of repeating the same operation as many times as you have cells in your data you can use one simple loop statement. Loops are also useful when your ranges are dynamic so the number of cells in your range changes. You can use loops to perform the required action to the number of cells you currently have in your excel worksheet.

There are many different ways to create excel vba loops. The following example demonstrates an approach to create your excel vba loops:

Sub MyLoop()

       Range("B1").Select

       Dim i As Integer

       For i = 1 To Application.CountA(Range("A:A"))

              ActiveCell.FormulaR1C1 = "=RC[-1]*10"

              ActiveCell.Offset(1, 0).Select

       Next i

End Sub

This excel vba code multiplies each cell value in column A with 10 and enters the results in column B next to each cell from column A. The data starts in cell A1 and the excel vba loop counts the number of cells in Column A and based on hte number of cells it performs the required action in Column B respectivelly. So regardless of the number of cells in your range (column A) the macro will always work based on your data.

This excel macro select the B1 cell and starts working in this cell by moving down one cell at a time and performs the calculation for as many times as there are non-blank cells in column A. This is defined by the value of i in the MyLoop macro.

Excel VBA Function

Using Excel VBA Functions allows you to use most of the excel worksheet functions in your excel vba macros. For example, you can use the excel functions such as sum, count, countif, countblank, frequency, min, max, weekday and many other excel worksheet functions with excel vba. There are two main approaches to use excel functions in your excel vba code. You can use excel vba functions to perform calculations by your code and use the results for other excel functions or show the results in your excel workbook. The second approach to use excel vba functions is to create excel formula in a cell in your excel worksheet.

Excel VBA Macro Example: Using the Excel Max Function

This simple excel macro shows you how you can use the excel Max function in your excel vba code. This excel macro looks for the maximum value in the range B2:B13 and shows the maximum value in a message box.

Sub Using_Excel_Max_Function()

       MaxResult = Application.WorksheetFunction.Max(Range("B2:B13"))

       MsgBox "The Maximum Value is " & MaxResult

End Sub

Excel VBA Chart

There are two alternatives for using Excel VBA Macro code to work with charts and create charts in excel. You can use excel macro to create chart in a new excel worksheet or you can create new embedded chart in a regular excel worksheet. Using chart sheet might be more convenient for presentations when you do not have many charts while embedded charts are useful when you want to create charts next to your excel data in your excel sheet or you want to use multiple charts for your excel data - this is useful when you are creating reports in excel.

Excel VBA Macro Example - Creating a Chart Sheet: This is a simple excel macro that uses excel vba to create chart as a chart sheet in excel. For this example the data is in Sheet1 - Columns A and B. We can create a simple chart as an excel chart sheet by using the following excel vba macro:

Sub Create_Chart_Sheet()

       Charts.Add

       With ActiveChart

              .ChartType = xlLineMarkers

              .SetSourceData Source:=Sheets("Sheet1").Range("A1:B13")

       End With

End Sub