Programming Excel with VBA: a practical real-world guide

✍ Scribed by Morgado, Flavio

• Publisher: Apress
• Year: 2016
• Tongue: English
• Leaves: 802
• Category: Library

✦ Synopsis

Learn to harness the power of Visual Basic for Applications (VBA) in Microsoft Excel to develop interesting, useful, and interactive Excel applications. This book will show you how to manipulate Excel with code, allowing you to unlock extra features, accuracy, and efficiency in working with your data.Programming Excel 2016 with VBAis a complete guide to Excel application development, using step-by-step guidance, example applications, and screenshots in Excel 2016.
In this book, you will learn:
How to interact with key Excel objects, such as the application object, workbook object, and range object


Methods for working with ranges in detail using code


Usage of Excel as a database repository


How to exchange data between Excel applications


How to use the Windows API to expand the capabilities of Excel


A step-by-step method for producing your own custom Excel ribbon
Who This Book Is For:Developers and intermediate-to-advanced Excel users who want to dive deeper into the capabilities of Excel 2016 using code.

✦ Table of Contents

At a Glance
Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Understanding Visual Basic for Applications (VBA)
What Is Macro Code?
The VBA Environment
Modules: The VBA Documents
Standard and Class Modules
Class Modules
The VBA Language
Procedures: The VBA Code
Using Function and Sub Procedures
Calculating Age in Years
Test Procedure Codes Using VBA Immediate Window
Using Your Function Procedure Inside Excel
Executing Code Procedures Step-by-Step
Variable Declaration
Implicit vs. Explicit Variable Declaration. Using Option ExplicitVariable Types
Array Declares
Variable Scope and Lifetime
Using the Static Statement to Hold Any Variable Value
Using Code Module Variables
Using Private Code Module Variables
Create a Flow Chart for the Algorithm of Complex Procedures
Comment Your Code!
Using Public Code Module Variables
Public Procedures and Variables Constitute the Module Interface
Using Enumerators
Passing Arguments by Reference or by Value
Using a Naming Convention
Using Property Procedures
Property Procedures Allow Greater Control of Private Variables. VBA Statements, Functions, and InstructionsUsing VBA Instructions
VBA Logical Decision Instructions
Making Decisions with If & nd If Instructions
Making Decisions with the Select Case & nd Select Instruction
VBA Looping Statements
The For & ext Statement
The For Each & ext Statement
The While & nd and Do & oop Statements
Using Event Procedures
Using Class Modules
Declaring and Raising Events on Object Code Modules
Using VBA UserForms
The VBA Me Keyword
Evoking a VBA Procedure from an Excel Worksheet
Two Special VBA Functions: MsgBox and InputBox
Using MsgBox()
Using InputBox. Dealing with VBA ErrorsThe On Error Resume Next Instruction
Setting an Error Trap
Protecting Your VBA Code
Conclusion
Summary
Chapter 2: Programming the Microsoft Excel Application Object
The Microsoft Excel Object Model
The Application Object
Using Application Properties to Control the Way the Excel Interface Behaves
Using Application Methods to Show Excel File Dialogs
Using the FileDialog Method
Open One Single File
Open Many Files
Using the GetOpenFileName and GetSaveAsFileName Methods
Open One Single File
Open Many Files
Using Application InputBox Method. Using Application OnTime MethodUsing Application Events to React to User Actions
Creating an Excel. Application Object Reference
Firing Application Events
Using Class Modules to Control Application Object Events
Using a Class Module to Control Sheet Tab Name Changes
Chapter Summary
Chapter 3: Programming the Microsoft Excel Workbook Object
The Workbook Object
Using Workbook Object Events
Workbook Open Event and the frmSplashScreen UserForm
Implementing a UserForm Timer
Using the Application. OnTime Method
Using the VBA Timer() Function
Using the UserForm Repaint Method.