Excel VBA · the Ultimate Beginner's Guide to Learn VBA Programming Step by Step by Williams, David A. -- Read -- Imperial Library of Trantor

Index

Introduction Chapter One: What Can You Do With VBA?

Common Uses of VBA

Automating Documents Customizing Application Interfaces Adding new application features

Chapter Two: Parts of the Program

Defining the Parts of a Program Programming Blocks

Project Module, class module and forms Function and Sub Statement

Using the Macro Recorder Using Subs Using Functions Comments

Writing basic comments Knowing when to use comments Writing Good Comments

Chapter Three: Fundamentals Of VBA

Project Explorer Properties Code Looking at the VBA Toolbox Starting the Visual Basic Editor Using Project Explorer

Working with special entries Using the properties window Understanding property types Getting help with properties Using the Code window Opening an existing Code window Creating a new Code window

Chapter Four: VBA, A Primer

Macro Recorder

Record A Macro Run The Recorded Macro See The Macro

Security and Macro Storage

How To Add A Trusted Location

How To Enable Macros Outside A Trusted Location

Disable All Macros Without Notification Disable All Macros With Notification Disable All Macros Except Digitally Signed Macros Enable All Macros Disabling All Macros With Notification

Chapter Five: Working With Loops

The For Loop

For Loop Example 1 Example 2

For…Next Statement The For Each … Next Statement

Example 1 Example 2

Nesting Loops The Exit For Statement Do…Loop Statement

Syntax

Chapter Six: Working With Conditional Statements

If…Then…Else Statements

Multiple-line Statements Nesting Using the Not Operator Single Line If…Then…Else Statements

Select…Case Statement

Syntax Using the To Keyword Using the Is Keyword Using a comma Nesting Go To Statement

Select…Case Statements Versus the If…Then…Else Statements

Chapter Seven: Working With Strings

Points to Remember

Original String Does Not Change Comparing Two Strings

Appending Strings Extracting Parts of a String Searching in a String

InStr InStrRev

Removing Blanks Length of a String Reversing a String

Chapter Eight: Arrays

Structured Storage Array Types VBA Array

Step 1 – Create A New Workbook Step 2 – Add A Command Button Step 3 – Save The File Step 4 – Write The Code

Example To Enter Student Marks

Without An Array Using An Array

Example With Loops Sorting An Array Example For Creating A Two-Dimensional Array

Chapter Nine: Error Handling And Debugging

The On Error Statement Enabled And Active Error Handlers The Resume Statement Error Handling With Multiple Procedures

A Note Of Caution

Chapter Ten: How To Improve The Performance Of Macros

Close Everything Except For The VBA Essentials

Disabling All The Animations Using System Settings Disabling Office Animations Using Registry Settings

Removing Unnecessary Selects

Before After

Using The With Statement To Read Object Properties

Before After

Chapter Eleven: How to Redirect the Flow

Using the GoTo statement correctly

Loops Exits Program flow problems

Chapter Twelve: Working with Excel Workbooks and Worksheets

The Workbook Collection The Worksheet Collection Charts Collection

Chapter Thirteen: Some Problems With Spreadsheets And How To Overcome Them

Multi-User Editing Shared Workbooks Linked Workbooks Data Validation Navigation Issues Security Issues Speed Issues Enter the database

Choosing The Right Database

Chapter Fourteen: How To Use Data From Excel

Property Application.ScreenUpdating Open the Source File and Read Data

Chapter Fifteen: How to Manipulate Data In Excel

How to Analyze and Manipulate Data In A Spreadsheet

How To Construct An Array And Locate The Range

Different Ways To Manipulate Data

Count The Number Of Sheets In A Workbook Filter By Using Advanced Criteria Getting Values From Another Worksheet Adding Named Ranges to Specific Sheets Copy An Entire Row For Pasting Inserting A Column Into A Worksheet Insert Multiple Columns Into A Worksheet Select A Specific Sheet

Chapter Sixteen: Resources For VBA Help

Allow Excel To Write The Code For You The Location Matters When You Ask For Help Choose Online Help Over Offline Help Using Code From The Internet Leveraging Excel VBA User Forums Leveraging on Excel VBA Blogs And Articles

ExcelGuru Org Contextures DailyDose MrExcel

Mining YouTube For Some Excel VBA Training Videos Attending A Live Online Excel VBA Training Class Dissecting Other Excel Files In Your Organization Ask The Local Excel Guru

Chapter Seventeen: Mistakes To Avoid

Not Using Arrays Using .Select or .Activate Using Variant Type Not Using Application.ScreenUpdating = False Referencing the Worksheet Name With a String Not Qualifying the Range References Writing a Big Function Using Nested For or If Statements

Conclusion References Exit Do

Example 1 Example 2

The Do While Loop

Do While…Loop Statement Example 1 Example 2 Example 3

The Exit Do Statement The Do Until Loop

Do Until…Loop Statement Example 1 Example 2

The Exit Do Statement Comparing Strings Comparing Strings Using Pattern Matching Replacing Part of a String Multiple Replaces Using Arrays And Ranges Use .Value2 Instead Of .Text or .Value

.Text .Value .Value2

Avoid Using Copy And Paste

Before After

Use The Option Explicit Keyword To Catch Undeclared Variables

← Prev
Back
Next →

← Prev
Back
Next →