Author of Programming Excel with VBA eBook

---

Flavio Morgado is a food engineer with a master’s degree in food science and technology. He is also a VBA professional developer, a technical writer, an English to Brazilian Portuguese technical translator, and a professor at UNIFESO Centro Universitário Serra dos Órgãos in the city of Teresopolis in Rio de Janeiro, Brasil.

He has written more than 30 books (and translated an equal number) all published just in Brazil. Flavio lives in Teresopolis; when he is not teaching, writing, or developing a love of something (or somebody…), he is running or riding his mountain bike throughout the Teresopolis Mountains, followed by his 11 dogs (2016’s dog count).

Programming Excel Contents

---

• Chapter 1: Understanding Visual Basic for Applications (VBA)
• Chapter 2: Programming the Microsoft Excel Application Object
• Chapter 3: Programming the Microsoft Excel Workbook Object 
• Chapter 4: Programming the Microsoft Excel Worksheet Object
• Chapter 5: Programming the Microsoft Excel Range Object 
• Chapter 6: Special Range Object Properties and Methods
• Chapter 7: Using Excel as a Database Repository
• Chapter 8: Creating and Setting a Worksheet Database Class 
• Chapter 9: Exchanging Data Between Excel Applications 
• Chapter 10: Using the Windows API 
• Chapter 11: Producing a Personal Ribbon Using RibbonEditor.xlam

Introduction to Programming Excel with VBA PDF

---

This book was created to teach you how to use Visual Basic for Applications (VBA) to automate worksheet applications.

It is a codebook that was written to teach everyone, including people with zero experience in programming and people with decent programming knowledge, how to use and apply programming techniques to better interact with the users of your Microsoft Excel solutions in a more professional way.

It uses some ready-made worksheet applications as practical examples of how you can produce solid, precise, and reliable worksheet applications based on VBA programming.

First I’ll introduce you to the VBA environment and the language structure and show you some basic examples that will take you on a consistent journey through the Excel object model. All the proposed examples use VBA to teach you how to interact with the Excel object model and its many properties, methods, and events.

As a basic strategy for teaching Excel VBA programming, this book uses the VBA Immediate window to first test each proposed Excel feature before showing how to use it in a code procedure.

It is full of programming examples whose complexity grows from the book’s beginning to its end. This means it was written to be read one chapter at a time, with each chapter using the knowledge of previous chapters to provide a jump on your programmer skills.

My teaching strategy is to show a UserForm interface or code procedure step-by-step and to comment on all its instructions, one at a time, to give you a better understanding of how the VBA code can use good programming techniques to produce the desired result, with clear, concise, and reusable code.

What is in Programming Excel with VBA eBook

This book is divided into 11 chapters, each one approaching Excel VBA programming with new complexity. To get a big picture of what you will find inside it, here is a summary of each chapter:

Chapter 1, “Understanding Visual Basic for Applications (VBA),” is intended to show you the VBA metaphor, including how to use VBA integrated development environment (IDE) and the VBA language structure. It is a basic chapter to show you a first approach to VBA and a programming language.

Chapter 2, “Programming the Microsoft Excel Application Object,” touches on the first programmable object in the Microsoft Excel object model hierarchy: the Application object, which represents the Microsoft Excel application window, with some of its main properties, methods, and events.

In this chapter, you will learn about the FileDialog, GetOpenFileName, GetSaveAsFileName, and OnTime methods; when the Application object events fire; and how to use a Class module to watch and/or control whether a worksheet tab name can be changed, as a first approach to producing VBA objects.

Chapter 3, “Programming the Microsoft Excel Workbook Object,” talks about the second layer of the Microsoft Excel object model, which represents the Workbooks collection and contains all the open Workbook objects inside the main Excel window.

You will learn about how and when the Workbook object events fire and will see the VBA UserForm and its controls, learning how to use the ListBox control and its interface (properties, methods, and events) to interact with the Workbook object using VBA code.

Chapter 4, “Programming the Microsoft Excel Worksheet Object,” touches on the third object level in the Excel object model hierarchy: the Worksheet object, which represents the sheet tab in an Excel workbook file.

It also shows how its programmable interface is composed (it properties, methods, and events). In this chapter, you will use again a VBA UserForm to learn how to add, delete, copy, move, rename, sort, and change sheet tab visibility inside a workbook file.

You will also learn the many ways (and the preferable one) to reference sheet tabs in the VBA code and how to control the “cascade events” phenomenon that happens with VBA objects.

Chapter 5, “Programming the Microsoft Excel Range Object,” talks about the deepest object inside the Excel object model hierarchy: the Range object, which can represent any number of cells inside a Worksheet object.

Using another UserForm interface, you will learn how to programmatically define the cell addresses that compose any selected range, how to use VBA to name a range (using the Names collection), how to use the VBA Collection object, and how to produce a similar interface to the Excel Name Manager interface using VBA, improving the Excel interface by allowing you to change the range name visibility inside the workbook file.

Chapter 6, “Special Range Object Properties and Methods,” expand your knowledge about the Range object by covering the Cells and CurrentRegion properties and the End, OffSet, Find, AutoFilter, Sort Copy, and PasteSpecial methods, using again the UserForm approach.

At the end of this chapter, you are presented with the frmFindFoodItems UserForm that uses most of this knowledge to find food items in the USDA food table using different search criteria (by food item name or nutrient content).

Chapter 7, “Using Excel as a Database Repository,” presents you with a programmable approach to implementing a database storage system to store worksheet data as database records in unused worksheet rows, based on a data validation list cell and range names.

This chapter uses all the knowledge gathered so far to produce a standard code module full of procedures that use module-level constant values to define the database parameters, allowing you to adapt it to any worksheet application design.

Chapter 8, “Creating and Setting a Worksheet Database Class,” expands the database code module to a Class module that uses range names to store the database properties inside unused worksheet rows.

It defines the SheetDBEngine class interface, showing how to implement its properties, methods, and events.

To allow you to easily use the SheetDBEngine class, this chapter also presents the frmDBPRoperties UserForm, which was produced as a database wizard, to help implement the database storage system on any worksheet application.

Chapter 9, “Exchanging Data Between Excel Applications,” answers important questions regarding how to programmatically update worksheet-based tables from where the worksheet application gathers its data.

It teaches how to update the USDA worksheet to any version released by the ARS-USDA web page, using either a simple, silent procedural approach or a UserForm that reacts to the updating process, finding food item name inconsistencies between two releases of SRxx.accdb or SRxx.

MDB Microsoft Access database nutrient tables. You will be also presented with the firm manage UserForm to allow mass operation on the worksheet database so you can delete and save recipe nutrient data and export and import recipes, using VBA automation.

Chapter 10, “Using the Windows API,” takes your VBA knowledge gathered so far in this book to the next level by teaching you how to understand and use the Windows application programmable interface (API), based on dynamically linked libraries (DLLs) and C++ procedures.

The API can be declared and called from within a VBA project. In this chapter, you will learn how to implement a Timer class to create programmable timer objects and how to change the UserForm appearance by removing its title bar; adding a resizable border and minimize, restore, and maximize buttons; and adding transparency and creating a fade effect.

You’ll also learn how to animate a UserForm when it loads and how to apply a skin effect to change the UserForm shape.

Chapter 11, “Producing a Personal Ribbon Using RibbonEditor.xlam,” uses the RibbonX VBA add-in developed by Andy Pope as free software to produce a personalized ribbon with tools that interact with your worksheet application, giving it a professional appearance.

The afterword is a brief testimony of the path used to write this book, with my insights about how I imagined it and created it step-by-step, with trial-and-error experimentation as I learned the Excel object model.

 

Programming Excel with VBA: A Practical Real-World Guide PDF

Author(s): Flavio Morgado (auth.)

Publisher: Apress, Year: 2016

ISBN: 978-1-4842-2204-1

 

Download Programming Excel with VBA by Flavio Morgado PDF Free

Download