Brilliant Excel VBA Programming

By Curtis Frye
October 2013
Pearson Education
Distributed by Trans-Atlantic Publications
ISBN: 9780273771975
265 pages, Illustrated
$49.50 Paper original

---

Develop the Excel skills that you need to produce spreadsheet solutions to your business needs – instantly!

There are an estimated 400 million Excel users, but the majority of them haven’t worked out a way to effectively use Excel VBA (Visual Basic for Applications).

This book is for those that use Excel at work and who want to use the advanced features of Excel but don’t have the time to have extensive training. It takes you through each task (as all Brilliant books do) and describes everything you could want to find out, for example:

- how to use the macro recorder

- how to take that recorded code and turn it into something useful that will really help you day to day

- how to automate reports and design applications

Users will then improve their Excel skills so they can in the end save a lot of time and get more done.

Brilliantguides allow you to find the info you need quickly, easily and without fuss. It guides you through all the tasks using a highly visual, step-by-step approach – providing exactly what you need to know when you need it.

Contents:

Introduction

1 Recording and running macros

• Record a macro
• Run a macro
• Edit a macro
• Delete a macro
• Record a macro using relative references
• Assign a macro to a keyboard shortcut
• Run a macro by clicking a shape
• Add a macro to the Quick Access Toolbar
• Customise a Quick Access Toolbar button
• Save a macro-enabled workbook
• Managing Excel 2010 security settings
• Change Protected View settings
• Change message bar settings
• Change data connection security settings
• Add a digital signature to a workbook

2 Starting with the Visual Basic Editor

• Introduce object-orientated programming
• Display the Developer ribbon tab
• Display the Visual Basic Editor
• Set project properties
• Create a code module
• Create a subroutine
• Create a function
• Add a comment to your code
• Run a VBA routine
• Rename a code module
• Delete a code module
• Export a code module to a text file

3 Working with data and variables

• Understand data types in Excel VBA
• Declare a variable
• Require variable declaration before use
• Manage variable scope
• Perform calculations using mathematical operators
• Define a constant
• Define a static variable
• Define an array
• Define a multidimensional array
• Redefine an array
• Define a dynamic array
• Display an object type
• Define an object variable

4 Managing workbooks and files

• Open a workbook
• Open a text file as a workbook
• Open a file the user selects
• Save a workbook
• Save a workbook in a different format
• Detect if a workbook is open
• Close a workbook
• Create a new workbook
• Delete a file

5 Managing worksheets

• Add a worksheet
• Delete a worksheet
• Move a worksheet
• Copy a worksheet
• Hide or unhide a worksheet
• Rename a worksheet
• Protect a worksheet
• Print a worksheet

6 Managing ranges

• Activate a cell range
• Select a cell range
• Select the active region
• Refer to cells using Offset
• Insert a cell range
• Delete a cell range
• Hide worksheet columns or rows
• Create a named range
• Resize a selectedrange
• Set the column width
• Set the row height

7 Managing cells

• Cut and paste a cell range
• Copy and paste a cell range
• Copy and paste values in cells using PasteSpecial
• Transpose a column into a row
• Create a cell comment
• Display a cell's comment
• Hide a cell's comment
• Delete a cell's comment
• Fill a range of cells automatically
• Copy a range to multiple sheets
• Add a cell border
• Find a cell value
• Replace a cell value

8 Formatting worksheets and worksheet elements

• Apply bold, italic and underline formatting
• Change a cell’s font
• Change a cell’s font size
• Change a cell’s font colour
• Change a cell’s fill colour
• Change a cell’s alignment
• Apply a cell style
• Apply a number format to a cell
• Clear a cell's format

9 Sorting and filtering data

• Sort cell data using a single criterion
• Create a multilevel sort
• Sort using a customised list of values
• Turn on filter arrows using VBA code
• Apply a filter using a single criterion
• Remove a filter
• Display a list of unique values
• Filter data to display two values in a column
• Filter data to display three or more values in a column
• Filter data based on values in multiple columns

10 Managing charts

• Create a chart
• Move a chart to a chart sheet
• Add a new data series to a chart
• Format a chart's legend text
• Format a chart's axis text
• Export a chart as an image
• Create a Line sparkline
• Create a Column sparkline
• Create a Win/Loss sparkline
• Delete a sparkline

11 Using built-in functions and statements

• Use the built-in Open dialog box
• Prevent screen flicker when running VBA code
• Suppress and restore alerts
• Calculate data using Excel worksheet functions
• Display a message box
• Get data from an InputBox
• Display the current date and time
• Format a date
• Remove spaces from a string
• Locate a portion of a string
• Concatenate strings

12 Managing code ssing logical constructs

• Create a For…Next loop
• Create a For…Each loop
• Create an If…Then...ElseIf statement
• Create a Case statement
• Create a Do loop
• Create a Do…While loop
• Create a Do...Until loop
• Call another macro from within your code
• Refer to objects using a With…End With statement

13 Debugging your VBA code

• Execute code in the Immediate window
• Set a breakpoint in your VBA code
• Watch a value in a routine
• Step through your code one line at a time
• Skip over a subroutine
• Step out of a subroutine
• Create an On Error GoTo statement
• Manage errors using an On Error Resume Next statement
• Manage errors using an On Error GoTo 0 statement

14 Using Excel events in your VBA code

• Display the available events
• Run a procedure when you open a workbook
• Run a procedure when you close a workbook
• Run a procedure when you save a workbook
• Run a procedure when a cell range changes

15 Gathering data with UserForms

• Create a UserForm
• Add a TextBox to a UserForm
• Add a ListBox to a UserForm
• Add a ComboBox to a UserForm
• Add an option button to a UserForm
• Add graphics to a UserForm
• Add a SpinButton to a UserForm
• Create a multipage or multitab UserForm
• Write UserForm data to a worksheet
• Display, load and hide a UserForm

Jargonbuster

Troubleshooting guide

 

Return to main page of Trans-Atlantic Publications