Excel Visual Basic Codes and Tricks
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
- Clear Contents of Clipboard
- Combine Data from two Spreadsheets into one Spreadsheet
- Date Validation
- Fixed Column Width
- Format Function for Numbers
- Headers and Footers
- Hiding Toolbars
- Pivot Tables - Printing
- Pivot Tables - Refresh
- Printing - Hide Rows or Columns
- Printing - Ranges from a ComboBox
- Query - Refresh Upon Opening Workbook
- Start a Macro by Clicking in a Cell
Auto Run
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 |
Clear Contents of Clipboard
Application.CutCopyMode=False |
Combine Data from two Spreadsheets into one Spreadsheet
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 |
Date Validation
if isdate(textbox.value) then cells(row,column) = textbox.value else msgbox "Invalid Date" endif |
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 |
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]) |
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' |
Headers and Footers
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".
Hiding Toolbars
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 |
Pivot Tables - Printing
'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 |
Pivot Tables - Refresh
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 |
Printing - Hide Rows or Columns
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 |
Printing - Ranges from a ComboBox
First, type the data within your spreadsheet (Days, Months, and Years) in the same ranges as the picture above. Second, create a button from the Control Toolbox (choose View on the menu bar > Toolbars > Control Toolbox). Choose Command Button and insert onto spreadsheet. Change the text to Printing Options. Enter the Visual Basic Editor (Alt-F11). For the purpose of this exercise, we will name the button btnPrintingOptions. Enter the following code for your btnPrintingOptions:
Private
Sub btnPrintingOptions_Click() UserForm.Show 'This code will load the UserForm (we haven't constructed it yet) End Sub |
Within the VB editor,
construct a Form within the workbook and name the form UserForm.
Insert a Label, ComboBox (name it cboPrintOptions) and CommandButton
(btnExit) and optional picture. Within the UserForm (Object) and
the Activate (Procedure) window, enter the following code:
Private
Sub btnExit_Click() Unload UserForm End Sub Private Sub cboPrintOptions_Click() Select Case cboPrintOptions.Value Case "Days" Worksheets("Sheet1").Range("B6:B10").PrintOut Case "Months" Worksheets("Sheet1").Range("D6:D10").PrintOut Case "Years" Worksheets("Sheet1").Range("F6:F10").PrintOut End Select End Sub Private Sub UserForm_Activate() With cboPrintOptions .AddItem "Days" .AddItem "Months" .AddItem "Years" End With End Sub |
Query - Automatically Refresh Upon Opening Workbook
First, this subroutine (Refresher) will connect to the Data file using a specified connection string (the link to your data) and refresh any number of sheets containing one query within the workbook. This example assumes that all of your queries within this workbook share the same data source:
Sub Refresher(x) ConnString = "ODBC;DRIVER=SQL Server;SERVER=ServerName;UID=UserId;PWD=Password;APP=Microsoft Office 2003;WSID=wsid;DATABASE=DatabaseName" Sheets(x).QueryTables(1).Connection = ConnString Sheets(x).QueryTables(1).Refresh End Sub |
Second, this subroutine (RefreshQueries) will call the above routine (Refresher) to refresh any specified sheets within the workbook. In this example, three separate spreadsheets (Sheet 1, Sheet 2 & Sheet 3) all contain query tables that will refresh once passed to the Refresher subroutine.
Sub RefreshQueries() Refresher ("Sheet 1") Refresher ("Sheet 2") Refresher ("Sheet 3") End Sub |
Third, create an event
procedure for the when the workbook opens. The Object (Workbook)
and the Procedure (Open) calls upon the subroutine Refresh Queries
above:
Private
Sub Workbook_Open() RefreshQueries End Sub |
You can add as many
worksheets within your subroutine RefreshQueries that contain queries
needing updating.
Start a Macro by Clicking in a Cell
Private
Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address <> "$A$1" Then Exit Sub Macro End Sub |