Excel VBA in easy steps, 3rd Ed

Mike McGrath

• Publisher: In Easy Steps Limited
• Year: 2019
• ISBN:
  9781840788716
• Topic: Computer Science
• Subtopic: Microsoft Programming
• Index: Computer Science

Excel VBA in easy steps neatly demonstrates VBA (Visual Basic for Applications) Macro programming in a clear and concise manner, so you can get more from the popular Microsoft Excel spreadsheet application. Areas covered include:

• Recording and editing Macros
• Creating your own Macro programs
• Coding with the VBA language
• Responding to Worksheet events
• Programming Excel procedures
• Making 'DataForms' for data editing
• Producing 'UserForms' for data entry
• Displaying dynamic Charts
• Sharing your Excel apps to impress your colleagues!

Whether you're upgrading to Excel 2019, exploring Excel in the Office 365 suite, or new to the Macro programming concept, use this guide to learn the key features constructively and get more out of Microsoft Excel – in easy steps!

1

Getting started

Welcome to the exciting world of Excel VBA ( Visual Basic for Applications). This chapter demonstrates how to create a VBA macro for Excel workbooks.

Introducing Excel VBA

Recording a macro

Viewing macro code

Testing a macro

Editing macro code

Referencing relatives

Saving macros

Trusting macros

Summary

Introducing Excel VBA

Visual Basic for Applications (VBA) is the programming language that is built into the Excel spreadsheet application and other Microsoft Office applications. It extends Excel so it can perform tasks that can’t be done with standard Excel tools, and provides the capability to automate many routine tasks.

The examples in this book assume the reader is an experienced Excel user who can accomplish these fundamental operations:

•Create workbooks and insert worksheets

•Navigate around a workbook and worksheet

•Use the Excel Ribbon interface

•Name cells and ranges

•Use the Excel worksheet functions

All examples are demonstrated using Excel 2019, although most examples are also applicable to earlier versions of Excel.

If you’re just starting out with Excel, please refer to our companion book Excel 2019 in easy steps.

Enabling VBA

Before you can get started using the capabilities of VBA, it must first be enabled in your Excel installation:

Launch Excel, then choose to open a Blank workbook

When the workbook opens, choose the File item on the Excel Ribbon

All the examples in this book are available for free download at www.ineasysteps.com/resource-centre/downloads

Next, select the Options item – to open the “Excel Options” dialog box

In the Excel Options dialog, choose the Customize Ribbon item on the left-hand pane

Now, check the Developer option box in the right-hand pane

Click the OK button to apply the change and to close the Excel Options dialog box

See that a Developer tab has been added to the Ribbon

Choose the Developer tab to see a Visual Basic button in the Ribbon’s “Code” group – VBA is now enabled

You can also open the Excel Options dialog box by pressing the ...

Table of contents

• Cover
• Title
• Copyright
• Contents
• 1 Getting started
• 2 Writing macros
• 3 Storing values
• 4 Performing operations
• 5 Making statements
• 6 Executing procedures
• 7 Employing functions
• 8 Recognizing events
• 9 Opening dialogs
• 10 Providing UserForm dialogs
• 11 Developing apps