# Excel Visual Basic for Applications Tutorial

## Introduction

Excel Visual Basic for Applications is a very useful tool if one deals with Excel on a daily basis. One can automate Excel commands using Visual Basic for Applications (VBA) such that tedious tasks can be done with a click of a button. VBA can also be very useful if one deals with data and needs to present it in a clear manner.

The first step in creating Excel VBA Macros is to add the Developer tab on the Ribbon. Select File in the upper-left corner of the Excel Menu and then select Options. Select Customize Ribbon and enable the Developer option under Main Tabs.

File

Options

Customize Ribbon

You should now see the Developer tab on your Ribbon. Select the Visual Basic option to open up the editor.

Developer Tab

A new window will pop up where you can select Module from the dropdown menu next to the Save icon.

Create Module

You can now start creating subroutines in VBA by typing sub SUBNAME in the editor window. Hitting enter right after your subroutine's name should automatically fill in the parentheses and the End Sub text. All your code will be within Sub SUBNAME() and End Sub. The subroutine can be executed by pressing F5 or by selecting the small green arrow.

Create Subroutine

One last step must be done before we continue. Make sure to save the Excel file as an Excel Macro-Enabled Workbook (.xlsm) in order to be able to run the Excel VBA Macros on that workbook.

Excel Macro-Enabled Workbook (.xlsm)

## Cells

The cells in Excel are what contain the information and data of the workbook. Being able to modify these cells is very important if one wants to automate tasks efficiently. This section will be dedicated to cells and how one can modify them.

### Clear Cells

Cells can be assigned values several different ways but it is also important to know how to clear the contents of a cell if we want to clean up the worksheet in order to test out new code. The command .ClearContents clears the contents of the specified cells. The first command below clears the contents of Cell(1,1), the second command clears the contents of the Range(Cells(1, 1), Cells(4, 4)), and the last command clears the contents of the whole worksheet.

Input:

Cells(1, 1).ClearContents 

Clear Contents

Output:

Cell(1,1) Clear Contents

Input:

Range(Cells(1, 1), Cells(4, 4)).ClearContents 

Output:

Range(Cells(1, 1), Cells(4, 4)) Clear Contents

Input:

Cells.ClearContents 

Output:

Clear All Contents

### Assigning Values to Cells

One can assign values to cells by using the Cells(ROW,COLUMN) command. Here we assign values to cells directly or through variables. A cell can be assigned a string using quotation ("") marks.

Input:

Cells(1, 3) = 1 Cells(3, 1) = "Test" Var1 = 5.5 Cells(6, 5) = Var1 Var2 = "Test2" Cells(5, 6) = Var2 

Output:

Cells Output

You can also use the range command to assign values to one cell or multiple cells. The range starts at the top left corner and covers everything down to the bottom right corner of the given values.

Input:

Range("A1") = 1 

Output:

Range A1

Input:

Range("A1:E5") = 1 

Output:

Range A1:E5

The cells command can also be used within the range command.

Input:

Range(Cells(1, 1), Cells(5, 5)) = 1 

Output:

Range Cells A1:E5

Variables can be used to enter the desired cell locations which is very convenient when dealing with dynamic data ranges. The LastRow and LastColumn variables have both been assigned a value of 3 in this example but the Last Row and Last Column section introduces a very useful method on finding the exact last row and last column of a data range dynamically.

Input:

LastRow = 3 LastColumn = 3 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 

Output:

Range LastRow LastColumn

A combination of cells and ranges can be used to assign values to cells as well. The range command below is mimicking Range("A1:D4") = 1 by using the & symbol to concatenate strings and numbers. Strings can be concatenated using the & symbol as well. Notice the space in the quotation marks for Cells(6, 6) and Cells(7, 7) in order to create a space in the actual cell entry.

Input:

FirstRow = 1 LastRow = 4 Range("A" & FirstRow & ":D" & LastRow) = 1 Cells(5, 5) = "Test" & 5 Cells(6, 6) = "Another " & "Test" Cells(7, 7) = "Another" & " Test" 

Output:

Range Combination

### Customize Cells

One of Excel's greatest features is the possibility to customize your data and display it in an eye-catching manner. Excel VBA can also be used to customize cells in many different ways. Before we continue, the code below erases the contents (.ClearContents), erases the formatting (.ClearFormats), and reverts the row height (RowHeight = 15) and column width (.ColumnWidth = 8.43) back to default for all cells. This will be useful when we want to clear a sheet in order to run new code.

Input:

Cells.ClearContents Cells.ClearFormats Cells.RowHeight = 15 Cells.ColumnWidth = 8.43 

Clear Contents

Output:

Default

The .Interior.Color command changes the color of the specified cells. The code below uses the RGB format to select the desired color.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Interior.Color = RGB(125, 0, 0) 

Output:

Interior Color

You can acquire the numbers for the RGB colors by selecting the Home tab and clicking on More Colors under the paint bucket icon. You can now select one of the default colors in the Standard tab then click on the Custom tab in order to get the RGB color numbers. You can also customize your own color with the Custom tab.

More Colors

Standard Colors

Custom Colors

Customize Custom Colors

The .Font.Color command changes the color of the font for the specified cells.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Color = RGB(250, 0, 0) 

Output:

Font Color

The .Font.Size command changes the font size for the specified cells.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Size = 20 

Output:

Font Size

Changing the font size of a cell can lead to the contents of the cell to be obscured by the cell's default row height and column width. The .AutoFit command can be used for both the Rows and Columns of a range in order to automatically adjust the height and width, respectively, of a cell to the size of the cell's contents.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Size = 20 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Rows.AutoFit 

Output:

Rows Auto Fit

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Size = 20 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Columns.AutoFit 

Output:

Columns Auto Fit

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Size = 20 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Rows.AutoFit Range(Cells(1, 1), Cells(LastRow, LastColumn)).Columns.AutoFit 

Output:

Rows and Columns Auto Fit

The row height and column width can be manually specified by using the .RowHeight and .ColumnWidth commands, respectively. Note that all the rows and columns of that range adjust accordingly.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).RowHeight = 50 Range(Cells(1, 1), Cells(LastRow, LastColumn)).ColumnWidth = 25 

Output:

Rows and Columns Size

The font can be bolded and italicized using the .Font.Bold = True and .Font.Italic = True commands, respectively.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Bold = True Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Italic = True 

Output:

Bold and Italic Font

The contents of a cell can be aligned both horizontally and vertically using the .HorizontalAlignment and .VerticalAlignment commands, respectively. Here we are centering the cell both horizontally and vertically using xlCenter. I included the resizing of the rows and columns to better illustrate the alignment of a cell's content.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).RowHeight = 50 Range(Cells(1, 1), Cells(LastRow, LastColumn)).ColumnWidth = 25 Range(Cells(1, 1), Cells(LastRow, LastColumn)).HorizontalAlignment = xlCenter Range(Cells(1, 1), Cells(LastRow, LastColumn)).VerticalAlignment = xlCenter 

Output:

Alignment

We can create borders for cells in a range by using the .Borders.LineStyle command. The border weight can also be adjusted with the .Borders.Weight command. There are various border styles and weights as seen below.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.LineStyle = xlContinuous 

Output:

Regular Border

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.LineStyle = xlDash 

Output:

Dash Border

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.LineStyle = xlDouble 

Output:

Double Border

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.LineStyle = xlContinuous Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.Weight = xlThick 

Output:

Regular Border Thick

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.LineStyle = xlContinuous Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.Weight = xlThin 

Output:

Regular Border Thin

The border color can be changed using the .Borders.Color command.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.LineStyle = xlContinuous Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.Weight = xlThick Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.Color = RGB(0, 250, 0) 

Output:

Border Color

You can also customize specific edges of cells by stating which edge you want to modify within the .Border(xlEdgeSIDE) command. Note that the specified edge side applies to the outer edges of the entire range and not the individual cells. I moved the cells to better illustrate the borders.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(2, 2), Cells(LastRow, LastColumn)) = 1 Range(Cells(2, 2), Cells(LastRow, LastColumn)).Borders(xlEdgeTop).LineStyle = xlContinuous Range(Cells(2, 2), Cells(LastRow, LastColumn)).Borders(xlEdgeRight).LineStyle = xlDash Range(Cells(2, 2), Cells(LastRow, LastColumn)).Borders(xlEdgeBottom).LineStyle = xlDouble Range(Cells(2, 2), Cells(LastRow, LastColumn)).Borders(xlEdgeLeft).LineStyle = xlDashDot Range(Cells(2, 2), Cells(LastRow, LastColumn)).Borders(xlEdgeTop).Color = RGB(250, 0, 0) Range(Cells(2, 2), Cells(LastRow, LastColumn)).Borders(xlEdgeRight).Color = RGB(0, 250, 0) Range(Cells(2, 2), Cells(LastRow, LastColumn)).Borders(xlEdgeBottom).Color = RGB(0, 0, 250) Range(Cells(2, 2), Cells(LastRow, LastColumn)).Borders(xlEdgeLeft).Color = RGB(125, 125, 125) 

Output:

Border Color

The style of the cells can be changed using the .Style command. Below are a couple of different style formats.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(1, LastColumn)).Style = "Currency" Range(Cells(2, 1), Cells(2, LastColumn)).Style = "Percent" Range(Cells(3, 1), Cells(3, LastColumn)).Style = "Comma" Range(Cells(4, 1), Cells(4, LastColumn)).Style = "Good" Range(Cells(5, 1), Cells(5, LastColumn)).Style = "Bad" 

Output:

Style Formats

All these different modifications can be combined to create your personal format as seen in the first code below. However, the first code can be written in a more compact manner by using the With command which the second code uses. Anything in the With block will be applied to what is stated right after the With command. In the second code, all the formatting within With is applied to Range(Cells(1, 1), Cells(LastRow, LastColumn)). Both codes result in the same outcome but one is easier to read and more compact than the other.

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Interior.Color = RGB(125, 0, 0) Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Color = RGB(250, 0, 0) Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Size = 20 Range(Cells(1, 1), Cells(LastRow, LastColumn)).RowHeight = 50 Range(Cells(1, 1), Cells(LastRow, LastColumn)).ColumnWidth = 25 Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Bold = True Range(Cells(1, 1), Cells(LastRow, LastColumn)).Font.Italic = True Range(Cells(1, 1), Cells(LastRow, LastColumn)).HorizontalAlignment = xlCenter Range(Cells(1, 1), Cells(LastRow, LastColumn)).VerticalAlignment = xlCenter Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.LineStyle = xlContinuous Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.Weight = xlThick Range(Cells(1, 1), Cells(LastRow, LastColumn)).Borders.Color = RGB(0, 250, 0) 

Output:

Combined Formats

Input:

LastRow = 5 LastColumn = 5 Range(Cells(1, 1), Cells(LastRow, LastColumn)) = 1 With Range(Cells(1, 1), Cells(LastRow, LastColumn)) .Interior.Color = RGB(125, 0, 0) .Font.Color = RGB(250, 0, 0) .Font.Size = 20 .RowHeight = 50 .ColumnWidth = 25 .Font.Bold = True .Font.Italic = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Borders.LineStyle = xlContinuous .Borders.Weight = xlThick .Borders.Color = RGB(0, 250, 0) End With 

Output:

Combined Formats With

## Sheets

Sheets are very useful when one has to separate large sets of data according to their subjects. This section will be dedicated to Excel VBA commands that are used to modify sheets.

### Create Sheet

Creating a new sheet can be done with the sheets.Add command. The .Add(After:=sheets(sheets.Count)) text adds a sheet after the last sheet in the workbook by using the sheets.Count text instead of a specific sheet name. The .Name command gives the new sheet a name, e.g. TESTSHEET. The second code adds a sheet in between two new sheets that I have created (TESTSHEET and TESTSHEET2) by specifying the sheet name TESTSHEET in .Add(After:=sheets("TESTSHEET")).

Input:

sheets.Add(After:=sheets(sheets.Count)).Name = "TESTSHEET" 

New Sheet

Output:

Input:

sheets.Add(After:=sheets("TESTSHEET")).Name = "BETWEENTESTSHEET" 

Between Sheet

Output:

### Delete Sheet

A sheet can be deleted by using the command sheets("SHEETNAME").Delete. A warning will pop up asking if the user is sure they want to delete the sheet, select Delete to delete the sheet.

Input:

sheets("BETWEENTESTSHEET").Delete 

Between Sheet

Output:

Delete Sheet Warning

Between Sheet Deleted

### Copy Sheet

A sheet can be copied by using the command sheets("SHEETNAME").Copy. The code below copies the sheet to the location after the last sheet using the After:=sheets(sheets.Count) command. Note the (2) after in the sheet name signifying it is a copy.

Input:

sheets("BETWEENTESTSHEET").Copy After:=sheets(sheets.Count) 

Copy Sheet

Output:

Copy Sheet Copied

### Rename Sheet

A sheet can be renamed by using the command sheets("CURRENTNAME").Name = "NEWNAME".

Input:

sheets("BETWEENTESTSHEET (2)").Name = "RENAMEDSHEET" 

Copy Sheet Renaming

Output:

Sheet Renamed

### Sheet Cells

Now that there are multiple sheets in this Excel Workbook, one must specify a sheet in order to modify that specific sheet's cells. Modifying cells without a sheet callout only modifies cells for the current active sheet; this is the sheet that is currently selected.

Input:

Cells(1, 1) = 1 

Output:

Selected Sheet

Not Selected Sheet

One would have to state what sheet is to be modified with the sheets("SHEETNAME").Cells(ROW, COLUMN) command. This method allows for the modification of cells in sheets even if the specified sheets are not selected.

Input:

sheets("Sheet1").Cells(1, 1) = 1 sheets("TESTSHEET").Cells(1, 1) = 1 

Output:

Selected Sheet

Not Selected Sheet Modified

The sheet name must also be declared when clearing the cells.

Input:

sheets("Sheet1").Cells.ClearContents sheets("Sheet1").Cells.ClearFormats sheets("Sheet1").Cells.RowHeight = 15 sheets("Sheet1").Cells.ColumnWidth = 8.43 sheets("TESTSHEET").Cells.ClearContents sheets("TESTSHEET").Cells.ClearFormats sheets("TESTSHEET").Cells.RowHeight = 15 sheets("TESTSHEET").Cells.ColumnWidth = 8.43 

However, the Range command is a bit different as the sheet must be specified for the cells and not the range.

Input:

Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 5)) = 1 Range(sheets("TESTSHEET").Cells(1, 1), sheets("TESTSHEET").Cells(5, 5)) = 1 

Output:

Selected Sheet

Not Selected Sheet Modified

An error will occur if the sheets("SHEETNAME").Range command is used and the sheet SHEETNAME is not the one currently active.

Input:

sheets("Sheet1").Range(Cells(1, 1), Cells(5, 5)) = 1 sheets("TESTSHEET").Range(Cells(1, 1), Cells(5, 5)) = 1 

Output:

Range Error

Empty Sheet

You can now modify and customize cells using the commands in the Cells section.

Input:

Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 5)) = 1 Range(sheets("TESTSHEET").Cells(1, 1), sheets("TESTSHEET").Cells(5, 5)) = 1 Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 5)).Interior.Color = RGB(125, 0, 0) Range(sheets("TESTSHEET").Cells(1, 1), sheets("TESTSHEET").Cells(5, 5)).Interior.Color = RGB(125, 0, 0) 

Output:

Sheet1 Color

TESTSHEET Color

### Copy Cells from Sheets

Copying cells from one sheet is done by using the .Copy command and pasting them into another sheet is done by using the .PasteSpecial command. Note that only the top left cell (in this case Cells(1,1)) needs to be stated for the pasting location.

Input:

Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 5)) = 5 Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 5)).Interior.Color = RGB(125, 0, 0) Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 5)).Copy sheets("TESTSHEET").Cells(1, 1).PasteSpecial 

Output:

Copy Range

Paste Range

## Charts

Excel VBA can be used to create charts in order to make the process of presenting data a lot faster. One can automatically select the data, name the chart, select the chart type, etc. using VBA instead of manually going through the Insert tab.

### Creating Charts

The first step in creating a chart is to add a chart using the .Shapes.AddChart command. The .Select command will select the chart in order to use the With command in a bit.

Input:

sheets("Sheet1").Shapes.AddChart.Select 

Output:

Blank Chart

All charts can be deleted with the command below. This command will give an error if there are no charts on the specified sheet.

Input:

sheets("Sheet1").ChartObjects.Delete 

Now we will select the data and give this chart a title. It is important to note that if no data is selected, the chart will remain blank without a title. The default chart type in excel is a column chart if one does not specify a chart type. The data series will be named according to the header column if the header column is included in the range.

Input:

sheets("Sheet1").Shapes.AddChart.Select With ActiveChart .SetSourceData Source:=Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 3)) .HasTitle = True .ChartTitle.Text = "Test Chart" End With 

Output:

New Chart

The chart type can be selected using the .ChartType command. For a list of all the other chart types, please see this Microsoft Chart Type Page.

Input:

sheets("Sheet1").Shapes.AddChart.Select With ActiveChart .SetSourceData Source:=Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 3)) .HasTitle = True .ChartTitle.Text = "Test Chart" .ChartType = xl3DColumnClustered End With 

Output:

New Chart

The axes can be given names with the .Axes command. The xlCategory is the X-Axis while the xlValue is the Y-Axis.

Input:

sheets("Sheet1").Shapes.AddChart.Select With ActiveChart .SetSourceData Source:=Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 3)) .HasTitle = True .ChartTitle.Text = "Test Chart" .ChartType = xl3DColumnClustered .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Name" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Text = "Data" End With 

Output:

Axes Titles

The series can be modified with the .SeriesCollection() commands below. The number in the .SeriesCollection() corresponds to the order of the plotted data. I have renamed the data series with .Name and changed the color with .Interior.Color.

Input:

sheets("Sheet1").Shapes.AddChart.Select With ActiveChart .SetSourceData Source:=Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 3)) .HasTitle = True .ChartTitle.Text = "Test Chart" .ChartType = xl3DColumnClustered .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Name" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Text = "Data" .SeriesCollection(1).Name = "Data One" .SeriesCollection(1).Interior.Color = RGB(250, 0, 0) .SeriesCollection(2).Name = "Data Two" .SeriesCollection(2).Interior.Color = RGB(0, 250, 0) End With 

Output:

Modifying Series

The chart location and size can be modified with the .Parent command. The .Top range only depends on the row and states the top position location of the chart. The .Left range only depends on the column and states the left position location of the chart. I have selected the top position of the chart to be located on row 10 and the left position of the chart to be located on column E. The .Height and .Width adjust the height and width of the chart, respectively. The height of the chart spans from row 10 to row 30 (E10 to E30) while the width of the chart spans from column E to column N (E10 to N10).

Input:

sheets("Sheet1").Shapes.AddChart.Select With ActiveChart .SetSourceData Source:=Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 3)) .HasTitle = True .ChartTitle.Text = "Test Chart" .ChartType = xl3DColumnClustered .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Name" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Text = "Data" .SeriesCollection(1).Name = "Data One" .SeriesCollection(1).Interior.Color = RGB(250, 0, 0) .SeriesCollection(2).Name = "Data Two" .SeriesCollection(2).Interior.Color = RGB(0, 250, 0) .Parent.Top = Range("A10").Top .Parent.Left = Range("E1").Left .Parent.Height = Range("E10:E30").Height .Parent.Width = Range("E10:N10").Width End With 

Output:

Chart Location and Size

Additional data can be added to a chart even if the data is not next to the other data. The Union command combines different ranges in order for all the data to be included in the chart. In the image below, I have created another range which covers the third data set (Range(sheets("Sheet1").Cells(1, 5), sheets("Sheet1").Cells(5, 5))) and appended it to the other ranges with the Union command by separating it from the first range with a comma.

Input:

sheets("Sheet1").Shapes.AddChart.Select With ActiveChart .SetSourceData Source:=Union(Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 3)), Range(sheets("Sheet1").Cells(1, 5), sheets("Sheet1").Cells(5, 5))) .HasTitle = True .ChartTitle.Text = "Test Chart" .ChartType = xl3DColumnClustered .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Name" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Text = "Data" .SeriesCollection(1).Name = "Data One" .SeriesCollection(1).Interior.Color = RGB(250, 0, 0) .SeriesCollection(2).Name = "Data Two" .SeriesCollection(2).Interior.Color = RGB(0, 250, 0) .SeriesCollection(3).Name = "Data Three" .SeriesCollection(3).Interior.Color = RGB(0, 0, 250) .Parent.Top = Range("A10").Top .Parent.Left = Range("E1").Left .Parent.Height = Range("E10:E30").Height .Parent.Width = Range("E10:N10").Width End With 

Output:

### Scatter Plot with Lines

Creating a scatter plot with lines (.ChartType = xlXYScatterLines) is a bit different than creating a column graph. Each series is individually created by using .SeriesCollection.NewSeries. The X Values are assigned using .SeriesCollection(#).XValues and the Y Values are assigned using .SeriesCollection(#).Values. The line color, line weight, and dash style are modified using .Format.Line.ForeColor.RGB, .Format.Line.Weight, and .Format.Line.DashStyle (Dash Styles), respectively. The marker color, size, and style are modified using .MarkerBackgroundColor, .MarkerSize, and .MarkerStyle (Marker Styles), respectively. For a much more efficient way of creating each of these new series, please see the Loop Chart section.

Input:

sheets("Sheet1").Shapes.AddChart.Select With ActiveChart .HasTitle = True .ChartTitle.Text = "Test Chart" .ChartType = xlXYScatterLines .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Values" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Text = "Data" .SeriesCollection.NewSeries .SeriesCollection(1).Name = "Data One" .SeriesCollection(1).XValues = Range(sheets("Sheet1").Cells(2, 1), sheets("Sheet1").Cells(5, 1)) .SeriesCollection(1).Values = Range(sheets("Sheet1").Cells(2, 2), sheets("Sheet1").Cells(5, 2)) .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(250, 0, 0) .SeriesCollection(1).Format.Line.Weight = 5 .SeriesCollection(1).MarkerBackgroundColor = RGB(125, 0, 0) .SeriesCollection(1).MarkerSize = 50 .SeriesCollection(1).MarkerStyle = xlMarkerStyleCircle .SeriesCollection.NewSeries .SeriesCollection(2).Name = "Data Two" .SeriesCollection(2).XValues = Range(sheets("Sheet1").Cells(2, 1), sheets("Sheet1").Cells(5, 1)) .SeriesCollection(2).Values = Range(sheets("Sheet1").Cells(2, 3), sheets("Sheet1").Cells(5, 3)) .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(0, 250, 0) .SeriesCollection(2).Format.Line.Weight = 2 .SeriesCollection(2).Format.Line.DashStyle = msoLineDash .SeriesCollection(2).MarkerBackgroundColor = RGB(0, 125, 0) .SeriesCollection(2).MarkerSize = 70 .SeriesCollection(2).MarkerStyle = xlMarkerStyleDiamond .SeriesCollection.NewSeries .SeriesCollection(3).Name = "Data Three" .SeriesCollection(3).XValues = Range(sheets("Sheet1").Cells(2, 1), sheets("Sheet1").Cells(5, 1)) .SeriesCollection(3).Values = Range(sheets("Sheet1").Cells(2, 5), sheets("Sheet1").Cells(5, 5)) .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(0, 0, 250) .SeriesCollection(3).Format.Line.Weight = 2 .SeriesCollection(3).Format.Line.DashStyle = msoLineDashDot .SeriesCollection(3).MarkerBackgroundColor = RGB(0, 0, 125) .SeriesCollection(3).MarkerSize = 25 .SeriesCollection(3).MarkerStyle = xlMarkerStyleSquare .Parent.Top = Range("A10").Top .Parent.Left = Range("E1").Left .Parent.Height = Range("E10:E30").Height .Parent.Width = Range("E10:N10").Width End With 

Output:

Scatter Plot with Lines

### Pie Chart

A pie chart can be created using .ChartType = xlPie. The range of the data includes the headers in order for the series to be labeled accordingly. Data labels are created using the .ApplyDataLabels command. The position, shape, and visibility of the data labels are modified with .SeriesCollection(#).DataLabels.Position (Data Label Positions), .SeriesCollection(#).DataLabels.Format.AutoShapeType (Data Label Shapes), and .SeriesCollection(#).DataLabels.Format.Line.Visible, respectively. The color of each data point is changed using .SeriesCollection(#).Points(#).Format.Fill.ForeColor.RGB. Note that the series collection number is (1) for all of the points while the points' number changes.

Input:

sheets("Sheet1").Shapes.AddChart.Select With ActiveChart .SetSourceData Source:=Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(5, 2)) .HasTitle = True .ChartTitle.Text = "Test Chart" .ChartType = xlPie .ApplyDataLabels (xlDataLabelsShowLabelAndPercent) .SeriesCollection(1).DataLabels.Position = xlLabelPositionOutsideEnd .SeriesCollection(1).DataLabels.Format.AutoShapeType = msoShapeRectangularCallout .SeriesCollection(1).DataLabels.Format.Line.Visible = msoTrue .SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = RGB(250, 0, 0) .SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = RGB(0, 250, 0) .SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = RGB(0, 0, 250) .SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = RGB(125, 125, 125) .Parent.Top = Range("A10").Top .Parent.Left = Range("E1").Left .Parent.Height = Range("E10:E30").Height .Parent.Width = Range("E10:N10").Width End With 

Output:

Pie Chart

## Useful Commands

This section will cover some of the commands that I have found useful over the years.

### For Loop

A for loop is very useful if one has to iterate through a lot of cells. The loop below uses the counter i to count from 1 to 10. The code assigns "Row" to rows 2 through 11 in column 1 by having the counter i+1 in the row position while keeping column 1 static. The code also assigns "Column" to columns 2 through 11 in row 1 by having the counter i+1 in the column position while keeping row 1 static.

Input:

For i = 1 To 10 sheets("Sheet1").Cells(i + 1, 1) = "Row" sheets("Sheet1").Cells(1, i + 1) = "Column" Next i 

Output:

For Loop

A for loop can be within another for loop as seen below. These loops have two counters (i and j) which allows them to iterate through both rows and columns. The outer loop counter is static until the inner loop finishes. After the inner loop finishes, the outer loop counter increases and the inner loop counts again. This loop starts out at i = 1 and loops through j = 1 to 10. Afterwards, the i counter increases to i = 2 and loops through j = 1 to 10 again and so on. In the example below, row 1 columns 1 through 10 are assigned a value of 1. Then, row 2 columns 1 through 10 are assigned a value of 1 and so on.

Input:

For i = 1 To 10 For j = 1 To 10 sheets("Sheet1").Cells(i, j) = 1 Next j Next i 

Output:

Embedded For Loop

### If Statement

The if statement is very useful if you want to check for certain criteria in cells. Below is an if statement inside a for loop that checks rows 1 through 10 in column 1 for certain criteria. The first if statement changes the cell color of the cell to red if the value is less than 10. The elseif statement changes the cell color of the cell to green if the value is greater than or equal to 10. The first if statement that a cell meets will be applied to that cell. As seen below, a third statement repeats the first statement but with a different color change. However, the modification does not get applied as the cells have already met a criteria beforehand.

Input:

For i = 1 To 10 If sheets("Sheet1").Cells(i, 1) < 10 Then sheets("Sheet1").Cells(i, 1).Interior.Color = RGB(250, 0, 0) ElseIf sheets("Sheet1").Cells(i, 1) >= 10 Then sheets("Sheet1").Cells(i, 1).Interior.Color = RGB(0, 250, 0) ElseIf sheets("Sheet1").Cells(i, 1) < 10 Then sheets("Sheet1").Cells(i, 1).Interior.Color = RGB(0, 0, 250) End If Next i 

Simple If Statement Not Executed

Output:

Simple If Statement Executed

Below is more complicated if statement inside a for loop that checks rows 1 through 15 in column 1 for certain criteria and then modifies the cells accordingly.

The first if statement checks if the cell is less than 10 AND is not empty (empty gives true for a value of 0) and changes the cell color to red.

The first elseif statement checks if the cell value is greater than or equal to 10 AND if it is less than 100 and changes the cell color to green.

The second elseif statement checks if the value is equal to 100 and changes the cell color to blue.

The third elseif statement checks if the value is greater than or equal to 100 and if it is numeric (string also gives a value greater than 100) and changes the cell color to gray.

The else statement gives everything else a value of "Blank." In this case, rows 11-15 do not fit any of the criteria in the other if statements above.

An example OR statement would be: If sheets("Sheet1").Cells(i, 1) < 10 Or Not IsEmpty(sheets("Sheet1").Cells(i, 1)) Then.

Input:

For i = 1 To 15 If sheets("Sheet1").Cells(i, 1) < 10 And Not IsEmpty(sheets("Sheet1").Cells(i, 1)) Then sheets("Sheet1").Cells(i, 1).Interior.Color = RGB(250, 0, 0) ElseIf sheets("Sheet1").Cells(i, 1) >= 10 And sheets("Sheet1").Cells(i, 1) < 100 Then sheets("Sheet1").Cells(i, 1).Interior.Color = RGB(0, 250, 0) ElseIf sheets("Sheet1").Cells(i, 1) = 100 Then sheets("Sheet1").Cells(i, 1).Interior.Color = RGB(0, 0, 250) ElseIf sheets("Sheet1").Cells(i, 1) >= 100 And IsNumeric(sheets("Sheet1").Cells(i, 1)) Then sheets("Sheet1").Cells(i, 1).Interior.Color = RGB(125, 125, 125) Else sheets("Sheet1").Cells(i, 1) = "Blank" End If Next i 

If Statement Not Executed

Output:

If Statement Executed

### Sum and Average

The code for the sum and average of a range can be seen below. I have also made certain cells have the string Sum: and Average: in order for the values to have a label.

Input:

sheets("Sheet1").Cells(15, 1) = "Sum:" sheets("Sheet1").Cells(16, 1) = "Average:" sheets("Sheet1").Cells(15, 2) = Application.WorksheetFunction.Sum(Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(10, 1))) sheets("Sheet1").Cells(16, 2) = Application.WorksheetFunction.Average(Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(10, 1))) 

Output:

Sum and Average

### User Input

Excel VBA can also ask for user input. The code below asks for the user's input using InputBox("Message", "Message Title") and assigns it to a certain cell. The code then uses a While loop to keep checking if the input is numeric. A while loop does not end until the criteria is met. Inside the while loop, a Message Box (MsgBox) pops up telling the user that the input was not numeric and then the code asks for the user's input again. The while loop keeps repeating until the user inputs a numeric value.

Input:

UserInput = InputBox("Enter a Value", "Input Message Title Goes Here") sheets("Sheet1").Cells(10, 10) = UserInput While Not IsNumeric(sheets("Sheet1").Cells(10, 10)) MsgBox "You have entered a non-numeric value. Please try again." UserInput = InputBox("Enter a Value", "Input Message Title Goes Here") sheets("Sheet1").Cells(10, 10) = UserInput Wend 

Output:

Input Message

Input Message Success

Input Message String

Input Message Error

### Last Row and Last Column

Acquiring the last row and last column of a data set is tremendously useful as one can create variables using those values which allows the Excel VBA code to adjust according to the data size. The Rows.Count and Columns.Count commands give the total number of rows and columns in a sheet, respectively. The .End(xlDIRECTION) moves the cell selection in the specified direction until it sees a non-empty cell. The .Row and .Column commands return the row and column of that specified cell, respectively. In the code below, the lastrow variable is moving up from the very last row in column 1 until it finds the first non-empty row which is the last row of the dataset. The same is happening with the lastcolumn variable except it uses columns instead of rows.

Input:

lastrow = sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row lastcolumn = sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column sheets("Sheet1").Cells(10, 5) = "Last Row:" sheets("Sheet1").Cells(10, 6) = lastrow sheets("Sheet1").Cells(11, 5) = "Last Column:" sheets("Sheet1").Cells(11, 6) = lastcolumn 

Output:

Last Row and Column

Dynamic counters for the for loops can now be created using the lastrow and lastcolumn variables. This makes it a lot easier to go through all the data in a table even if the data table changes size. In the code below, the rows start at i = 2 since row 1 is the header row and continues to lastrow. The columns start at j = 1 and continues to lastcolumn.

Input:

For i = 2 To lastrow For j = 1 To lastcolumn sheets("Sheet1").Cells(i, j).Interior.Color = RGB(125, 125, 125) Next j Next i 

Output:

Last Row and Column Loop

### Loop Chart

Another very useful VBA code that combines for loops with the lastrow and lastcolumn variables is a code that creates charts. As seen in the Scatter Plot with Lines section, using .SeriesCollection.NewSeries for every new series can make the code really large. Using for loops with the lastrow and lastcolumn variables reduces the size of the code significantly especially if you have +10 series.

In the code below, there is a for loop for the creation of each new series. The loop starts at column 2 since the first column contains the X values and continues to lastcolumn. The .SeriesCollection(#) number is i-1 since i starts at 2. The X values are in column one and the rows of the values range from row 2 to lastrow since the first row is the header. The values of each series are in column i and the rows range from row 2 to lastrow since the first row contains the headers. This makes the chart creation a lot shorter and less tedious by not having to input each new series individually.

Input:

sheets("Sheet1").Shapes.AddChart.Select With ActiveChart .HasTitle = True .ChartTitle.Text = "Test Chart" .ChartType = xlXYScatterLines .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X Values" .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Text = "Data" For i = 2 To lastcolumn .SeriesCollection.NewSeries .SeriesCollection(i - 1).Name = sheets("Sheet1").Cells(1, i) .SeriesCollection(i - 1).XValues = Range(sheets("Sheet1").Cells(2, 1), sheets("Sheet1").Cells(lastrow, 1)) .SeriesCollection(i - 1).Values = Range(sheets("Sheet1").Cells(2, i), sheets("Sheet1").Cells(lastrow, i)) Next i End With 

Output:

Last Row and Column Chart

### Match

One can find specific columns of data using the .Match command. Once you have found a specific data column, you can use that column number as a variable to modify those cells. The code below looks for an exact match (0) of the string "Data #" in the specified range of the first row from the first column up to last column. The code then uses that column number to modify the color of each of those cells.

Input:

Data1Column = Application.WorksheetFunction.Match("Data 1", Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(1, lastcolumn)), 0) Data2Column = Application.WorksheetFunction.Match("Data 2", Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(1, lastcolumn)), 0) Data3Column = Application.WorksheetFunction.Match("Data 3", Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(1, lastcolumn)), 0) sheets("Sheet1").Cells(1, Data1Column).Interior.Color = RGB(250, 0, 0) sheets("Sheet1").Cells(1, Data2Column).Interior.Color = RGB(0, 250, 0) sheets("Sheet1").Cells(1, Data3Column).Interior.Color = RGB(0, 0, 250) 

Output:

Last Row and Column Match

### Hide

Rows and columns can be hidden with the .Hidden command. The code below loops through rows 1 through 10 in column 1 and hides any cells' rows that meet a certain criteria.

Input:

For i = 1 To lastrow If sheets("Sheet1").Cells(i, 1) = "Bad" Then sheets("Sheet1").Rows(i).EntireRow.Hidden = True End If Next i 

Hiding Rows

Output:

Hidden Rows

You can unhide the rows by making the statement "False" in the .Hidden command.

Input:

sheets("Sheet1").Rows.EntireRow.Hidden = False 

The code below loops through columns 1 through 10 in row 1 and hides any cells' columns that meet a certain criteria.

Input:

For i = 1 To lastcolumn If sheets("Sheet1").Cells(1, i) = "Bad" Then sheets("Sheet1").Columns(i).EntireColumn.Hidden = True End If Next i 

Hiding Columns

Output:

Hidden Columns

You can unhide the columns by making the statement "False" in the .Hidden command.

Input:

sheets("Sheet1").Columns.EntireColumn.Hidden = False 

### Auto Filter

Filters can be added to the header row by using the .AutoFilter command.

Input:

Range(sheets("Sheet1").Cells(1, 1), sheets("Sheet1").Cells(1, lastcolumn)).AutoFilter 

Output:

Filter

### Screen Updating

If you have a large Excel VBA code, updating your screen every time a command executes can slow down your macro. Using the Application.ScreenUpdating = False can help execute your code in less time. Place your code in between Application.ScreenUpdating = False and Application.ScreenUpdating = True.

Input:

Sub TESTSUB() Application.ScreenUpdating = False *** THE REST OF YOUR CODE GOES HERE *** Application.ScreenUpdating = True End Sub 

### Master Subroutine

Creating multiple subroutines is useful if one would want to declutter their macros. Subroutines can be executed within another subroutine by using the Call command. Here, the Master subroutine calls two other subroutines: Sub1 and Sub2. These subroutines can be within the same module or different modules.

Input:

Sub Master() Call Sub1 Call Sub2 End Sub 

Master Sub Same Module

Master Sub Different Module

Subs Different Module

Output:

Sub Call

### Macro Button

Adding macro buttons to a sheet for quick access to a macro can be done by going to the Developer tab, selecting Insert, and clicking on Button. The button size will then be adjustable by dragging the mouse to the desired size. Right clicking on the button will allow the user to edit it. Left clicking on the sheet and then on the button will run the macro.

Create Button

Create Button Size

Assign Macro

Right Click to Edit Button

Rename Button

Resize Button

Move Button

Click Button

Button Clicked