Excel is an excellent software program for creating spreadsheets and charts for reporting purposes. However, that's not the limit of Excel's functionality.

Apple Online Store

Visual Basic for Applications (VBA) is a programming language. With the addition of the VBA Editor, it is possible to use Excel for far more than just basic reports. Excel VBA can automate complex tasks and perform complicated series of actions. The intent of this page is to show some useful Excel VBA  examples that I have picked up in the process of creating my own applications. Click on the bulleted items below to view Excel Visual Basic codes and tricks.

 

Auto Run

How do you make your macros run automatically when opening your workbook. You can either use the Auto Open method or the Workbook Open method. These macros will display the message "Hello" when you open the workbook.

Sub Auto_Open()
 Msgbox "Hello"
End Sub


This code would be located in the module. However if you use the second method, the code must be in the workbook (double click "This Workbook" in the explorer window). Click on the drop down list (that says General) and select Workbook. Click on the drop down list (that says declarations) and select Open.

Private Sub Workbook_Open()
 Msgbox "Hello"
End Sub 

Back to Top^

 

Clear Contents of Clipboard

To clear the clipboard and prevent message boxes prompting "There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another program later?", use the following code:

Application.CutCopyMode=False

Back to Top^

 

Combine Data from two Spreadsheets into one Spreadsheet

To combine data from two spreadsheets into one spreadsheet, assume the following: Three spreadsheets within the same workbook will be used in this example: Sheet1, Sheet2, and CombinedData. The header rows on each spreadsheet that contains data (Sheet1 and Sheet2) are the same. The data from both spreadsheets will be combined (along with just one header row) into one spreadsheet named "CombinedData." The procedure will be called AppendData.

Sub AppendData()

 'Start within the spreadsheet CombinedData
 Dim totalrows As Integer
 Dim x As Integer
 totalrows = ActiveSheet.UsedRange.Rows.Count
 x = totalrows

 Application.ScreenUpdating = False

 'Clear the contents of the spreadsheet CombinedData
 Sheets("CombinedData").Select
 Cells(1, 1).Select
 ActiveCell.CurrentRegion.Clear
 i = 1
 'Copy data from Sheet1
 Sheets("Sheet1").Select
 Range("A1").Select
 ActiveCell.CurrentRegion.Copy
 j = ActiveCell.CurrentRegion.Rows.Count

 'Paste data from Sheet1 to CombinedData
 Sheets("CombinedData").Select
 Cells(i, 1).Activate
 ActiveSheet.Paste
 i = j + i

 'Copy data from Sheet2
 Sheets("Sheet2").Select
 Range("A1").Select
 ActiveCell.CurrentRegion.Copy
 j = ActiveCell.CurrentRegion.Rows.Count

 'Paste data from Sheet2 to CombinedData
 'Note that i will be row count of where Sheet1 data ends
 Sheets("CombinedData").Select
 Cells(i, 1).Activate
 ActiveSheet.Paste
 'Delete header row taken from Sheet2
 ActiveCell.EntireRow.Delete

 Range("A1").Select

 Application.ScreenUpdating = True

End Sub

Back to Top^

 

Date Validation

Can you validate a date based on information inputted within a form? The following code can be inserted within an Exit Event of the text box or OK button on the form to validate a date was entered:

if isdate(textbox.value) then
 cells(row,column) = textbox.value
else
 msgbox "Invalid Date"
endif

Back to Top^

 
 

Fixed Column Width

This won't lock/freeze the column, but whenever the user moves the cursor it will automatically reset the column width to what you want it at.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 columns("A:A").columnwidth=14
End Sub

Back to Top^

 

Format Function for Numbers

The Format function takes an expression and returns it as a formatted string. The syntax for the Format function is:

Format(expression, [format])

Where expression is the value to format. "Format" is optional. It is the format to apply to the expression. You can either define your own format (example given after the table) or use one of the named formats that Excel has predefined in the table below:

Format Explanation
General Number Displays a number without thousand separators.
Currency Displays thousand separators as well as two decimal places.
Fixed Displays at least one digit to the left of the decimal place and two digits to the right of the decimal place.
Standard Displays the thousand separators, at least one digit to the left of the decimal place, and two digits to the right of the decimal place.
Percent Displays a percent value - that is, a number multiplied by 100 with a percent sign. Displays two digits to the right of the decimal place.
Scientific Scientific notation.
Yes/No Displays No if the number is 0. Displays Yes if the number is not 0.
True/False Displays True if the number is 0. Displays False if the number is not 0.
On/Off Displays Off if the number is 0. Displays On is the number is not 0.

Some named and custom examples and their results:

Format(250.6, "Standard") would return '250.60'
Format(0.981, "Percent") would return '98.10%'
Format(1267.5, "Currency") would return '$1,267.50'
Format(250.6, "#,###") would return '250'
Format(250.6, "#,###.#") would return '250.60'
Format(250.6, "#,###.##") would return '250.600'
 

Back to Top^

 

Headers and Footers

How do you insert the filename and path in the footer of a document?

Sheets("Name of Sheet").PageSetup.CenterFooter = ActiveWorkbook.FullName


A cell reference works fine too, such as:

Sheets("Name of Sheet").PageSetup.CenterFooter = Sheets("Name of Sheet").Range("A1")


If you want the information to be inserted within the header, just change the "CenterFooter" to "CenterHeader".

Back to Top^

 

Hiding Toolbars

How do I hide toolbars within my excel spreadsheet? The first step is to create macros which remove the toolbars (macro: RemoveToolbar) upon opening the spreadsheet and then restore the toolbars (macro: RestoreToolbar) when closing the spreadsheet. If you don't restore the toolbars, the next time the user runs Excel they will be in for quite a surprise. Here are the sub routines calling the macros upon opening and closing the workbook.

Macro RemoveToolbar code:

Private Sub Workbook_Open()
 Run "RemoveToolbar"
End Sub

   
Macro RestoreToolbar code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Run "RestoreToolbar"
End Sub


This is the code for removing the toolbars:

Sub RemoveToolbar()
  Application.CommandBars("Worksheet Menu Bar").Enabled = False

 ' Formula Bar (Restore after application is closed)
 Application.DisplayFormulaBar = False

 ' Status Bar (Restore after application is closed)
 ' Application.StatusBar = False

 Application.CommandBars("Standard").Visible = False
 Application.CommandBars("Formatting").Visible = False
 Application.CommandBars("Borders").Visible = False
 Application.CommandBars("Chart").Visible = False
 Application.CommandBars("Control Toolbox").Visible = False
 Application.CommandBars("Drawing").Visible = False
 Application.CommandBars("External Data").Visible = False
 Application.CommandBars("Forms").Visible = False
 Application.CommandBars("Formula Auditing").Visible = False
 Application.CommandBars("List").Visible = False
 Application.CommandBars("Picture").Visible = False
 Application.CommandBars("PivotTable").Visible = False
 Application.CommandBars("Protection").Visible = False
 Application.CommandBars("Reviewing").Visible = False
 Application.CommandBars("Task Pane").Visible = False
 Application.CommandBars("Text To Speech").Visible = False
 Application.CommandBars("Formatting").Visible = False
 Application.CommandBars("Visual Basic").Visible = False
 Application.CommandBars("Watch Window").Visible = False
 Application.CommandBars("Web").Visible = False
 Application.CommandBars("WordArt").Visible = False
 
End Sub


This is the code for restoring the toolbars:

Sub RestoreToolbar()

 ' Restores Formula Bar
 Application.DisplayFormulaBar = True

 ' Restore Status Bar
 ' Application.StatusBar = True

 Application.CommandBars("Standard").Visible = True
 Application.CommandBars("Formatting").Visible = True
 Application.CommandBars("Worksheet Menu Bar").Enabled = True

End Sub


Back to Top^

 

Pivot Tables - Printing

How do you print only a pivot table within a spreadsheet and capture all data no matter how many times you refresh the pivot table?

'Assume your pivot starts at cell B12
 Range("B12").Select
 ActiveCell.PivotTable.RowRange.Select
 'x counts the number of rows starting from B12
 x = Selection.Rows.Count
 'y defines a range based on this pivot table starting in column B
 'and ends within column E
 '11 rows are added onto x due to our starting point at cell B12
 y = "B12:E" & x + 11
 ActiveSheet.PageSetup.PrintArea = ""
 ActiveSheet.PageSetup.PrintArea = y
 Worksheets("Pivot-Beds").PrintOut
 Range("A1").Select


Back to Top^

 

Pivot Tables - Refresh

How do you refresh a pivot table within a spreadsheet or multiple pivot tables within a workbook?

Refresh a Single Pivot Table:

Sub PivotMacro()
 Dim pt As PivotTable
 Set pt = ActiveSheet.PivotTables("MyPivot")
 pt.RefreshTable
End Sub

Refresh all Pivot Tables in a Worksheet:

Sub AllWorksheetPivots()
 Dim pt As PivotTable
 For Each pt In ActiveSheet.PivotTables
 pt.RefreshTable
 Next pt
End Sub

Refresh Chosen Pivot Tables in a Worksheet:

Sub ChosenPivots()
 Dim pt As PivotTable
 For Each pt In ActiveSheet.PivotTables
 Select Case pt.Name
 Case "PivotTable1", "PivotTable4", "PivotTable8"
 pt.RefreshTable
 Case Else
 End Select
 Next pt
End Sub

Refresh All Chosen Pivot Tables in a Workbook:

Sub AllWorkbookPivots()
 Dim pt As PivotTable
 Dim ws As Worksheet
 For Each ws In ActiveWorkbook.Worksheets
 For Each pt In ws.PivotTables
 pt.RefreshTable
 Next pt
 Next ws
End Sub


Back to Top^

 

Printing - Hide Rows or Columns

Hide rows (this example hides rows 1 through 4):

With ActiveSheet
 .Range("1:4").EntireRow.Hidden = True
 .PrintOut
 .Range("1:4").EntireRow.Hidden = False
End With


Hide columns (this example hides column B and D):

With ActiveSheet
 .Range("B1,D1").EntireColumn.Hidden = True
 .PrintOut
 .Range("B1,D1").EntireColumn.Hidden = False
End With

Back to Top^