VBA for Beginners eBook Learn VBA -

Visual Basics for Applications for Beginners Page | 2

Copyright © 2013 Trevor Easton Online PC Learning

Author Trevor Easton All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of Trevor Easton.

For support related to this book, email Online PC Learning Support at [email protected]

The example companies, organizations, products, domain names, email addresses, logos, people, places, and events depicted herein are fictitious. No association with any real company, organization, product, domain name, email address, logo, person, place, or event is intended or should be inferred.

This book expresses the author’s views and opinions. The information contained in this book is provided without any express, statutory, or implied warranties. Trevor Easton nor Online PC Learning will be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.

2|Page



Visual Basics for Applications for Beginners Foreword 10 Why I have written this information

10

How to use this e-book

10

Companion Excel Workbook Resource Resource Workbook Contents

Chapter 1: What can you do with VBA?

11 11

12

OVERVIEW

12

Run a simple test

13

Reduce staff costs

14

The benefits

15

Chapter 2: Understanding the Visual Basic Editor

16

OVERVIEW

16

Basic VBE Components

16

VBE Project Explorer

17

Saving the Workbook

19

The Properties Window

21

Understanding Modules

22

Procedures Window

24

Chapter 3: What is the VBA object based language

27

OVERVIEW

27

Let’s go Camping

27

Understanding Objects

28

Object Properties

29

Is there Method in your Methods

29

What are Events?

30

3|Page



Chapter 4: Recording your first macro

32

OVERVIEW

32

Where Do I Start?

32

What can I record?

33

The Developer Tab

33

Allowing Macros in your Workbooks

34

Record our first macro

35

Run the macro from the VBA editor

37

Test your new skills at recording macros

39

Chapter 5: Working with VBA (learning to speak the language)

41

OVERVIEW

41

Adding a Module

42

Adding a Userform

44

Code Keywords

46

Entering code

47

Relative and absolute references

50

Line-continuous character

51

Stepping through the code

51

Chapter 6: How to use Variables in VBA

57

OVERVIEW

57

Naming a variable

59

Why use variables?

59

Declaring variables

60

What is a data type?

61

What is the scope of a variable?

62

Chapter 7: IF Statement and Decision Modelling

64

OVERVIEW

64

Decision Modelling

65

4|Page


Visual Basics for Applications for Beginners Page | 5 If – Then Constructs (True)

66

Single If-Then

67

Multiple If-Then constructs

67

If–Then End If

68

Multiple If - Then - End If

69

If – Else (True, False)

69

If – Then - ElseIf – Else

71

Nested If - Then - Else

72

If – And –Then

72

If – Or – Then

74

If – Not – Then

74

IIf Function

75

Chapter 8: Select Case Constructs

76

OVERVIEW

76

Benefits of the Select Case function

77

Syntax

78

Select Case - (Single condition)

80

Select Case - (Multiple conditions)

81

Select Case – Else

81

Multiple Conditions (Comma separator)

82

Multiple Conditions Between (To)

83

Combining Multiple Conditions (To + Separator)

83

Using Operators with Select Case

84

Select case and Text Conditions

85

Option Compare Text

85

Combining Numbers and Text Conditions

86

Combining Select Case (Magic combinations)

87

5|Page


Visual Basics for Applications for Beginners Page | 6 Chapter 9: Excel VBA Message Boxes

89

OVERVIEW

89

Let your applications speak for themselves

89

Microsoft Excel’s use for message boxes

90

Simple VBA message box

90

Constants available to the message box function

96

How it all works behind the scenes

97

Multiple lined Message Boxes

98

Message Boxes Examples

99

Chapter 10: Understanding the Input box construct

104

OVERVIEW

104

Why use an Input Box

105

Input Function

107

Input Method

107

Type argument

107

A Simple Input Box

108

VBA Examples

109

Create a User Login

109

Collect employee age

113

BMI Calculator

115

Working with Ranges (Input Method for Objects)

117

Chapter 11: Understanding VBA loops by Examples

119

OVERVIEW

119

Do Loop

121

Do Until Loop

122

Do While Loop

123

For Loop

125

For Each Loop

129

Nesting Loops

131

6|Page


Visual Basics for Applications for Beginners Page | 7 Chapter 12:Basic Error Handling

135

OVERVIEW

135

Why Use Error Handling?

136

How to Configure Error Settings

136

How to Reset Errors

137

Three Types of VBA Errors

138

When to use error handling

140

What is the VBA Debug?

141

Understanding the On Error Statement

142

VBA Resume Statement Explained

148

Chapter 13:Build your First Userform Database

153

What is a Userform?

153

Userform Project

156

Insert a Userform

157

Userform Properties

158

Toolbox

159

Adding Controls

160

Writing the VBA Code

166

Writing our code

166

Set up the Database Worksheet

172

Test the userform with data

172

Setting up The Interface

173

Running the advanced filter

174

Chapter 14:Developing your first Application

176

How do we develop bullet proof Excel Applications?

176

Where do I start?

176

Great Idea – Bad Design

177

I’m going to share with you my recipe for success.

177

In the line of fire

178

7|Page


Visual Basics for Applications for Beginners Page | 8 In Conclusion

184

Appendix - Macros in this eBook

186

Message Box Macros

186

Variable Scope

187

If Function Macros

188

Select Case Macros

193

Message Box Macros

199

Input Box Macros

203

Loops Macros

209

My Tax Receipt Macros

223

8|Page


VBA for Beginners: Features Page | 9

VBA for Beginners: Features

190 Pages: Step by Step Training Written In language the beginner can understand This learning guide has been written for the VBA beginner. I have tried to break all of the learning down to the basic building blocks required to understand Visual Basic for Applications. More than 130 full colour illustrations To add to an easy learning experience I have added over 130 full colour illustrations, many with notes and diagrams to highlight the necessary steps required to learn effectively. Macros After you have read this publication you will be returning to find code to use in your future applications. For this reason I have added all of the key macros at the end of the publication. They are listed under the function they belong too. This will save a lot of time when you return to the publication. Five Projects to complete In harmony with the spirit of Online PC Learning, that project based learning is a fast and effective learning path. I have included projects throughout the eBook. This will give you the chance to practice your skills as you go. 1. User Login 2. My Age Calculator 3. BMI Calculator 4. My Tax Receipts Companion Resource Workbook Along with this eBook you will receive a companion Excel file that is set up to demonstrate many of the chapters within.

9|Page


Foreword Page | 10

Foreword Why I have written this information The chapters in this book and the accompanying files will enable you to quickly grasp the basics of VBA in Microsoft Excel. There is a lot of published information on the web that deals with the learning Visual Basic for applications. Often a newcomer becomes lost with the complexity and randomness of the information provided. Not everybody learns the same way. Some people like learning by rout from books and in many situations that may be necessary. But that’s not me. I am a visual learner and I learn by associating tasks.

We will be learning by examples I have presented the chapters in such a way that each chapter will set the basis for the next chapter so that you can progressively learn the visual basics for Applications. Based on my own learning experience and the questions I asked on Online P See Learning, these chapters should cover all that is necessary to get you up and running and developing some awesome VBA code. There are illustrations throughout the chapters to demonstrate all the key points that are presented.

How to use this e-book Go through the book chapter at a time. If you feel you fully understand all the concepts in each chapter then move to the next chapter. Open the companion Excel workbook resource file that is provided and view and run the code that are provided as examples and test it out in your computer. You will remember concepts more easily if you use them in a file rather than just read how to go about achieving the desired result. Best wishes with your learning endeavours

Trevor Easton 10 | P a g e


Companion Excel Workbook Resource Page | 11

Companion Excel Workbook Resource Along with this eBook you will receive a companion Excel file that is set up to demonstrate many of the chapters within.

Resource Workbook Contents o o o

o o o

Interface If Function Case Function Input box function  My Age Calculator  BMI Calculator Loops Error Handling Userforms  My Tax Receipts

As you can see above there are also 3 completed applications.

11 | P a g e


Page | 12

Chapter 1:

What can you do with VBA?

OVERVIEW    

Automating business processes Obtain accurate data analysis Reducing labor costs Increase job satisfaction Almost anything that you can do manually in Excel you can do with VBA and in a fraction of the time normally taken. When the code is written or recorded it can be executed time and time again in a number of ways. You can grab objects, workbooks, worksheets, ranges charts and shapes and do almost anything with them in a fraction of a second.

VBA is an object based language and VBA Macro or procedure is simply a set of instructions to manipulate objects. In fact you can do more with VBA procedures than you can do manually in many instances. Some tasks can only be accomplished with code. VBA is an object based language and VBA Macro or procedure is simply a set of instructions to manipulate objects.  

Format a worksheet Add data validation 12 | P a g e


Chapter 1:    

What can you do with VBA? Page | 13

Create a chart Activate a pivot table Filter large data ranges with multiple criteria And lots more

Speed up processes Tasks that would normally take a very long time to do can be accomplished in just a few seconds.

Run a simple test For many years now, to demonstrate the power of Excel 2003 I have highlighted all of the cells on a worksheet by clicking in the box diagonally to the left and above cell A1 on a spreadsheet. Then type a number and press Ctrl + Shift + Enter. This would insert the number into the approximately 16 million cells on the worksheet. Execution time varies with computers but the job is done in about 3 to 4 seconds. The point that I would make is that if you were to type a number into 1 cell at a rate of 1 per second and did that 24 hours a day, 7 days of the week, it would take about 180 days to complete the task. We just did that in 3 seconds! From 2007 onwards the columns and rows increased to 1048576 rows and 16384 columns which is equal to 17,179,869,184 cells on each sheet. If you tried to do that, there would not be the available resources necessary to accomplish the task. If you were to highlight just the first 20 columns which is 20,971,520 cells and then add a number the way I have described it would take about 6 seconds for the 20 million cells to be filled. Here is the code to do that. Don’t save your work if run this experiment as the file will be about 55 mb in size.

Sub AddNumber() Columns("A:T")= "8" End Sub

Can VBA save you time? The answer is yes, and lots of it. 13 | P a g e


Chapter 1:


Analyze data Now let’s look at a more practical example. As an example let’s say you have a large data set with 30 columns as headers and 5000 rows deep and you want to filter that data based on some multiple criteria. That is easy enough to do with the advanced filter that Excel offers. The problem is every time you want a result you need to set up the filter to run with the new data and criteria. With a small piece of code, that laborious task is no longer necessary, you just click a button and the filter is automatically set for you. Then add a simple variable and the criteria can be changed depending on the option selected by the user.

Reduce staff costs Repetitive tasks Anything that you do in Excel on a regular basis is a good candidate for a VBA procedure.

Two real examples Example 1 A hospital with 20 wards has staff in each ward working four hours a week to put together a list of all the staff working on each day and each shift. They then go to their roster and copy that information to another spreadsheet. To do this with a VBA procedure takes less than 3 seconds and the same program is used for all 20 groups. Time saved per week is 80 hours at approximately $30 per hour which saves approximately $2400 per week.

Example 2 Every morning an activity report needs to be run and sent to the director. This is a repetitive task that takes one hour. Imagine starting your day with that ahead of you. This process can be automated with VBA instructions and performed with just a couple of clicks.

14 | P a g e


Chapter 1:


The benefits 1. Better job satisfaction 2. Accurate data 3. Labor savings If you are paying wages time is money and anything that can save time and eliminate human error is good business practice.

15 | P a g e


Page | 16

Chapter 2: Understanding the Visual Basic Editor OVERVIEW In this chapter we will take a tour of the Visual Basic Editor. I will run you through the standard setup and some of the key options you will need to understand.              

VBE Project Explorer Changing the project name Protecting the code Saving the Workbook The Properties Window Docking and Undocking Understanding Modules Modules are filing cabinets Naming the Module Procedures Window Intellisense Adding comments to your code VBE Immediate Window Quick test

Basic VBE Components SHORTCUT FOR VBE: Alt+F11 To get started let’s open the Visual Basic Editor. (The Visual basic editor is a program that comes with Microsoft Office)

16 | P a g e


Chapter 2: Understanding the Visual Basic Editor Page | 17 Open the VBE by holding down the Alt+F11key and enable these windows in your editor. Click on the View tab and select the windows that you to appear in the editor. For a basic setup you should have the Project Explorer / Properties Window and the Intermediate Window visible as you work. These options are available from the View tab on the menu bar of the Visual Basic Editor as illustrated below.

VBE Project Explorer SHORTCUT FOR THE PROJECT EXPLORER: Ctrl+R As you look at the Visual Basic Editor you will notice that the name given to each workbook by the Visual Basic Editor is “Project-VBA Project”.

Below the name in the Project Explorer the hierarchy or tree with all of the objects for each project exist. If you cannot see the Project Explorer then click on the View tab at the top of the VBE and click on Project Explorer. When you single click on each object or module you select it. You can then right click to view the shortcut 17 | P a g e


Chapter 2: Understanding the Visual Basic Editor Page | 18 menu and choose from the options. Options include opening the properties or viewing the code or deleting the object. If you double click an object you will be taken to the code for the procedure in the VBA Code Editor.

Changing the project name It is good practice to change the name of the Project to more accurately reflect what that project does. If you have several workbooks open this will help you to clearly identify each project. To change the project name: 1. 2. 3. 4.

Right click on the VBA Project Choose the General tab Add your new name in the box for Project Name Click OK

Protecting the code If you do not want your precious code altered or perhaps you want to keep it secret, then you should protect the VBA part of your application. 18 | P a g e


Chapter 2: Understanding the Visual Basic Editor Page | 19 1. 2. 3. 4. 5. 6.

Right click on the VBA project title Select the VBA Project Properties Tick the box next to Lock project for viewing Add your password and confirm it in the text boxes provided Click Save Close and save your workbook

When you next go to the VBA Editor you will need to supply tour password to open it so do not forget what the password was. Note: Protection in Excel Workbooks is not hard to crack. So in essence you will be protecting your code from the honest and less experienced Excel user.

Saving the Workbook In Office 2007, Microsoft changed its file structure for workbooks from a binary (.xls) to a special (Microsoft) .xml format. That is why the file structure changed from (.xls ) to (.xlsx). The x indicating the xml format. It is necessary to save your file to a .xlsm (Macro enabled) file if you are using 2007 plus versions. You will be prompted (as shown below) with a warning and if you continue without choosing .xlsm all of your code will be deleted from the workbook. 19 | P a g e


Chapter 2: Understanding the Visual Basic Editor Page | 20 DO NOT CLICK “YES” or all will be lost.

Follow the steps listed below and your work will be fine and safely stored. Here are the steps:     

File / Save As Save as type = .xlsm File name = “Add Appropriate” Location = Where you want to store the file Save = This will execute your options

20 | P a g e


Chapter 2: Understanding the Visual Basic Editor Page | 21

The Properties Window SHORTCUT FOR THE PROPERTIES WINDOW: F4 Objects have properties. In fact all objects have properties. What we mean by properties is that the object can have a name or a color or a size and so on. In essence it is a setting for the object. Think of it as descriptive, like an adjective in English. Properties available to an object vary depending on the object selected. You will need to have the properties window readily available. It is possible to float this window if that is preferable to your development style. I personally like to have the properties window somewhere on the screen as I am writing code. It can be floating or docked. This illustration below shows the floating Project Explorer and the Propertied Window. To float them click on the top of the window and drag the window to where you 21 | P a g e


Chapter 2: Understanding the Visual Basic Editor Page | 22 want. Notice here we have Sheet2 selected and the relevant properties are displayed for that object.

Docking and Floating Windows Docking means, locking the window into its home position in the Visual Basic Editor. To undock (float) the window, hold down the left mouse button on the title and drag the window to the location of your choice. The main thing is that you can get to it quickly. To dock the window to the left hand pane, first maximize the VBE editor then drag the window to the left hand side of the screen.

Understanding Modules Modules are the home for procedures. You can add multiple procedures / macros to a single module. Just as you can have multiple rooms in a home.

22 | P a g e


Chapter 2: Understanding the Visual Basic Editor Page | 23 Modules are filing cabinets (Modular filing cabinet) Modules are like filing cabinets for your procedures. A filing cabinet organizes your paperwork. Open a draw and select the tab for “Rates” and you should expect of find all of the rate bills. Our Modules serve the same purpose. When you open the VBE you should quickly be able to locate your specific procedure by the name of the module.

Naming the Module You should name a Module to represent the contents that it contains. This is done in the properties window for that module. Double click on the Module and then change the name in the properties window.

As an example: If you put all of the procedures that filter ranges into a Module, you could call that Module “MyFilters” or something descriptively similar. This is like a tab at the top of the Modular filing cabinet. Don’t put unrelated macros in a named module.

So in summary a Module can contain multiple procedures. It is a good idea to keep them manageable by categorizing the procedures and limiting the number within each Module.

23 | P a g e


Chapter 2: Understanding the Visual Basic Editor Page | 24

Procedures Window The procedures window or code editor is where you add, view and edit the VBA code. When you record a macro, the code will appear in this window. Here is an example of a simple procedure to toggle the sheet tabs visibly. Sub is short for “Sub Procedure”.

IntelliSense A great feature that is often overlooked is IntelliSense. IntelliSense appears when you type a period (Dot) to separate the levels of the object members in that hierarchy. This will give you a list of members associated with the object. Double click on the list item to insert it into the code for that procedure. This is a list of methods and properties available to the object. If you cannot find it in the list then it is not available to the object.

24 | P a g e


Chapter 2: Understanding the Visual Basic Editor Page | 25 If this feature does not appear to be working for you then go to Tools / Options / Auto List Members to activate IntelliSense.

Adding comments to your code Macros can be very long and can be associated with other procedures. It is a good idea to add comment to your code so that others who view and edit it can understand what is happening. For me, it is so I can remember what the code is doing each time I visit it. It is very good practice to add comments throughout your procedures and name them intuitively. There are two ways to do this. The most common is to use an apostrophe (‘) in front of your comment. For example: ‘here is my comment The second method and now almost extinct is the (Rem) statement. Put the statement in front of you comment e.g. Rem here is my comment When you do this you will notice that that line of code will change color to let you know it is not part of the executable code.

VBE Immediate Window SHORTCUT FOR THE IMMEDIATE WINDOW: Ctrl+G This window is very useful in testing small parts of the code as you are developing the procedure. You will be able to use this window with single statements. Simply type in your statement and press enter. As the name suggests, it will execute the code immediately.

25 | P a g e


Chapter 2: Understanding the Visual Basic Editor Page | 26 Quick Test Here is an example. In cell A5 type the value 11, then in the Immediate window add a question mark followed by Range("A5")>10. The (?) will print the answer in the Immediate window. Put the curser inside the code and press enter and you will see True appear in the next line in the immediate window. Because the number 11 is greater than the number 10. ? Range("A5")>10

26 | P a g e


Page | 27

Chapter 3:

What is the VBA object based language

OVERVIEW    

Understanding Objects Object Properties Is there method in your Methods What are Events I mentioned previously that VBA is an object based language. VBA manipulates objects. The key to understanding VBA in the Excel application is to understand the relationship between:

1. 2. 3. 4.

Objects Properties Methods Events

Let’s go Camping

Here is an illustration that may help. I personally like camping. In fact as I am writing this I am sitting under the awning of my camper trailer at Burrum Heads on the Frazer coast of Queensland, Australia. A truly magnificent holiday destination. 27 | P a g e


Chapter 3:

What is the VBA object based language Page | 28 To understand the object model take the illustration of a caravan or trailer park.

Understanding Objects The caravan park has many caravans, some are coming and others are going each day. Think of the caravan park as a container or Object and each caravan in the park is an object associated with that park. The caravan park is an object with a collection of member objects (caravans) inside its area. We can see a hierarchy here because the caravan park is the main object that contains member objects (caravans). Now let us apply that to understanding VBA a little better. The Excel Application is an object and you can add workbooks and then you can add worksheets to each workbook. The Excel Application can have hundreds of workbooks or objects each containing different collections of worksheets (objects). Yes, you see it is all about objects. Adding, moving and manipulating objects. Now back to our illustration. Each caravan has rooms, a bed, a kitchen, two wheels, a roof and walls and tables. All of these are objects collected under the caravan object. OK, now let’s take the table and fold it up and put it out of sight under the bed. It is still there but we cannot see. However we can pull it out and use it anytime we want. The Excel Workbook has Worksheets and these Worksheets have Ranges, Cells, Charts and even Pivot Charts. All of these are objects under the Workbook collection. Everything that you can see is an object. Of course some objects can be hidden out of site but they are still member objects of that collection and can be unhidden and referenced as we need them.

Look at this piece of code that selects cell A1 in Sheet1 Look closely, can you see the object hierarchy. Application.Workbooks(“Book1.xlsm”).Worksheets(“Sheet1”).Range(“A1”) Let’s break it down to show the object hierarchy 1. Application 1.1. Workbooks(“Book1.xlsm”) 1.1.1. Worksheets(“Sheet1”) 1.1.1.1. Range(“A1”)

28 | P a g e


Chapter 3:

What is the VBA object based language Page | 29

Object Properties The caravans have different properties. Some are longer than others some are different colors. In fact all of these physical objects have properties. Look inside the caravan at the objects, the bed has different properties to the table and so on. I think we can see this point very clearly. Properties are descriptive as adjectives are in the English language. A green caravan, a pink door, a silver sink they describe the property. You can change the property of the pink door to white by painting it. Now in Excel, every object has properties. You just don’t notice this because Microsoft sets most of these properties by default. A worksheet has a different name and set of properties. These are the properties that you will be changing with VBA or in the properties window. A chart has different properties to a cell. The beautiful thing is that you can change these properties with code, if we understand them. Look at the Worksheet properties below. Can you see how easy it is to change the properties of an object in the properties window? You can also refer to and change properties with VBA to achieve remarkable effects. It’s like magic.

Is there Method in your Methods The weather is beautiful, so we decide that we want to move our caravan (object) closer to the water to a different site still within the park (object). We need to take action. How are 29 | P a g e


Chapter 3:

What is the VBA object based language Page | 30 we going to do this? Well first we need to pull down the annex and unplug the power, move the car and connect it to the trailer. Then we will tow it to the new site. These are the methods we will use to accomplish the task. These are the methods available to the object for moving the object. In Excel, Methods are also actions. Let’s say we want to move a range (object) from one sheet to another sheet. What methods or actions are available to us? We might select the data and copy it, then select the destination and paste the data, then clear the contents from the initial range. These are methods to manipulate the objects involved in the move.

Visual Basic for Applications gives us a hand. How? With IntelliSense! Can I ask you to try this little test? Open the VBE and type Sub Test and push enter. End sub will automatically be added. Now add the code from the illustration below Range(“A1”). between the lines. Notice when you type the period or Dot (member separator) the IntelliSense appears. Now type (s) and all of the methods and properties appear. We can choose the select method. Selecting is our action or method.

What are Events? What are events? Have you heard the expression, “I had an event filled day”. Moving our caravan is just full of events. How so? Well! Opening the caravan door is an event, pulling down the annex is an event. Connecting the car to the caravan is an event. With Excel, opening the workbook, saving the workbook, closing the workbook, making a change to a cell, activating a sheet are all events. We can use these events to perform certain tasks. For example when a worksheet activates we can run some code to take the 30 | P a g e


Chapter 3:

What is the VBA object based language Page | 31 user to a specific area or we could change the properties of the sheet. We can run procedures from these events. In the illustration below of a VBA procedure, I am using the Workbook open event to run a series of actions. When the workbook opens we can set things up for the user.

To find what events are available to the sheet object, first select the object from the drop down list on the left and then the event from the dropdown list on the right.

Can you understand the difference between? 1.

Objects

2.

Properties

3.

Methods

4.

Events

Understanding these basics will help with our programming skills. We should be starting to see what the VBA code is doing and why it is doing it. 31 | P a g e


Page | 32

Chapter 4:

Recording your first macro

OVERVIEW When it comes to VBA code many people get scared. They labour under the misapprehension that it’s too difficult to get started.

Me! Writing VBA Code? Never!!!!!! The truth is, it is easier than you think. For those who would like to venture into Excel VBA coding the big question arises.

Where Do I Start? Imagine this for a minute. What if someone were to write the code for you? Yes that’s right; write your code for you!! Microsoft Excel with its macro recorder does just that. When you turn it on and then perform an action in Microsoft Excel it records that action and writes those actions in code. Let me tell you what I think about the Macro Recorder. FANTASTIC! PHENOMENAL! AWESOME! INCREDIBLE! WONDERFUL! 32 | P a g e


Chapter 4:

Recording your first macro Page | 33 As you might notice I am quite impressed. It is true that without macros excel can do phenomenal things. It truly is an awesome program. But if you add the power of Visual Basic for Applications, it literally takes off into outer space. Being an objectbased language, Excel VBA will allow you to accomplish almost any task. YES ALMOST ANY TASK! I’m going to introduce you to the macro recorder. I will show you a few of the basics that are involved and get you started in recording your first macros. Many of us would have a video camera at home. When you turn on that video camera everything in front of the lens is captured and you can play it back over and over again. The macro recorder does exactly that. It will record your actions and then you can playback that action or group of actions over and over again.

What can I record? There are too many things for me to list here, but in essence it is: 1. Anything that you do repetitively 2. Work procedures that take a long time to accomplish 3. Very difficult tasks that you will use more than once You can do all those things by recording the actions and assigning the macro to a button. With the click of a button, hey presto, it’s done. In this article I’m going to show you how to hide and unhide all of the sheet tabs. I chose this task because it is something I use on a daily basis. Microsoft Excel 2010 and 2013 are the versions referenced in this chapter. The earlier versions will do exactly the same however the location of some of the controls will vary.

The Developer Tab To record macros it is great to have the developer tab visible. To add the developer tab in Excel 2010 and 2013 follow these steps: 1. Right click on the Ribbon and choose Customize Ribbon 2. In the list on the right put a tick in the box beside Developer 33 | P a g e


Chapter 4:

Recording your first macro Page | 34

3. Click OK, and the Options window will close

Allowing Macros in your Workbooks

1. On the Ribbon in the section Code choose Macro Security 2. In the Trust Centre dialogue box click Macro Settings in the left-hand panel 3. Under Macro Settings click the option button Enable all macros Rehearse your script - the dry run Because a macro recorder is going to record every single thing you do it is a good idea that we rehearse our actions beforehand. With more complex recorded macros you may even need to write them down step-by-step so that you can record succinct and clear code. We are going to record a macro to hide the sheet tabs. 1. Click File and Choose Options 2. Click Advanced locate Display options for this workbook 3. Remove the tick from show sheet tabs Rehearse these actions Start Recording 1. 2. 3. 4.

Record our first macro Run the Recorded Macro from the ribbon Assign the Recorded Macro to a button Dissect your code

34 | P a g e


Chapter 4:


Lights… Camera… Action... Start Recording There are 3 ways to start recording From the status bar

From the view tab

From the developer tab

Record our first macro Click Record Macro on one of the above mentioned methods you will now be asked to enter some basic information.

35 | P a g e


Chapter 4:


1. Add an appropriate name. For example because we are hiding the sheet tab with this macro we could name it HideTabs or Hide_Tabs. 2. Spacers are not allowed. 3. Create a shortcut. Click inside the box next to Ctrl+ and push down the shift key and add a letter. If we choose H then the shortcut to run the macro would be Ctrl+Shift+H. 4. Choose the location where you want to store the macro. We will select This Workbook. 5. Add a description. “ My first fantastic macro” 6. Click OK to begin recording 7. Run through the steps you rehearsed 8. Stop the recording from one of these 3 locations 1. From the View tab 2. From the Developer tab 3. From the Status bar

Run the Recorded Macro from the ribbon

36 | P a g e


Chapter 4:

Recording your first macro Page | 37 Make the tabs visible again from the File / Options / Advanced There are multiple ways to run the macro that you have just recorded

   

From the view tab On the developer tab Push Ctrl+F8 Hit the keyboard shortcut Ctrl+Shift+H

All of these options will open this dialog box

Select the macro and push Run

Run the macro from the VBA editor Push the Edit button in the dialog box above or alternatively hold down Alt+F11 Double click on Module1or single click and push F7 in the left hand pane [Project Explorer] 37 | P a g e


Chapter 4:


Click inside the code and then Click the green run button

Alternatively hit the F5 key Assign the Recorded Macro to a button

Insert a shape. Insert tab / Shape Right click on the shape and choose assign macro. Select the macro and then click OK

38 | P a g e


Chapter 4:


Saving your work

The workbook must be saved with a different file extension. .xlsm File / Save as /Add a name and choose Excel Macro Enabled Workbook(*.xlsm)

Experiment with your code Hold down the Alt+F11 key on your keypad to open the Visual Basic editor. Double-click on the module that contains your procedures. Read through how the code is working ActiveWindow.DisplayWorkbookTabs = False Change the False to True and run the code to see what happens. If you change the code as shown below you turn it into a toggle button Click it once and if the tabs are hidden it will unhide them click it again and it will hide them. I use this little piece of code all the time. It cannot be recorded but I am sure that you can see that we have used recorded code to create it. ActiveWindow.DisplayWorkbookTabs = Not ActiveWindow.DisplayWorkbookTabs

Start producing your awesome Visual Basic for Application code It is time to start recording all of your favorite tasks. If you have found this information helpful let me know and I will add some further steps into the basics of coding.

Test your new skills at recording macros Your assignment 1 Record macro to hide the worksheet tabs 39 | P a g e


Chapter 4:

Recording your first macro Page | 40 Record another macro to unhide the worksheet tabs Assign these two macros to buttons on your worksheet Your assignment 2 Record a macro to protect your worksheet Record a macro to unprotect your worksheet Assign these two macros to buttons on your worksheet

40 | P a g e


Page | 41

Chapter 5: language)

Working with VBA (learning to speak the

OVERVIEW        

Adding a Module Removing a Module Adding a Userform Code Keywords Entering code Relative and absolute references Line-continuous character Stepping through the code In this chapter we are going to look at many of the basic processes in working with objects and their code. When you record a macro, a module is automatically added for you and the code is inserted. What if we want to add our own module and VBA code? We will also have a look at the way the code is structured. Finally I will show you how to step through the code to check each section for errors.

41 | P a g e


Chapter 5:

Working with VBA (learning to speak the language) Page | 42

Adding a Module Method 1 1. 2. 3. 4.

To add a module Right click any object Choose Insert Select module

Don’t forget to name your module appropriately as described previously.

Method 2 1. 2.

Select Insert from the VBE menu Click Module

42 | P a g e


Chapter 5:


Removing a Module To remove a module from your application: Follow these steps. 1. Right click on the module you want to remove 2. Choose Remove Module from the quick pick list 3. Choose to Export before deleting Note: If you delete a module, all of the code in that module will be lost. So it is worthwhile to consider exporting the module to a location of your choice in case you need to reinstate it in the future.

43 | P a g e


Chapter 5:


Adding a Userform We will look at Userforms a little later in another chapter. For now let’s see how to add a Userform.

Method 1 1. 2. 3. 4.

To add a userform Right click any object Choose Insert Select Userform

Don’t forget to name your module appropriately as described previously.

44 | P a g e


Chapter 5:


Method 2 1. Select Insert from the VBE menu 2. Click Userform

45 | P a g e


Chapter 5:


Removing a Userform To remove a userform from your application: Follow these steps. 1. Right click on the userform you want to remove 2. Choose Remove userform from the quick pick list 3. Choose to Export before deleting Note: If you delete a userform all of the code in that module will be lost. So it is worthwhile to consider exporting the userform to a location of your choice in case you need to reinstate it in the future.

Code Keywords Special terms are designated as keywords and are displayed in blue. For example the word Sub is a keyword that precedes the name of the macro. Notice the end of the macro is always concluded with the keywords End Sub. Notice in the code below that the Boolean statement True is a keyword. An apostrophe preceding code indicates a comment that is descriptive and will appear in green. It becomes a non-executable part of your code. It is good practice to get into the habit of adding comments to your code.

Copy the code below and add it to a module to get familiar with the way comments and keywords are recognized in the visual basic editor. 46 | P a g e


Chapter 5:

Working with VBA (learning to speak the language) Page | 47 Sub Test() ' Test Macro ' Test for keywords ' Keyboard Shortcut: Ctrl+Shift+A ActiveCell.FormulaR1C1 = "1" Selection.AutoFill Destination:=Range("A1:A10"), Type:=xlFillSeries Range("A1:A10").Select Selection.Font.Bold = True End Sub

Entering code There are 3 ways to enter code into the procedure window of the editor:   

Type it in Record a macro Paste the code from another source Let’s look at each one individually.

Typing the code (Writing your own macros) When you want to write your own code the first step to take is to add a module: 1. Select Insert then select Module to insert a new module a. Name the module from the properties window 2. Now type the keyword Sub followed by an appropriate name for your macro. a. E.g. Sub My_First_Macro b. Notice the underscores to join multiple words into one word. 3. Now press Enter a. This is what will now appear: Sub My_First_Macro End Sub 4. Between the lines type a. Range("A1").Value = "This is my first written macro" b. Your code block will now look like this: Sub My_First_Macro Range("A1").Value = "This is my first written macro" End Sub

47 | P a g e


Chapter 5:


c. With your curser inside the code push the F5 key and you will see the text "This is my first written macro" inserted into cell A1 of the active worksheet. 5. Congratulations you have written and executed your first macro. It is a good idea to make use of IntelliSense when writing your code. It will ensure that your code is accurate. Tip: Type your code in lower case. If your syntax is correct the Visual Basic Editor will change the first letter of the keywords for the objects properties and methods to upper case. This will indicate that you do not have any syntax errors in that section of the code.

Recording the code When you record a macro, the macro recorder will add a lot more code than you actually need. Do not let this be of a concern, the code will work fine. It is worthwhile shortening the code where possible to remove the parts that are not necessary. Let’s perform a little test: 1. 2. 3. 4. 5.

Select any cell on the worksheet Click on the developer tab and then Record Macro On the worksheet Home tab choose Font / Borders Add a medium border Stop recording the macro

48 | P a g e


Chapter 5:


The code block below is what will be recorded. That is a lot of code for just a simple task. Take heart, it is not all necessary. This code can be shortened.

Here is the abreviated code.This is all that is necessary to accomplish the task.

49 | P a g e


Chapter 5:


Paste code into the project There are many times when you will need to paste from a website or other source. The basic steps are listed below. Note: If you are pasting from a word document then caution needs to be exercised with fancy quotes and dashes. If the word application has fancy quotes enabled, and it probably will, then follow this simple step. Fancy quotes will need to be replaced with the straight apostrophe. Replace

(‘) with (') in the code editor.

1.

Copy the code form the website

2.

Open the workbook where you want to insert the code

3.

Press Alt key+ F11 key, to open the Visual Basic Editor

4.

Choose Insert | Module

5.

You should see that the cursor is flashing

6.

Select Edit and then select Paste

Relative and absolute references

50 | P a g e


Chapter 5:


Line-continuous character Type a space, then type an underscore and then press ENTER.

Stepping through the code Stepping through the code is very helpful in finding errors. This feature will find and highlight the error for you. Follow these steps: 1. Open a fresh workbook and add the text a. “Color” into cell C3 b. “Blue” into cell C4 c. “Green” into cell C5 d. “Yellow” into cell C6 e. “Red into cell C7

51 | P a g e


Chapter 5:


2. Add a new module to the Visual Basic Editor and add the code that you see below. a. Sub MyColors() ' MyColors Macro Range("D4").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.399975585192419 .PatternTintAndShade = 0 End With Range("D5").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent3 .TintAndShade = -0.249977111117893 .PatternTintAndShade = 0 End With Range("D6").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With Range("D7").Select With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic 52 | P a g e


Chapter 5:


.Color = 255 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub 3. Close the Visual Basic Editor by Alt+F11 4. In the ribbon select Developer / Macro 5. The Macro dialog box will appear

6. In the dialog box click on Step Into 7. Push the F8 key and watch as each line of code is executed. You will need to push the key for each line of code.

53 | P a g e


Chapter 5:


8. The colors will be added to the cells adjacent to the colors.

Finding errors by stepping through the code 1. Now add a deliberate error to the code. In this instance I have retyped the word Selection incorrectly. We will step through the code and allow the editor to find the error for us. 2. Reset the colors to on fill for the range D4:D7 3. Open the macro dialog box again and select Step Into 4. Push the F8 key repeatedly to move you down the code until you come to the error in the code.

54 | P a g e


Chapter 5:


5. The Microsoft Visual Basic Debugger will appear 6. Click Debug 7. Now the line of code with the error will be highlighted in yellow

8. Retype the wrongly typed word “Selection” 9. Reset the Visual Basic Editor by selecting Run /Reset

55 | P a g e


Chapter 5:


10. The error has been found and corrected.

56 | P a g e


Page | 57

Chapter 6:

How to use Variables in VBA

OVERVIEW       

What are variables? Naming a variable Why use variables? Declaring variables What is a data type? What is the scope of a variable? What are variables?

A variable is a storage container that holds changing information. It is information stored in temporary memory on your computer. Perhaps you could think of it as a container that holds information that can be used over and over without adding the information each time. A variable is the access to the information. To illustrate what a variable is, imagine a storage container and the key to unlock its contents. A variable consists of 2 parts: 1. Name / (MyVar) 2. Data / (Range(“A1”) When we access the variable name in code it opens the storage container for us and connects us to its contents. The variable name which is often referenced many times in code, only needs to be stated to open the data storage and gain access to the data. It is like turning the key to unlock the container, without having to restate the contents of the container each time it is used.

57 | P a g e


Chapter 6:

How to use Variables in VBA Page | 58

As a simple example, look at the variable below called “MyVar” that references “Range(“A1)” of the active worksheet. In the procedure where this variable resides, we no longer need to add Range(“A1”) to the code. We would simply type “MyVar” instead of the range reference when we run the procedure. It is there, ready to be used as many times as we want during the life of that procedure. It would be written this way in our procedure. Set MyVar = Range(“A1”) Note that in this variable, because we are referring to an object we need to add Set before the variable to tell VBA we are referring to an object. If you were referring to a number, we would not need to add the Set parameter. A variable for the number 100 would read as below: MyVar = 100 Open the Visual Basic Editor, insert a module and copy and paste the code that you see below. On the sheet that is active, add a value to cell A1. Return to the Visual Basic Editor and put the curser in the code and press F5 or click the Run button in the menu. You will notice that the value that you added to cell A1 will appear in the message box. Sub Macro1 Set MyVar = Range(“A1”) MsgBox MyVar End Sub Now change the value in cell A1 and run the procedure again. The message box value will change also. Now let me demonstrate one of the powerful features of variables.

58 | P a g e


Chapter 6:

How to use Variables in VBA Page | 59 If you were to use the reference to cell A1 fifty times in a lengthy procedure and you wanted to change that reference to cell A2, you would need to locate all fifty occurrences and change them individually. As you can see, if you were to assign a variable to the range reference, all you would need to do is make the change to the variable and all the references would be accurately updated. It is fast and eliminates human error in lengthy code. There are also other advantages that we will talk about later in this chapter.

Naming a variable I used the name MyVar, in the previous demonstration; however you can use any name you like. You should name the variable to describe the action it is associated with. If for example you were using a variable to establish a discount for a product you sell, then you might call the variable SaleDisc. This will make understanding what that variable is referring to and make editing the procedure much easier. Note: There are a couple of caveats with regard to the naming of variables. 

  

A variable must start with a letter and it can be a single letter. For example a nondescriptive single letter such as. X (single letters are not descriptive and are considered by many to be bad coding practice if used as a variable) has little meaning. Reading a variable named this way can be very confusing. It is best to use abbreviated and descriptive terms. The name cannot be longer than 250 characters It cannot be an Excel keyword. As an example you cannot call a variable Sub or Sheet as these are reserved by Excel. It must be a single word or multiple words joined by an underscore to form a continuous character string. For example, “My_Var”. After a while you will develop your own naming conventions that if used consistently will help you to understand what the variable stands for.

Why use variables? I have already demonstrated some of the advantages of using variables. To be succinct here are the four main reasons why you should develop a habit of using variables in the code that you write. 1. Procedures will execute more efficiently as the reference is held in memory at the start and for the duration of the scope of the variable. 59 | P a g e


Chapter 6:


2. The code will be less prone to errors. 3. It is considered good coding practice to use variables because users can more readily decipher your code. 4. Editing the code is much easier, faster and more accurate.

Declaring variables What does it mean to declare a variable? In simple terms it means to tell VBA how much memory will be needed to store the data for the variable. To explain a little further, there are different types of data as you can see from the list below. Each type will require a different amount of memory to store or hold that data. Let us refer to our illustration of a storage container again. Not all storage containers hold the same amount. You choose the size of the container based on the amount that needs to be stored. You would not use a forty litre container to hold one small screw. That would be a waste of storage space that could be better utilized elsewhere. The same applies to variables. We do not waste memory. The right data type saves memory space and improves efficiency. What happens if you do not declare a variable? If you do not tell VBA the data type it will automatically assign a Variant type to the data. The problem with this is that the Variant type requires 16 bytes of memory, which could impact negatively on the performance of our application in large procedures. This is not going to affect small procedures. So the point is, there is no law here, but it is good practice to develop a habit of declaring variables. The Variant data type is very versatile as it can hold numeric, string and dates, and will work with empty or null values. So if you do nothing, VBA will automatically cover the data type by assigning a Variant data type. If you just type Dim MyVar (data type omitted) or leave out the Dim statement altogether VBA will automatically assign the Variant data type to the variable. Common variable data types Here is a list of the most common data types and storage sizes: Data type

Storage size Allowable Range

Boolean

2 bytes

60 | P a g e


True or False

Chapter 6:

How to use Variables in VBA Page | 61 Integer

2 bytes

-32,768 to 32,767

Long

4 bytes

-2,147,483,648 to 2,147,483,647

Double

8 bytes -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values

Currency

8 bytes

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

Date

8 bytes

January 1, 100 to December 31, 9999

String

10 bytes + string length

What is a data type?

The list above is largely self-explanatory, however here is a little more information that you may find helpful that clearly identifies when to use each data type. Boolean:

This is True (-1) or a False (0) so there are only two possible outcomes. Only 2 bytes of memory are needed

Integer:

Whole numbers between -32,768 to 32,767

Long:

Number values from -2,147,483,648 to 2,147,483,647 and the memory is 4 bytes.

Double:

Numbers with decimals and holds double precision data for both positive and negative numbers. This type requires 8 bytes of data.

Currency:

Money and fixed point calculations

61 | P a g e


Chapter 6:


String:

Characters that are not numbers. Text is most commonly declared as a string. It can be a continuous sequence of numbers or spaces. 10 bytes of memory is required plus the length of the string.

Date:

Used to store dates and times. Dates and times are numbers. 8 bytes of memory is needed.

What is the scope of a variable? I previously mentioned the word scope when we discussed why we should use variables. What is scope? There are three types of scope available for a variable. What does scope mean when referring to variables? Scope means the part of the VBA project that the variable is accessible to. To express this in simpler terms, it is where the variable is active in memory or “alive”. Let’s run through the scoping levels that are available to a variable in VBA. 1. Procedure level scope Scope = Available only to its procedure. Procedure level scope means it appears between the start and the end of the procedure after the Sub keyword, as shown below. This variable will not be seen by any other macros. The scope is for the procedure only.This is the level that is used the most. Sub Macro1 Dim MyVar as Range Set MyVar = Range(“A1”) MsgBox MyVar End Sub It is local to this macro or procedure only. The variable is released from memory after the procedure is executed. 2. Private level scope Scope = Available only to its module. Private level declarations are accessible to the whole module and are placed above the start of the first procedure in the module. It will be held in memory for the whole time the 62 | P a g e


Chapter 6:

How to use Variables in VBA Page | 63 module is active. You can use either the Private of the Dim statement to achieve this as shown in the two examples below. Dim MyVar as Range Sub Macro1 Set MyVar = Range(“A1”) End Sub Private MyVar as Range Sub Macro1 Set MyVar = Range(“A1”) MsgBox MyVar End Sub It is active in memory when any macro in the module is executed.

3. Public level scope Scope = Available to the whole project. If you put the Public statement in front of your variable, your variable will be available to all macros in all of the modules of the project. Public MyVar as Range Sub Macro1 Set MyVar = Range(“A1”) MsgBox MyVar End Sub This is project level scope. Lifetime of the variable When you run a macro, all of the variables that have scope are held in memory and are considered to have “life”. We can thus refer to the lifetime of a variable which will vary depending on its scope. When a variable loses its scope it no longer has value and is released from memory and its lifetime ends until next time the scope is active. You can also change its value during its lifetime.

63 | P a g e


Page | 64

Chapter 7:

IF Statement and Decision Modelling

OVERVIEW We will be looking at the basic structure for IF function as it is used in VBA. This magical function has many variations that you can use to create decision modeling within your Excel applications. We will discuss all the information you will need to use the VBA If function effectively. Test code is provided for the seven common uses covered in this chapter.       

If – Then If - Else If – Then - ElseIf – Else If – And -Then If – Or - Then If – Not - Then IIf Function

Open the resource file that came with this eBook and navigate to the If Function worksheet. This will enable you to test each of these examples.

64 | P a g e


Chapter 7:

IF Statement and Decision Modelling Page | 65

Decision Modelling You may remember this scene from Alice in Wonderland. This is my granddaughter’s favourite film. It can help us to focus on the heart of decision modelling.

“Would you tell me which way I ought to go from here?” asked Alice. “That depends a good deal on where you want to get,” said the Cat. “I really don’t care where” replied Alice. “Then it doesn’t much matter which way you go,” said the Cat.

Which way do I go? We need to help the user of our applications to decide the right way to proceed. It does matter! We do not want Alice in Wonderland applications.

If like Alice it does not matter to you then you too are in for a wild ride. Decision modelling is all about making the right decision or choosing the right road to take with your VBA Application. 65 | P a g e


Chapter 7:


In fact it can mean the difference between your application being very successful or an abysmal failure. Why not consider using the If-Then construct as an effective way to made decisions with the VBA code? Why is this important? It is because good decisions are essential for the development of successful applications.

If – Then Constructs (True) You may be familiar with the If function used in worksheet formulas. In the worksheet formula the If function returns either True or False and then allows us to carry out an action based on the result. The If Function in VBA is similar. It can return True and if the condition is true the instructions are executed. Syntax for the function If - Then: If (condition to evaluate) Is True Then (Do action) It is possible to add False by using the Else statement. The Else clause is an optional clause that we have available in our toolbox of tricks. 66 | P a g e


Chapter 7:

IF Statement and Decision Modelling Page | 67 Syntax for the function If – Then - Else: If (condition to evaluate) Is True Then (Do action) Else (Do another action). When a condition is met it can execute our instructions and if the condition is not met it will execute a different set of instructions. The example I have chosen changes the value of a cell depending on the value of another cell. This is something you will need to do all the time in your VBA applications.

Single If-Then In the VBA example below, if the value of Rng1 is greater than 50 then we’re going to add 50 to Rng2 to plus the value of Rng1. If Rng1.Value > 50 Then Rng2.Value = 50 + Rng1.Value Here is the code that you can copy to a module in your Excel application for testing. 1. Press Alt+F11 to open the VBA editor and from the menu choose Insert / Module, then copy and paste the code below. 2. Add a value greater than 50 to sell A1 of the active sheet in your workbook. 3. Run the code by putting your curser in the code block and pressing F5. 4. The condition will be met (True) then our new value will be added to Rng2. Note: I have assigned variables to our ranges so that the code will be easier to read I have also dimensioned or declared them with the Dim statement. This is what is called a single line If -Then construct or a line continuous construction because it presents the complete If Then argument on one line. Sub If_Range_Single() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value > 50 Then Rng2.Value = 50 + Rng1.Value End Sub

Multiple If-Then constructs When multiple arguments need to be met, the statement is repeated. Simply repeat the single line If statement with different parameters and values. Each line of code is executed in the example below and if the condition is met (True) the values will change accordingly. 67 | P a g e


Chapter 7:

IF Statement and Decision Modelling Page | 68 Sub If_Range_Multiple() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value > "" Then Rng2.Value = 20 + Rng1.Value If Rng1.Value < 50 Then Rng2.Value = 50 + Rng1.Value If Rng1.Value >= 50 Then Rng2.Value = 70 + Rng1.Value End Sub Disadvantages: 



A disadvantage with the single line If-Then statement is that it can be hard to read and to decipher if edits are needed. A better approach is to use multiple lines by adding the keywords End If. This indicates to VBA that the If statement has ended. Another disadvantage that should be mentioned is that every line is executed to achieve just one result. It would be far better coding to only execute the line that evaluates to True. I will show you how to do this a little later in this article.

If–Then End If To have the If function on two lines we need to tell VBA where the function ends. To do this we use the keywords End If. Here is our one line example expressed in two lines with the End If keywords at the end of the If statement. Sub If_EndIf() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value < 50 Then Rng2.Value = 50 + Rng1.Value End If End Sub

68 | P a g e


Chapter 7:


Multiple If - Then - End If You can run multiple If - Then - End If constructs as shown below. It may be a little easier to read and understand but it is still inefficient because we need to run all of the If statements to get just one result. Sub If_EndIf_Multiple() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") ‘Condition 1 If Rng1.Value <= 20 Then Rng2.Value = 20 + Rng1.Value End If ‘Condition 2 If Rng1.Value >20 Then Rng2.Value = 50 + Rng1.Value End If ‘Condition 3 If Rng1.Value >= 50 Then Rng2.Value = 70 + Rng1.Value End If End Sub

If – Else (True, False) All of the statements above only work if the condition is met (True). It is possible to make this far more versatile by adding a False condition to our statement. To do this we use the Else syntax. The diagram below illustrates the flow of the syntax. If the condition is met (True) then the true part of the code is executed. If the condition is not met (False) the Else statement takes us to the false part of the code which is then executed. This means that the code is only executed when either true or false occurs. This is more efficient.

69 | P a g e


Chapter 7:


This illustration shows the basic movement of the If – Then -Else function construct used in VBA coding.

Input box example If - Then For the sake of variety we will change our If statement to include an InputBox variable that will allow us to enter a value (Amount). In the code that you see below if the amount that is entered is equal to or greater than 50 then a message box will appear and show the amount that we entered into the InputBox. If the amount that we enter into the InputBox is less than 50 then no message box will appear. This code is the same as our previous two line If statement. Sub If_Input() Dim Amount As Variant Amount = InputBox("Enter an Amount:") 70 | P a g e


Chapter 7:

IF Statement and Decision Modelling Page | 71 If Amount >= 50 Then MsgBox "Amount: " & Amount End If End Sub

Input box example If – Then - Else Now let us add the Else statement to allow us to run code if the True statement is not met, in other words if it is False. So instead of running two If statements that both need to be executed we run just the one. The action occurs only when the condition is met (True, False). This is a more efficient code. Sub If_Input_Else() Dim Amount As Variant Amount = InputBox("Enter an Amount:") If Amount >= 50 Then MsgBox "Amount: " & Amount Else MsgBox "The Amount is less than 50" End If End Sub

If – Then - ElseIf – Else But what do we do if we need to run more than two conditions and still want our code to be compiled effectively. To do this we will add the ElseIf statement to our code. You will be able to add as many ElseIf statements as you need to into this block of code. Here I have only used one ElseIf statement, however you could use 10 or more. The beautiful thing about this is that the code will run very efficiently and executed only when a condition is met. Note: For more than 3 conditions the Select Case Function will offer greater flexibility. Sub If_Range_ElseIf() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value < 20 Then Rng2.Value = 20 + Rng1.Value ElseIf Rng1.Value < 50 Then 71 | P a g e


Chapter 7:

IF Statement and Decision Modelling Page | 72 Rng2.Value = 50 + Rng1.Value Else Rng2.Value = 70 + Rng1.Value End If End Sub

Nested If - Then - Else Another way to run this code effectively and is to nest multiple If statements inside of the Else statement. Some may find this easier. This construct is not often used. Sub If_Nested_Multiple() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value < 20 Then Rng2.Value = 20 + Rng1.Value Else If Rng1.Value < 50 Then Rng2.Value = 50 + Rng1.Value Else If Rng1.Value >= 50 Then Rng2.Value = 70 + Rng1.Value End If End If End If End Sub

If – And –Then There are two other common operators thatare reserved as keywords in VBA that work hand in hand with the If fuzznction. They are the And / Or operators. These two operators will allow you to reduce your code even further and add greater flexibility. These operators should have been used in some of the previous examples. First let’s have a look at the And operator.

72 | P a g e


Chapter 7:

IF Statement and Decision Modelling Page | 73 And means by derivation: To perform a joining between two conditions, a conjunction. To concatenate two values so both are required to perform the operation. By using this operator both conditions need to be met as (True and True) for our action to be executed. As we look at the code below we can see that the value of Rng1 needs to be greater than 50 And less than 100. When the both of those conditions are met then the code is executed to achieve the desired result. Note: You can add as many And operators as you like. In the two examples below I have used only one; however you could use multiples in order to achieve filtered results. Bear in mind that the code would be very long and perhaps difficult to understand or decipher. Focus on this part of the procedure. If Rng1.Value > 50 And Rng1.Value<100 Below is the test code that you can add to a VBA module. Add various values to Rng1 and execute the code by putting the cursor inside of it and pressing the F5 key.

Example 1 Sub If_And_Single() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value > 50 And Rng1.Value < 100 Then Rng2.Value = 50 + Rng1.Value End Sub

Example 2 Here is another example for you to test with the InputBox. Less than 25 will receive no discount, between 25 and 50 will receive a discount of 10% and over 50 will receive a discount of 20%. The results will appear in the message box. Sub If_Input_ElseIf() Dim Amount As Variant Dim discount As Double Amount = InputBox("Enter an Amount:") If Amount >= 0 And Amount < 25 Then 73 | P a g e


Chapter 7:

IF Statement and Decision Modelling Page | 74 discount = 0.1 ElseIf Amount >= 25 And Amount < 50 Then discount = 0.2 Else discount = 0.3 End If MsgBox "Savings: " & discount End Sub

If – Or – Then The other common operator here is the Or operator that is reserved as a keyword in VBA. With the Or operator, only one condition needs to be met. So both (True and False) or (False or True) will cause our code to act. Look at the piece of code below and you will see that if the value is less than 50 Or greater than 100 then our action will be carried out. Focus on this part of the procedure. If Rng1.Value < 50 or Rng1.Value >100 Sub If_Range_Single() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value < 50 Or Rng1.Value > 100 Then Rng2.Value = 50 + Rng1.Value End Sub I’m sure that you can see why these two operators are used so frequently with the If function in decision modeling within VBA applications.

If – Not – Then Another operator that we can use with our If statement is Not. It is used to reverse the statement. Not is another VBA keyword. In the procedure that you see below the message box would simply indicate that 500 is not less than 499. So if you need reverse logic you might want to use the Not operator in conjunction with your If function. Add the code below into a module and test it. Then change the values and test it again.

74 | P a g e


Chapter 7:

IF Statement and Decision Modelling Page | 75 Sub If_MsgBox() If Not 500 < 499 Then MsgBox "500 is not less than 499" End If End Sub

IIf Function The Immediate If Function (IIf) almost perfectly mirrors the worksheet If function. This function is more often used in Microsoft Access then it is in Excel. It will run a little bit slower and will evaluate both conditions. If applied incorrectly an error is generated as both conditions need to be evaluated. It is good to know that the IIf function exists, and how it is constructed but I think you’ll find that you will not use this construct very often. Here is the syntax: IIf (condition to evaluate, True part, False part) Copy the code below to a module and add a value to cell A1 of the active sheet and run the procedure. Now remove the value and run the procedure again. Sub IIf_Msg() Dim rng As Range Set rng = Range("a1") MsgBox IIf(IsEmpty(rng), "Cell is empty", "Cell is not empty") End Sub

Conclusion We have looked at a few different variations that show how to use the If function. You will find that you will probably use it for only the most basic of operations. More complex scenarios will be covered with the Select Case structure. As you will see in our next chapter, it holds out its hand with many great advantages over the If statement for more complex decision modeling in our procedures. We will be weighting up these two decision making functions.

75 | P a g e


Page | 76

Chapter 8:

Select Case Constructs

OVERVIEW              

Benefits of the Select Case function Syntax Select Case - (Single condition) Select Case - (Multiple conditions) Select Case – Else Multiple Conditions (Comma separator) Multiple Conditions (To) Combining Multiple Conditions (To + separator) Using Operators with Select Case Select case and Text Conditions Option Compare Text Combining Numbers and Text Conditions Combining Select Case (Magic combinations) List of Keywords and Operators for Select Case function

Open the resource file Open the resource file that came with this eBook and navigate to the Select Case Function worksheet. This will enable you to test each of these examples.

76 | P a g e


Chapter 8:

Select Case Constructs Page | 77

Decision modelling - Tool number 2 In the previous chapter we looked at decision modelling with the use of the If Function. Now we will build on this concept with the Select Case function. This function can provide more flexibility, especially when multiple conditions need to be met. Some of the features of this function are unique. We will compare both the If function and the Select case function to see when it is best to use each one.

Benefits of the Select Case function 1. 2. 3. 4. 5.

More efficient, especially with multiple conditions Easier to read and decipher Provides more flexibility Handles multiple conditions easily and clearly Text and numbers can be combined as conditions

77 | P a g e


Chapter 8:


Syntax Let’s have a look at the way that the syntax is constructed. Select Case (Expression to test) Case (Test1) Execute code 1 Case (Test2) Execute code 2 Case Else Execute code 3 End Select

How the Select Case function works: The Select Case Function will only run when a test condition is met. Notice that there are three basic parts to the Select Case Function. 1.

The expression to test, for example. (Range("A1").Value). This expression is often best set as a variable.

2.

A single or multiple statements to test with this expression.

3.

Code that is executed when the statement condition is met.

78 | P a g e


Chapter 8:


With up to three conditions it probably has little advantage over the If Function. It would be the function of choice however if more than three conditions needed to be met. If you are a beginner to Visual Basic for Applications then it would be recommended that you learn how to use the If Function first and then move to the Select Case function for larger decision-making and application modelling. I have prepared this table that compares the If Function to the Select Case function under various conditions. It should help in deciding which function is best to use in various situations. Please note: This is just a guide.

79 | P a g e


Chapter 8:


Select Case - (Single condition) Let’s get started with some basic examples of how you can use the Select Case Function in your VBA code. In this single condition example shown below I have not included the optional Case Else statement. In reality you would not use a single condition Select Case statement, but to show the basic setup, we will do so. Note: It would be best if you just had a single condition, as you see below, that you use the If Function. I will break this down into the three parts that we discussed earlier. 1. Select Case (The expression to test) = Range("A1").Value 2. Case ( statements to test with this expression) = 5 3. (The code to run when the condition is met) = Range("B1") = 50 The way that this code would then be understood is: If Range("A1").Value is equal to 5 then Range("B1") = 50. Sub Case_Single() Select Case Range("A1").Value Case 5: Range("B1") = 50 80 | P a g e


Chapter 8:

Select Case Constructs Page | 81 End Select End Sub The code can be added to the same line by using a Colon: (as shown below). I think that this makes the function easier to read. It will make no difference to the performance of the function. Case 5: Range("B1") = 50

Select Case - (Multiple conditions) In this example I have used multiple conditions. When the first condition is met the code will be run. If you were to compare this to an If statement you would soon realize the Select Case is far easier to read and understand. Another advantage is that this code will execute very efficiently. The formula below reads: If Active sheet cell A1 value equals 5 then active sheet value B1 equals 50. If cell A1 equals 10 then cell B1 equals 100 and so on. Sub Case_Multiple() Select Case Range("A1").Value Case 5 Range("B1") = 50 Case 10 Range("B1") = 100 Case 15 Range("B1") = 150 Case 20 Range("B1") = 200 End Select End Sub

Select Case – Else The Else statement is optional, however it is extremely handy as it provides a Not (condition not met) option. In the example below if none of the conditions are met, the Else part of the code can be used to clear the values. 81 | P a g e


Chapter 8:

Select Case Constructs Page | 82 Sub Case_Else() Select Case Range("A1").Value Case 5 Range("B1") = 50 Case 10 Range("B1") = 100 Case 15 Range("B1") = 150 Case 20 Range("B1") = 200 Case Else Range("B1") = "" End Select End Sub

Multiple Conditions (Comma separator) A comma can be used as a separator to achieve multiple Or conditions. I have also added the two ranges as variables and dimensioned them. The formula below reads: If Rng1 is 5 or 10 or 15 or 20 then Rng2 would be given the value of 50 and if no condition is met then the value of Rng2 would be blank. To use an If function to do this would require a very long line of code that would be difficult to understand. Sub Case_Comma() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case 5, 10, 15, 20 Rng2 = 50 Case Else Rng2 = "" End Select End Sub 82 | P a g e


Chapter 8:


Multiple Conditions Between (To) Here is a great feature of the Select Case function. Multiple conditions between values or text can be used as the condition to be met with the use of the To keyword. This equates to “Between”. If the value Rng1 is between 5 and 14 then Rng2 value would be 50. If Rng1 value is between 15 and 100 then Rng2 value would be 100. If neither condition is met then Rng2 would be blank. Sub Case_To() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case 5 To 14 Rng2 = 50 Case 15 To 20 Rng2 = 100 Case Else Rng2 = "" End Select End Sub

Combining Multiple Conditions (To + Separator) We can also combine the two previous features in the one Select Case function. Each Case statement contains two sets of contiguous values as the condition to be met. You can use more than two sets if you like. Note: How easy this is to read and decipher. Sub Case_To_Comma() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case 5 To 10, 15 To 20 83 | P a g e


Chapter 8:

Select Case Constructs Page | 84 Rng2 = 50 Case 30 To 40, 50 To 60 Rng2 = 100 Case Else Rng2 = "" End Select End Sub

Using Operators with Select Case You can use operators with your statements. These include, >= , <=, >, <. The formula below reads: In this instance if Rng1 is less than < 20 then Rng2 value would be 20 and if Rng1 value is less than 50 then Rng2 value would be 50 if the value of Rng1 is greater than or equal to 50 the value of Rng2 would be blank. Note: The first condition met will be executed. Both less <20 and <50 are less than 20. But because we put <20 first, it will be executed first, for values <20. Sub Case_Operators() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case Is < 20 Rng2 = 20 Case Is < 50 Rng2 = 50 Case Else Rng2 = "" End Select End Sub

84 | P a g e


Chapter 8:


Select case and Text Conditions We can work with text with the Select Case function very easily. In the procedure below if the text in Rng1 is “Online” then Rng2 would have the text “Online is there”. If the condition is not met then Rng2 would be “Wrong text”. Note: Text is by default case sensitive (Option Compare Binary). “online” (lowercase) would not be met as a condition. Sub Case_Text() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case "Online" Rng2 = "Online is there" Case Else Rng2 = "Wrong text" End Select End Sub

Option Compare Text Specific word If you wanted to make the procedure not case sensitive, it is necessary to add the (Option Compare Text) above the procedure as shown below. Option Compare Text Sub Case_Text_Compare() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case "Online" Rng2 = "Online is there" Case Else Rng2 = "Wrong text" End Select 85 | P a g e


Chapter 8:

Select Case Constructs Page | 86 End Sub

Alphabetically with operators Let’s switch our Select Case function code to a message box. By using an operator with text we can set conditions for words in any part of the alphabet. The message box would vary depending on whether we had used (Option Compare Text) above our procedure. Sub Case_Text_Compare2() Select Case "robert" Case Is < "Peter" MsgBox "Is less than" Case Is > "Peter" MsgBox "Is greater than" End Select End Sub

Alphabetically between letters In this procedure we are checking for the express R (Capitals) between "a" To "m" and "n" To "z". Add this to a module and test it by changing the expression letter. Sub Case_Text_Compare3() Select Case "R" Case "a" To "m" MsgBox "between a and m" Case "n" To "z" MsgBox "between n and z" End Select End Sub

Combining Numbers and Text Conditions It is possible to combine both text and numbers as part of the statement to test. Look at the example below and you will note I have made a comment in the code to indicate a variable must be used for our expression. Change the variable value from text “R” to a number (between 1 and 30) and run the code. 86 | P a g e


Chapter 8:

Select Case Constructs Page | 87 Sub Case_Text_Number() Var = "R" ' 22 'must set a variable here Select Case Var Case 1 To 10, "a" To "m" MsgBox "between a and m or 1 to 10" Case "n" To "z", 20 To 30 MsgBox "between n and z or 20 to 30" End Select End Sub

Combining Select Case (Magic combinations) With this example to show the flexibility of the case function we are testing the expression with 3 statements with the use of Numbers / Text and an Operator. Sub Case_Combo() Set Rng1 = Range("A1") Select Case Rng1 Case 1 To 10 MsgBox "between 1 to 10 the number is " & Rng1 Case "n" To "z" MsgBox "between n and z the word starts with " & Rng1 Case Is >= 11 MsgBox "greater than 10 the number is " & Rng1 End Select End Sub

List of Keywords and Operators for Select Case function Here are the operators that can be used Operators:

>

<

>=

<=

Keywords available to the function Keywords:

(Case Is)

(To) (Select Case) (End Select) (Case Else)

Separator Separator:

(Or)

, (comma)

87 | P a g e


Chapter 8:


Two great program boosters That covers all of the basics for the Select Case Function. Both the If function and the Select Case Function are fantastic decision making tools. The skill that we need to develop is to know when each should be used effectively. While these two functions are not synergistic they certainly go hand in hand with many similar features and outcomes. With both of these functions at our disposal we can create simply awesome applications with automated decision making processes that can mean the difference between a successful outcome and failure.

88 | P a g e


Page | 89

Chapter 9:

Excel VBA Message Boxes

OVERVIEW Key points from this chapter:        

Microsoft Excel’s use for message boxes Simple Message Box Message Box Syntax Constants available to the message box function How it all works behind the scenes Multiple lined Message Boxes Message Boxes to give the user options VBA Examples

Let your applications speak for themselves Decision making –Tool number 3 Our applications should not only hold out their hand to greet the user but should also communicate with them where necessary to ensure that the right decisions are made.

Yes we will be empowering the user with: 89 | P a g e


Chapter 9:

Excel VBA Message Boxes Page | 90

1. The right options 2. Effective decision making 3. Clear communication In this chapter we will discuss how we can use message boxes to communicate and facilitate decision-making within our applications.

Microsoft Excel’s use for message boxes Microsoft Excel communicates with us with message boxes when actions that we take are inappropriate and to explain how to make the right decision with regard to the wrong action. As an example: Notice what happens as we try to copy across merged and unmerged cells at the same time.

Note: The Action is stopped and there is a warning, but more importantly the user is helped to make the right decision. Let’s see how we can create a simple message box like this with Visual Basic for Applications. Open the Visual Basic Editor and insert a new module from the Insert Tab. Into this module we will put the test code that you see this article. Test each piece of code until you fully understand the wonderful features available.

Simple VBA message box We will add a value to range A1 of the active worksheet and then tell the user that the operation was successful with a simple message box. 90 | P a g e


Chapter 9:

Excel VBA Message Boxes Page | 91 Copy and paste this into the module you created earlier. Put the mouse pointer inside the code and press the F5 key to run the macro. Sub MessageBox() Range("A1").Value = 50 MsgBox "The value was added successfully" End Sub Here is the resulting message box. You have created your first message box. The syntax for the message box function is very interesting and extremely versatile. We are going to step through the available parameters one at a time.

Syntax Delete the line: MsgBox "The value was added successfully". Now type the keyword MsgBox, after you typed the keyword MsgBox and push the space bar you are prompted with all of the options available to the message box. Test this out; your code should look just like this. We are being told here exactly what to do step-by-step.

Notice we have 4 parameters MsgBox(prompt, [buttons] , [title] ,[ helpfile, context])

Prompt The square brackets indicate optional parameters. The prompt parameter is the only one that is required (must be added between double quotes). That is the only one that we used in our simple procedure. Prompt="The value was added successfully" 91 | P a g e


Chapter 9:


Buttons and Icons Type a comma as a separator after the prompt. You will be asked to choose the button constant. If we fail to use this parameter vbOKOnly constant will be added by default (you will not see it in the code). Have a look at the options available in the dropdown list. All of these constants are in the table below. Note: Each constant has a numeric value equivalent.

Select vbInformation from the drop-down list. Your code should look like this: MsgBox "The value was added successfully", vbInformation or MsgBox "The value was added successfully", 64 Now click inside your code and push the F5 key to run the procedure. Notice the information icon in the message box.

Syntax: [buttons] =, vbInformation (or the number 64). Note: I do not suggest that you use numbers as it will be difficult to decipher your code.

92 | P a g e


Chapter 9:


List of Icons There are four icons available to us. Here are the four constants.    

vbCritical vbQuestion Visual Basic Editorxclamation vbInformation

List of Buttons Eight buttons are available to us in the six constants displayed below.      

vbOKOnly vbOKCancel vbAbortRetryIgnore vbYesNoCancel vbYesNo vbRetryCancel To create effective communication within our applications we need to add buttons and icons and If or Case functions that will enable the user to make a successful choice or decision. In the message box below we have chosen the vbYesNoCancel constant which will add three buttons to a message box. In a moment I will show you how to add code that will make these buttons very effective communication tools. For now I just want to demonstrate how to add the various icons and buttons to our test message box. MsgBox "The value was added successfully", _ vbYesNoCancel + vbInformation Note: To add the code to several lines, press the spacebar then an underscore and then hit enter. This will make long code easier to read by putting it onto multiple lines. When you run your code this is the message box with the icon and three buttons that should appear, as shown below.

93 | P a g e


Chapter 9:


Default Button In our previous illustration you will notice by default that our first button is highlighted or active. We need to be able to control which button the operator should be choosing and make that button the active button, when the message box opens. There is a very good reason for this: If we had the wrong button active by default we can have problems because people often just simply click through message boxes without reading them. This can bring devastating results to our project. The wrong data could be irretrievably deleted. So how do we change the default button? In the button parameter for our code we would choose to add the constant that will show which button should be the default. Note: Multiple button options will need to be added. vbYesNoCancel + vbInformation + vbDefaultButton2

94 | P a g e


Chapter 9:

Excel VBA Message Boxes Page | 95 As you can see from the illustration now our second button is highlighted as the active button because we have used the constant vbDefaultButton2. Here are the four constants for setting the active button.

   

vbDefaultButton1 vbDefaultButton2 vbDefaultButton3 vbDefaultButton4

Adding a variable for the buttons and icons To make your code easier to read we could add button syntax as a variable. This is because the button syntax can include Button Type, Icon and Default Button. Note: When we click one of these buttons (constants) a whole number (Integer) is processed so we would need to dim this variable with the data type Integer. Add and run this code in your module. Sub MessageBox() Dim ButtonType As Integer ButtonType = vbYesNoCancel + vbInformation + vbDefaultButton2 Range("A1").Value = 50 MsgBox "The value was added successfully", ButtonType End Sub

Title Our next parameter is the title for the message box. All we need to do is type a comma then inside double quotes add the text that we want to appear in the title of the message box. MsgBox "The value was added successfully", _ VbInformation, "Operation Complete" Syntax: [, title]= , "Operation Complete"

95 | P a g e


Chapter 9:


That is all the information we need to know to get us started with buttons and icons in our message boxes. Let’s move along to our next parameter.

Help file [helpfile, context] The help file and context deal with a specific help file and context that may be set up for the message box. We will not deal with this in a basic tutorial. You will in fact find that you will rarely use these two parameters.

Constants available to the message box function The list below that Microsoft provides is extremely helpful to understanding the full range of options available to the Message Box function. To make it even easier I’ve grouped them and put a heading above each group to show what each constant will do. May I encourage you to take the time to look carefully at this list? Already in this chapter we have discussed many of these constants and how useful they can be. This table will shed some light on how the message box that VBA presents is structured. The first three sections will be extremely helpful if you are new to VBA.

96 | P a g e


Chapter 9:


Constant

Value Description Button types: 8 unique buttons in 6 constants vbOKOnly Display OK button only. 0 vbOKCancel Display OK and Cancel buttons. 1 vbAbortRetryIgnore Display Abort, Retry, and Ignore 2 buttons. vbYesNoCancel Display Yes, No, and Cancel 3 buttons. vbYesNo Display Yes and No buttons. 4 vbRetryCancel Display Retry and Cancel buttons. 5 Icons available to the Message Box vbCritical Display Critical Message icon. 16 vbQuestion Display Warning Query icon. 32 Visual Basic Display Warning Message icon. 48 Editorxclamation vbInformation Display Information Message 64 icon. Active Button vbDefaultButton1 0 First button is default. vbDefaultButton2 256 Second button is default. vbDefaultButton3 512 Third button is default. vbDefaultButton4 768 Fourth button is default. Modality vbApplicationModal 0 Application modal; the user must respond to the message box before continuing work in the current application. vbSystemModal 4096 System modal; all applications are suspended until the user responds to the message box. Help and Alignment vbMsgBoxHelpButton 16384 Adds Help button to the message box VbMsgBoxSetForegro 65536 Specifies the message box und window as the foreground window vbMsgBoxRight 524288 Text is right aligned vbMsgBoxRtlReading 1048576 Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

How it all works behind the scenes 97 | P a g e


Chapter 9:

Excel VBA Message Boxes Page | 98 Before we start adding some examples that you can use let’s have a look at what happens when you click a button in the message box. VBA will recognise the constant but what it actions is the value that the constant stands for. As an example if we were the click vbOK then the value (1) is what is processed. However if we had 3 buttons in our message box with the constant vbYesNoCancel then the values 6 for Yes / 7 for No / 2 for Cancel would be returned as an integer and processed accordingly if clicked.

Constant vbOK vbCancel vbAbort vbRetry vbIgnore vbYes vbNo

Value 1 2 3 4 5 6 7

Description OK Cancel Abort Retry Ignore Yes No

Multiple lined Message Boxes There are times when we need to present a long message or multiple lines to communicate with the user. How can we break our text into multiple lines? The message box function statement addresses this with the forced line break. The constant is vbCrLf and is expressed in this way in your code & vbCrLf & This is how it would be written in VBA. MsgBox("Are you sure you" & vbCrLf & "want to delete this data?" We can also use the carriage return character Chr(13) or linefeed character Chr(10) or a carriage return linefeed character combination. Here are three examples of how it would be expressed in your code. MsgBox("Are you sure you" & Chr(13) & "want to delete this data?" MsgBox("Are you sure you" & Chr(10) & "want to delete this data?" MsgBox("Are you sure you" & Chr(10) & Chr(13) & "want to delete this data?" Now that we have all this information as to how a message box works and how we can set it up visually, let’s have a look at some code that we could run that will enhance our 98 | P a g e


Chapter 9:

Excel VBA Message Boxes Page | 99 applications. This will demonstrate the functionality and flexibility of this wonderful decisionmaking tool that VBA offers.

Message Boxes Examples Open the VBA editor and in the Insert Tab choose Insert Module. You can then copy and paste the code below into this module and run all of the tests for yourself.

Example 1

Displaying a Message

In the example below you can add any code that you like in the code section. The procedure will be paused and a simple message box with all of the default settings will appear with the text in it saying "Hello Everybody" Sub MessageBox() 'any code MsgBox "Hello Everybody" End Sub

Example 2

A Message Box with Three Buttons and In Icon

A variable is assigned for the button parameter in the message box. The variable could have been written this way ButtonType = 4 + 64, but unless you know 4 and 64 stand for the value of the constants vbYesNoCancel and vbInformation then the variable will be quite a mystery. Sub MessageBox() Dim ButtonType As Integer ButtonType = vbYesNoCancel + vbInformation ' any code MsgBox "The value was added successfully", ButtonType End Sub

Example 3

A Message Box with an Option

Let’s create a scenario that will often be presented within our applications. Our code is going to allow the user to delete some vital information from a dataset. Let’s see how we 99 | P a g e


Chapter 9:

Excel VBA Message Boxes Page | 100 can use the message box to guide the user to make the right decision. I’ve taken this example from the Simply the Best Phonebook project on the website. The variable named CheckDelete is added to the message box. Note: We have declared (dimension) the message box with the data type VbMsgBoxResult. A message box will appear and if the user clicks Yes then the value of the cell A1 is deleted. If the user clicks No then the value is not cleared from the cell. This is our first example where we are encouraging the user to make a decision by using the If function in association with the message box. Notice that we run the If function on one line of code. (Refer to previous chapter). Sub DeleteCheck() Dim CheckDelete As VbMsgBoxResult CheckDelete = MsgBox("Are you sure you want to delete this data?", vbYesNo) If CheckDelete = vbYes Then Range("A1").ClearContents End Sub

Example 4

Message Box Option and Confirmation Message

If the user clicks Yes then we run the code and then give the user a confirmation message to let them know that this has occurred. This is particularly useful if we are working with data that the user cannot see or that is on a different worksheet. It is establishing good communication with the end user. Sub DeleteCheckMsg() Dim CheckDelete As VbMsgBoxResult CheckDelete = MsgBox("Are you sure you want to delete this data?", vbYesNo) If CheckDelete = vbYes Then Range("A1").ClearContents MsgBox "Your data has been deleted" End If End Sub

100 | P a g e


Chapter 9:


Example 5

Message Box Default Button

Unfortunately most people are in the habit of clicking their way through programs without reading the messages that are given to them. To avoid disaster that could occur when someone clicks the Yes button or hits the Enter key without properly reading the message we could set the default button to No. That way the user would have to make a conscious decision to delete the data by clicking the Yes button which is not the default. vbYesNo + vbDefaultButton2 Sub DeleteCheckNo() Dim CheckDelete As VbMsgBoxResult CheckDelete = MsgBox("Are you sure you want to delete this data?", vbYesNo + vbDefaultButton2) If CheckDelete = vbYes Then Range("A1").ClearContents MsgBox "Your data has been deleted" End If End Sub

Example 6

Message Box with If / Else

In this code we simply add the Else statement to the message box which will provide the user with a message regardless of the choice. We will be confirming the users decision for both Yes and No. Sub DeleteCheckValue() Dim CheckDelete As VbMsgBoxResult CheckDelete = MsgBox("Are you sure you want to delete this data?", vbYesNo + vbDefaultButton2) If CheckDelete = vbYes Then Range("A1").ClearContents MsgBox "Your data has been deleted" Else MsgBox "The value was not deleted" End If End Sub

101 | P a g e


Chapter 9:


Example 7

Message Box with Three Options

The If Function is used here once again. This time we are adding the ElseIf statement along with the Else. The range that we are adding text to is set as a variable. Depending on the decision that the user makes we will be sending back that result to the user in a message box. This is a very basic example, however I’m sure that you can see the wonderful possibilities in being able to present variable data back to the user in a message box so that they can make the right decisions in their workflow. Sub AddCheckShow() Dim CheckAdd As VbMsgBoxResult Dim rng As Range Set rng = Range("A1") CheckAdd = MsgBox("Do you want to add the word (Yes)", vbYesNoCancel + vbDefaultButton2, "Add Value") If CheckAdd = vbYes Then rng.Value = "Yes" MsgBox "Your data is added. The Value is " & rng ElseIf CheckAdd = vbNo Then rng.Value = "No" MsgBox "Your data is added. The Value is " & rng Else rng.Value = "" MsgBox "The value is blank" End If End Sub

Example 8

Message Box with the Select Case Function

The example below is the same as the previous code, however, this time I have used the Select Case Function. For me it is a lot easier to read and will run very efficiently. Sub AddCheckCase() Dim CheckAdd As VbMsgBoxResult Dim ButtonType As Integer Dim rng As Range ButtonType = vbYesNoCancel + vbInformation + vbDefaultButton2 Set rng = Range("A1") 102 | P a g e


Chapter 9:

Excel VBA Message Boxes Page | 103 CheckAdd = MsgBox("Do you want to add Yes", ButtonType, "Add Value") Select Case CheckAdd Case vbYes: rng.Value = "Yes" MsgBox "Your data is added. The Value is " & rng Case vbNo: rng.Value = "No" MsgBox "Your data is added. The Value is " & rng Case vbCancel: rng.Value = "" MsgBox "The value is blank" End Select End Sub

Conclusion Message boxes are a wonderful communication and decision making tool at our disposal. The effectiveness of message boxes in our VBA applications lies entirely in our hands. Take the time to use message boxes to empower the end user with effective accurate decisions. So far in our decision modeling we have looked at the If Function, the Select Case and Message Boxes as tools that we can use. The best is yet to come, because we will now take this to a new level with the Input Box function.

103 | P a g e


Page | 104

Chapter 10: Understanding the Input box construct OVERVIEW . 1. 2. 3. 4. 5. 6. 7.

Syntax Input Function Input Method Type argument A Simple Input Box VBA Examples Create a User Login a. Example 1 Add a name b. Example 2 Create a login c. Example 3 Create a login dynamic d. Example 4 Login with 3 attempts (Download) 8. Collect employee age a. Example 1 Age list b. Example 2 Age calculator (Download) 9. BMI Calculator a. Example Basic BMI Calculator (Download) 10. Working with Ranges a. Example Dynamic print area

104 | P a g e


Chapter 10:

Understanding the Input box construct Page | 105

Decision making tool – Number 4 We can let the end users of our applications interact with the data flow with Input Boxes. They are quick, effective and easy to construct and modify. Putting this function into your box of tricks will add greater flexibility to your work We can let the end users of our applications interact with the data flow with Input Boxes. They are quick, effective and easy to construct and modify. Putting this function into your box of tricks will add greater flexibility to your work

Open the resource file Open the resource file that came with this eBook and navigate to the Input Box worksheet. This will enable you to test each of these examples.

Why use an Input Box We can gather some information from our users with the message box function. However there are times when we want to collect more detailed information. To accomplish this task we can use the InputBox function. As you will see in this chapter we can collect information from a spreadsheet or gather details for processing directly from our user with the use of the VBA Input Box Function. We can allow the user to enter data in the form of text, numbers or even a formula by using an InputBox function.

Syntax Let’s have a look at the syntax. Open the VBA editor by holding down the ALT key and press F11. On the insert tab choose Insert module. Type in Sub Test and hit the enter key. 105 | P a g e


Chapter 10:


Then type any single word followed by an equal sign. Type InputBox( The syntax will then be available as shown below. The Microsoft table below explains each parameter. You will notice that the Prompt is mandatory and is inserted between double quotes. You will rarely use [Xpos] (4) and [Ypos] (5) which are the left and top positions for the Input box. Helpfile (6) and ContextID (7) are seldom used. Prompt (1), Title (2), Default (3) and Type (8) are the parameters we will use the most.

expression .InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type) The expression is a variable that represents an Application object. In the Input Box above it is the variable Var=.

This table will give more detailed information Name

Data Type

Descriptions

Prompt

Requires/ Optional Required

String

Title

Optional

Variant

Default

Optional

Variant

Left

Optional

Variant

Top

Optional

Variant

HelpFile

Optional

Variant

The message to be displayed in the dialog box. This can be a string, a number, a date, or a Boolean value (Microsoft Excel automatically coerces the value to a String before it is displayed). The title for the input box. If this argument is omitted, the default title is "Input." Specifies a value that will appear in the text box when the dialog box is initially displayed. If this argument is omitted, the text box is left empty. This value can be a Range object. Specifies an x position for the dialog box in relation to the upper-left corner of the screen, in points. Specifies a y position for the dialog box in relation to the upper-left corner of the screen, in points. The name of the Help file for this input box. If the

106 | P a g e


Chapter 10:

HelpContextID Type


Optional Optional

Variant Variant

HelpFile and HelpContextID arguments are present, a Help button will appear in the dialog box. The context ID number of the Help topic in HelpFile. Specifies the return data type. If this argument is omitted, the dialog box returns text.

Input Function The input box function will allow the user to enter a value into the input box; however the data is always coerced to text and would return a string value.

Input Method If we add the word application before the InputBox syntax we turn the Function into a Method. Option Type (8) now appears. This will allow us to set the data type for the input box. No other type will be allowed. Below is a list of type values and their respective meanings.

Type argument Value

Meaning

0

A formula

1

A number

107 | P a g e


Chapter 10:


2

Text (a string)

4

A logical value (True or False)

8

A cell reference, as a Range object

16

An error value, such as #N/A

64

An array of values

Type 8 refers to the range object and there are 2 options. 1. If we want a cell reference, then we must use the Set keyword to show referring to the cell object. 2.

we are

If we omit this, the value in the range will be returned.

Set myRange = Application.InputBox(prompt := "Sample", type := 8) So if you do not use the Set statement, the variable is set to the value in the range, rather than the Range object itself.  

Set myRange= Application.InputBox =Cell or range address myRange= Application.InputBox =Value in cell

That is the theory behind the Input Box. Now we will look at some practical examples.

A Simple Input Box This InputBox will display a simple dialog box into which information can be added. You will be presented with an OK button and a Cancel button. If OK is chosen then the value entered is returned. If Cancel is clicked then False is returned.

108 | P a g e


Chapter 10:


Sub InputSimple() 'Input variable ShowBox = InputBox("Type Your first name", "Add Name", "Type name here") End Sub

VBA Examples I have added the four examples below to help with the understanding of the InputBox function and Method. After these four examples I have included four small practical projects that I think you will find very helpful. You will also be able to download two workbooks containing three of the projects. 1. 2. 3. 4.

A user login An age calculator The BMI calculator Print area range tool

Create a User Login I have added four examples for a user login. Each example builds on the previous with the last example developing a small login application. Creating a user login with the Input box is relatively easy. We can use the Input box Function in order to accomplish this. In the example below we are adding the value of the Input box into the active sheet range A1.

Example 1

Add a name

Notice in this example that we are using the Prompt, Title and Default parameters of our syntax. Sub InputName() 'Declare the variable Dim AddName As String 'Input variable AddName = InputBox("Type Your first name", "Add Name", "Type name here") 'Add name to the worksheet Range("A1").Value = AddName

109 | P a g e


Chapter 10:


End Sub

Example 2

Create a login

We can extend the flexibility of this simple login by adding the user name and in the next column adding the date and time that the login occurred. All those who login are recorded. In this example we are adding a variable that finds the next available row to into which we will add our data. Sub Login() 'Declare the variables Dim AddData As Range Dim AddName As String 'Copy location Set AddData = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'Input variable AddName = InputBox("Type Your first name", "Add Name") 'Check for a value If AddName = Empty Then Exit Sub 'Add data to the worksheet AddData.Value = AddName AddData.Offset(0, 1).Value = Now End Sub

Example 3

Create a login dynamic

Let us take this one step further by adding a Select Case statement that requires that the user uses a passcode to be able to login. If the user fails to select the right pass code then a message box is sent telling them that they are not authorised to login to the workbook. With this login code we need to add the names of those allowed to login directly into the VBA procedure. In this instance it is, Trevor, Harry and Mary. Sub Login2() 'Declare the variables Dim AddData As Range Dim AddName As String 'Copy location Set AddData = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 110 | P a g e


Chapter 10:


'Input variable AddName = InputBox("Type Your first name", "Add Name") 'Check for a value If AddName = Empty Then Exit Sub 'Check for the right name Select Case AddName Case "Trevor" AddData.Value = AddName AddData.Offset(0, 1).Value = Now MsgBox "Welcome: - " & AddName Case "Harry" AddData.Value = AddName AddData.Offset(0, 1).Value = Now MsgBox "Welcome: - " & AddName Case "Mary" AddData.Value = AddName AddData.Offset(0, 1).Value = Now MsgBox "Welcome: - " & AddName Case Else MsgBox "Name not authorised" End Select End Sub

Example 4

Login with 3 attempts

Here is a practical example of all of the features we have just mentioned plus extending the flexibility by allowing the number of users to login to be as many as we require.

111 | P a g e


Chapter 10:


To do this we loop through the range of valid users and compare this with the value that is typed into the Input box. This illustration shows how the worksheet should be structured. In practice you would not put the list of users where they could be visible. In fact all of this information should really be hidden. If the wrong password is added three times, the workbook will close and not be saved. Note: Be careful if you edit this code because it is possible to create a procedure that cannot be exited.

Sub LoginCheck() 'Declare the variables Dim AddData As Range, Check As Range Dim Nme As String 'Count attempts Set Check = Sheet1.Range("E2") 'Destination location Set AddData = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'Input variable Nme = Application.InputBox("Add your login code name:- You have 3 attempts", Type:=2) 'Variable for authorise Auth = 0 'Loop through range of names For Each cName In Sheet1.Range("D2:D14") If cName.Text = Nme Then AddData.Value = Nme AddData.Offset(0, 1).Value = Now MsgBox "Welcome: - " & Nme Auth = 1 Exit For End If Next cName 112 | P a g e


Chapter 10:


'Not authorised after 3 attempts and close workbook If Auth = 0 Then If Check.Value = 3 Then ActiveWorkbook.Close False MsgBox "You are not authorised" Check.Value = Check.Value + 1 'Call macro again LoginCheck End If End Sub

Collect Employee Age Example 1

Age list (Input Function)

In this example we are just simply collecting the age of the employee and adding it to a list on the worksheet. The ages are added one after the other in column five.

Sub InputAge() 'Declare the variable Dim AddAge As Long 'Input variable AddAge = InputBox("Type Your Age", "Add Age", "Type your age") 'Copy location Cells(Rows.Count, 5).End(xlUp).Offset(1, 0) = AddAge End Sub

Example 2

Age calculator (Input Method)

Let’s use the Import box method once again, this time to calculate the age of an individual based on the date of birth entered into the Input box. The Type will now appear as a

113 | P a g e


Chapter 10:


parameter. In this procedure we are setting the Type to (1). This restricts the entry into the Input box to a number. Dates are numbers! If the user enters text it will be rejected. You will notice that we do all of the calculations on the worksheet and return the calculated value to the user in a message box at the end of the procedure. You will find this in the resource file. Note: A formula needs to be added to cell G8 to calculate the age. G8 formula =If(F8="","",DATEDIf(F8,TODAY(),"Y")&" Years, "&DATEDIf(F8,TODAY(),"YM")&" Months, "&DATEDIf(F8,TODAY(),"MD")&" Days")

The result will appear in the message box. Notice we are adding this cell values to the message box content.

Sample file Sub InputAge() 'Declare the variables Dim AddAge As Long Dim mMyAge As Range 'Set the the result range Set MyAge = Sheet1.Range("G8") 'Variable for input box AddAge = Application.InputBox("Type Your Age", "Add Age", "Type your date of birth", Type:=1) 'Check for value 114 | P a g e


Chapter 10:


If IsNumeric(AddAge) And AddAge <> 0 Then 'Add date of birth to worksheet Sheet1.Range("F8") = AddAge 'Show formula result in a message box MsgBox "Your age is:- " & MyAge End If End Sub

BMI Calculator Example: Basic BMI Calculator (Input Method)

Two input boxes are called one after the other and the data is stored on the worksheet. In this example we are using the Input box Method by inserting Application before the keyword InputBox. The data type that we have chosen is (1) indicating that we are limiting this to numbers only. We show two input boxes one after the other to collect the Height and then the Weight which is necessary to calculate our BMI. Height is added to F8 and Weight is added to G8 on the worksheet. A formula is added to cell H8 to calculate the BMI for us. This is stored as a variable and shown in the message box result. H8 formula

=G8/((F8/100)*(F8/100))

115 | P a g e


Chapter 10:


Resulting message box show below

Here is the code.

Sub BMI_Calculator() 'Declare the variables Dim Height As Long Dim Weight As Long Dim BMI As Range 'Variable for result Set BMI = Sheet2.Range("H8") 'Input variable for height Height = Application.InputBox("Add your height in centermetres:", Type:=1) 'Check for value and add value If IsNumeric(Height) And Height <> 0 Then Sheet2.Range("F8") = Height Else 116 | P a g e


Chapter 10:

Understanding the Input box construct Page | 117 MsgBox "You must add a Height" Exit Sub End If 'Input variable for weight Weight = Application.InputBox("Add your weight in kilograms:", Type:=1) 'Check for value and add value If IsNumeric(Weight) And Weight <> 0 Then Sheet2.Range("G8") = Weight Else MsgBox "You must add a Height" Exit Sub End If 'Show the BMI result in a message box MsgBox "Your BMI is: " & Format(BMI, "#,##0.0")

End Sub

BMI = (Weight in Pounds / (Height in inches x Height in inches)) x 703 BMI = (Weight in Kilograms / (Height in Meters x Height in Meters)) A healthy BMI for an adult is between 20 and 25.

Working with Ranges (Input Method for Objects) This is our final working example. You will find this piece of code very useful in setting up a variable print area and then opening the print dialogue box to enable the user to print that selected piece of the worksheet. Note: Type (8) is used for the data type. A cell reference as a range object>

Example:

Dynamic print area

Sub InputPrint() 'Declare the variable Dim PrintArea As Range 'Set error handler 117 | P a g e


Chapter 10:


On Error Resume Next 'Input variable Set PrintArea = Application.InputBox("Select the area to print", "Print area", "Scroll over print area", Type:=8) 'Check for value If PrintArea Is Nothing Then Exit Sub 'Set the print area ActiveSheet.PageSetup.PrintArea = PrintArea.Address 'Show the print dialog box Application.Dialogs(xlDialogPrint).Show 'Reset error handler On Error GoTo 0 End Sub

Conclusion The Input box function and the Input box Method are awesome tools for collecting data from a user and then using that in our calculations and decisions in our applications. It presents us with the opportunity to very allow the user to interact with the processes we have set. We have now discussed the four decision modelling tools at our disposal. The functions are: 1. 2. 3. 4.

If function Select Case function Message box function Input box function and method.

With these functions we can help the end user to make effective decisions as they use our applications.

118 | P a g e


Page | 119

Chapter 11: Understand VBA Loops by Examples OVERVIEW In this chapter we will look closely at loops in VBA.       

What are loops? Do Loop Do Until Loop Do While Loop For Loop For Each Loop Nesting Loops

Open the resource file Open the resource file that came with this eBook and navigate to the Loop worksheet. This will enable you to test each of these examples.

119 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 120

Illustration As an example to help us to understand loops, think of our planet Earth that orbits (loops) around the Sun. With each loop (orbit) life-giving tasks (changes) are carried out that make this planet the jewel of the universe. We are looping around the sun at approximately 67,000 miles an hour (108,000 km per hr), so hang on.

VBA Loops Similarly we can loop through huge datasets and make changes that would otherwise take the user an enormous amount of time. Yes, we can do this at high speed if our loops are constructed properly and used in the right circumstances.

120 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 121 We are often confronted with the arduous task of working with large data sets and ranges of various sizes and types. Loops work their way through data sets performing repeated tasks for us. Skilfully using loops can save you heaps of time, effort and worry. You can literally find a needle in a haystack. There are basically five different types of loops and endless combinations of these. In this chapter may I invite you to open Microsoft Excel and the VBA Editor? Hold down the Alt Key and press F11. This will open the VBA editor; from the Insert tab choose Insert Module. This information is also contained in the resource file that accompanies this eBook. Note: There are 17 basic procedures (Macros) that you can add, test and modify to use in your own VBA procedures. Please note though that these procedures are here to help us to get the concept or the understanding behind how loops work in our VBA code.

What are loops? Loops are controlled VBA structures used to repeat a given section of code a certain number of times or until a particular condition is met. In this section we will look at the five different types of loops and also show how to nest loops. 1. 2. 3. 4. 5. 6.

Do Loop Do Until Loop Do While Loop For Loop For Each Loop Nesting Loops

Do Loop Here is how a Do loop works in its simplest form. All this loop does is simply repeat the code until a condition is met. In the example below we will be adding the value of the variable num = 1 to establish a range and also increment that range. Each time that we loop we are adding one (1) num = num + 1 to the value of the variable. The loop will continue to operate until the number is greater than 30. Loop Until num > 30. Test this loop out in your module.

121 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 122 Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Syntax for the Do loop Do (Code to repeat) Loop Until (Condition to be met) Sub Do_Loop() 'loops until a condition is met 'fills cells A1:A30 with the value of "num" 'increase the variable "num" by 1 with each loop Dim num As Integer num = 1 Do Range("A" & num).Value = num num = num + 1 Loop Until num > 30 End Sub

Do Until Loop The Do Until loop that is almost identical to the Do loop. In this loop we are putting the condition to be met at the start of the procedure. If the condition is not met the loop does not run at all. So we have checked the condition before the procedure runs. Do Until num > 30. With a very large set of data this could make a significant difference to the time the loop would take to run if the condition was not met. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Syntax for the Do Until Loop Do Until (Condition to be met) (Code to repeat) Loop 122 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 123 Sub Do_Until() 'loops until a condition is met 'fills cells B1:B30 with the value of "num" 'increase the variable "num" by 1 with each loop Dim num As Integer num = 1 Do Until num > 30 Range("B" & num).Value = num num = num + 1 Loop End Sub

Do While Loop This loop is in fact the reverse of the two previous loops. It will run while a condition is met. Loop While num < 31 Notice that this time our condition is <31. Because we only want to add 30 values.

Syntax for the Do While Loop (end) Do (Code to repeat) Loop While (Condition to be met)

Do While Loop: Example 1 This procedure loops while a condition is met and will fill the cells of the C1: C30 with the value of our variable. Notice that the condition to meet occurs at the end of the procedure. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Sub Do_While() 'loops while a condition is met 'fills cells C1:C30 with the value of "num" 'increase the variable "num" by 1 with each loop 123 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 124 Dim num As Integer num = 1 Do Range("C" & num).Value = num num = num + 1 Loop While num < 31 End Sub

Do While Loop: Example 2 This is a more effective way to construct the loop with the condition at the beginning to be checked before the procedure is executed. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Syntax for the Do While loop (start) Do While (Condition to be met) (Code to repeat) Loop

Sub Do_While2() 'loops while a condition is met 'fills cells D1:D30 with the value of "num" 'increase the variable "num" by 1 with each loop Dim num As Integer num = 1 Do While num < 31 Range("D" & num).Value = num num = num + 1 Loop End Sub

124 | P a g e



For Loop The For loop is probably the loop that is used the most. For this reason I have added six examples to deal with some of the features that you will find useful in your VBA coding. The number of times that the loop runs is set at the beginning of the procedure. By default it will increment by one each time the loop is run. In the loop we are adding all 56 colours to the first 56 cells in column F. A variable must be set at the beginning of the procedure for this loop. For ColCell = 1 To 56 There are no Until or While features with this loop it simply loops 56 times. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Syntax for the For loop For (Variable) = (Any Number) To (Any Other Number) (Code to repeat) Next (Variable)

For Loop: Example 1 Sub Loop_For() 'loops 56 times 'fills cells F1:F56 with background colors 'adds a backgroud color with each loop Dim ColCell As Integer For ColCell = 1 To 56 Range("F" & ColCell).Select With Selection.Interior .ColorIndex = ColCell .Pattern = xlSolid End With Next ColCell End Sub

125 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 126 For Loop: Example 2 In this example we are adding a number to the range and then changing the font colour to represent all 56 colours available. Two variables are set, one for the For loop and one for our range object. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Sub Loop_For2() 'loops 30 times 'fills cells G1:G56 with 'adds a number and colors the number with each loop Dim ColNum As Integer Dim Rng As Range For ColNum = 1 To 56 Set Rng = Range("G" & ColNum) Rng.Value = ColNum Rng.Font.ColorIndex = ColNum Next ColNum End Sub

For Loop with Step Plus +: Example 3 A wonderful feature of the For loop is that we are able to change the default incremental value of 1 to whatever we want. In the example below we miss one, run one, miss one, run one et cetera. This is achieved by using the Step keyword after our variable. For num = 2 To 30 Step 2 To make this easy to see I have changed the font size by one point after each loop to show which way the loop is running. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Sub Loop_For_Step() 'loops 15 times 'fills every second cell in range H1:H30 with numbers 'changes the font size by 1point with each loop 126 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 127 Dim Rng As Range Dim num As Integer For num = 2 To 30 Step 2 Set Rng = Range("H" & num) Rng.Value = num Rng.Font.Size = num Next num End Sub

For Loop with Step Minus: Example 4 The loop shown here is the reverse of the loop above. Notice two important differences that are contained in our variables. First the numbers are reversed 30 To 1 and second the Step incremental number has a minus sign before it. I changed the font size to show you which way that the loop is running. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code.

Sub Loop_For_Step_Minus() 'loops 30 times from highest to lowest 'fills every cell in range I1:I30 with numbers from highest to lowest 'changes the font size by 1point with each loop Dim Rng As Range Dim num As Integer For num = 30 To 1 Step -1 Set Rng = Range("I" & num) Rng.Value = num Rng.Font.Size = num Next num End Sub

127 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 128 For Loop with Exit: Example 5 It is good practice to provide an exit for our loop especially if we are looping through large sets of data. This will dramatically cut down the time the loop will take to run. To achieve this we need to add an If statement that sets a condition and if the condition is met we use the two keywords Exit For to stop the loop. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Sub Loop_For_Exit() 'loop stops in the middle if condition is met 'exit for loop Dim num As Integer For num = 1 To 1000 Range("J" & num).Value = num If num = 30 Then MsgBox "I will stop at 30" Exit For End If Next num End Sub

For loop changing data: Example 6 As mentioned previously this loop will run exactly the number of times that we tell it to. But what if we are not sure how many times we want the loop to run because our data may change on our worksheet? To cope with this we can count the number of cells and add that figure as the second number in our variable. Add some extra data into column A and test the flexibility of this procedure for yourself. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Sub Loop_For_2() 'allows for a variable amount of data in the range 'bolds all numbers in the rfange Dim num As Integer 128 | P a g e



For num = 1 To Cells(Rows.Count, 1).End(xlUp).Row Range("A" & num).Font.Bold = True Next num End Sub

For Each Loop The For Each Loop is designed specifically for dealing with objects. You will remember previously we discussed objects and object collections in Chapter 3. Cells, ranges, charts, worksheets, workbooks and even the Excel application itself are all objects. We can use this loop to speedily make changes to numerous objects within an object collection. In this example we simply loop through the Range("J1:J30") and change the font to bold. This is a very easy loop to use and to understand. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Syntax for the For Each Loop For Each (Object Variable) in (Object Collection) (Code to repeat) Next (Object Variable)

For Each Loop: Example 1 Sub For_Each() 'loops through the object range 'changes the font to bold Dim mycell As Range For Each mycell In Range("J1:J30") mycell.Font.Bold = True Next

129 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 130 End Sub

For Each Loop: Example 2 With the procedure below we are looping through each sheet in our workbook and protecting each sheet. You can add a password to this to make the protection secure. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code.

Sub For_Each_Protect() 'loops through each worksheet and protect the sheet Dim sht As Worksheet For Each sht In ThisWorkbook.Worksheets sht.Protect Next sht End Sub

For Each Loop: Example 3 Now that you’ve protected all the workbooks it would be a good idea to unprotect them again and that is what the code below achieves for us. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Sub For_Each_UnProtect() Dim sht As Worksheet 'loops through each worksheet and unprotect each sheet For Each sht In ThisWorkbook.Worksheets sht.Unprotect Next sht End Sub

130 | P a g e



Nesting Loops To understand the concepts of nesting loops think about the illustration at the beginning of this chapter about our earth orbiting around the sun. At the same time, the earth itself is looping or orbiting on its own axis. We can see a structure here of an outer loop and an inner loop. Around the sun is the outer loop and on its axis is the inner loop. It is common practice to nest loops or to put one loop inside of another loop so that two sets of conditions need to be met. In the procedure below, For Each sht is the outer loop and For Each mycell is the inner loop. This macro will add the cell address to the Range("K1:K30") of every sheet in the workbook.

Nesting Loops

Example 1

I have included several examples here of nested loops. The first example loops through each sheet and adds the cell address to the range on each sheet. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Sub For_Each_Nest() 'loops through each worksheet 'adds the cell address to the range Dim sht As Worksheet Dim mycell As Range For Each sht In ThisWorkbook.Worksheets For Each mycell In sht.Range("K1:K30") mycell.Value = mycell.Address Next mycell Next sht End Sub

Nesting Loops Example 2 This example loops through each worksheet and checks for a value in a range on each sheet and if that value exists it colours the background of the cell to a yellow. 131 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 132 The outer loop is the sheet object and the inner loop is the range object. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Sub For_Each_Nest2() 'loops through each worksheet 'checks for a value in a range 'colors the background if it exists Dim sht As Worksheet Dim mycell As Range For Each sht In ThisWorkbook.Worksheets For Each mycell In sht.Range("A1:A30") If mycell > 15 And mycell < 25 Then mycell.Interior.ColorIndex = 6 End If Next mycell Next sht End Sub

Nesting Loops Example 3 This is a little bit more complex. Have a close look at it and see if you can see the relationship between the two loops. This loop is not in the resource file. Here is an explanation of what is being achieved. Numbers are added to multiple columns and the numbers increase by one with each column. As you will see in the VBA code, we are running this procedure to work on the next worksheet (Sheet2) as our first worksheet is getting a little cluttered by now. Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code. Sub Nested_For() 'adds numbers to multiple columns 'increases numbers by 1 for each column Dim MyRows, MyColumns As Integer For MyRows = 1 To 30 132 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 133 For MyColumns = 1 To 5 Sheet2.Cells(MyRows, MyColumns).Value = MyRows + MyColumns Next MyColumns Next MyRows End Sub

Nesting Loops Example 4 This is our final example for this chapter. We are looping through 30 rows over five columns and checking for the value of 20. If the value exists then we are changing the interior background colour of the cell to yellow. Again this procedure is run on our next worksheet (Sheet2). Copy and paste it into the module. Put your curser inside the procedure and push F5 to execute the code.

Sub Nested_For2() 'loops through multiple columns 'checks for a value and colors the background if it exists Dim MyRows, MyColumns As Integer Dim Rng As Range For MyRows = 1 To 30 For MyColumns = 1 To 5 Set Rng = Sheet2.Cells(MyRows, MyColumns) If Rng.Value = 20 Then Rng.Interior.ColorIndex = 6 End If Next MyColumns Next MyRows End Sub

133 | P a g e


Chapter 11: Understand VBA Loops by Examples Page | 134 Comment and caution 1. As a final comment a word of caution needs to be given with regard to loops in your VBA procedures. Loops can the slow down a workbook if they are poorly constructed or if the data to be looped is extensive. 2. Care should be taken in creating a user-defined function (UDF) for a loop. If this function is used as part of a cell formula in an uncontrolled fashion the workbook can perform poorly. 3. In particular you we need to avoid what are known as endless loops. These will start a loop and the condition is never met. We hope that the planet Earth loops in this manner but not our VBA procedures.

In conclusion All of the examples above are used to help us to understand how loops function and the various types of loops available to us. The goal is to help us to see the concepts that we can now use to making our VBA application a far more rewarding experience for our end users. An experience that will save both you and any users of your applications lots of time and effort. Stay in the loop.

134 | P a g e


Page | 135

Chapter 12: Basic Error Handling OVERVIEW        

Why Use Error Handling? How to Configure Error Settings How to Reset Errors Three Types of VBA Errors When to use error handling What is the VBA Debug? Understanding the On Error Statement Good habits to minimize errors

Open the resource file Open the resource file that came with this eBook and navigate to the Error Handling worksheet. This will enable you to test each of these examples.

135 | P a g e


Chapter 12:

Basic Error Handling Page | 136

Provide an error free environment

We all want our applications to appear error free, providing the user with a friendly environment in which to work. It is very disconcerting than to open an application and to be greeted with the VBA Debug message box. We would like to be given the thumbs up by those using our applications but when this occurs the thumb points the other way. Skilful use of error handling can make the difference between a problem plagued program and a successful user-friendly application. Error handling should successfully anticipate potential problems and guide the user past these without incident.

Why Use Error Handling? Here are some good reasons to consider the use of error handling in your VBA application development.    

Provides a user friendly experience Traps and deals with errors Allows for continued seamless work flow Communicates necessary information with the user

How to Configure Error Settings Before we get started make sure that your Excel application has the right options checked to enable you to deal with errors properly. To access this dialogue box in Visual Basic Editor choose Options from the Tools menu and then click on the General tab. 136 | P a g e


Chapter 12:


Notice the section Error Trapping. Set the option Break on Unhandled Errors.

How to Reset Errors With all compilation(compile) errors and if we choose the debug option with Run-time errors the Visual Basic Editor is going to go into debug mode. Generally the line of code causing the error will be highlighted. Before you can proceed with running your code and you edit your code you should reset the Visual Basic Editor. From the menu bar choose Run and then click Reset. Note: Reset clears the execution stack and module level variables and resets the project.

137 | P a g e


Chapter 12:


Three Types of VBA Errors Errors fall into three types. The three types of errors are listed below. 





Syntax errors (Compile Errors) o Compile errors occur as the code is being compiled so there is no error handling for compile errors. Run-time errors o We can effectively control Run-time errors with error handling as we will demonstrate shortly. First let’s look at these three types and show you an example of each. Logic errors (Bugs) o Logical errors are generally not noticed by the VBA editor.

Syntax errors (Compile Errors) When these errors occur they are recognised by the VBA compiler as being illegal. The error occurs before the procedure starts to run. This is a compilation error. In the procedure below we have the wrong data type declared which will produce a compilation error. When this type of the error occurs VBE will go straight into debug mode highlighting the offending piece of code and presenting you with the message box. The only options given are Help and OK which will take you to the offending code. This is called a fatal error as the execution is terminated. Note: You will need to reset the VBE before you proceed. Run the code in the sample resource file that you downloaded. 138 | P a g e


Chapter 12:


VBA Workbook Example 1

Sub Compile_Error1() 'compile error 'wrong data type Dim Rng As String Rng = Range("D4") Rng.Value = 20 End Sub

Run-time errors A Run-time error can occur as your code is being executed. The code will stop running, the Visual Basic dialog box will appear with three options (End, Debug and Help), and if you choose Debug, the line that is causing the error will be highlighted. In the code below the Object variable is not set, causing the procedure to fail at Run-time. Again a fatal error is indicated. When presented with these options End will ignore what has happened Debug will take you to the offending line of code. Run the code in the sample resource file that you downloaded.


139 | P a g e


Chapter 12:


Sub Run-time_Error2() 'Run time error Dim Rng As Range 'Object variable not set Rng = Range("D4") Rng.Value = 20 End Sub

Logic errors Logical errors are the bugs in your VBA code. What do we mean by bugs in our code? The code will run successfully but the outcome will be false or inaccurate. The way that these errors occur is by missing logic in the code of the procedure. Hence the term logical errors. These errors are often difficult to pinpoint. To find these errors we need to step through the code using the F8 key. They are not compile or run-time errors so they often slip under the radar. As an example you might assign the wrong object range reference to a variable which will skew the data giving inaccurate results.

When to use error handling If you’ve been working with VBA for a while you’ve probably heard the expression “every procedure needs to have error handling”. There are times when a basic procedure may not

140 | P a g e


Chapter 12:


need error handling. There are no rules here. Consider when to add error handling to the application, for this reason. In the development stages of the application it might be wise to comment out (add an apostrophe before the line) your error handling or add it only after you are confident that your procedure is free of bugs and Run-time errors. The reason for this is that VBE Debug does a great job in highlighting your Run-time errors, making your development process much easier. You are letting the VBE Debug do the work of finding your errors for you. At this stage the question could be asked, what exactly is the VBA Debug?

What is VBE Debug? The Debug is the default error handling of your Visual Basic Editor. When VBA encounters an error then it reacts in one of two ways. A. It stops at the line of code that causes the error and highlights that line. It will tell us the type of error, Compile or Run-time, the error number and the message indicating what the error is. This is very helpful. B. If it finds error handling in the procedure the debug will execute that error handling and will not highlight the offending code. If the Debug toolbar is not showing choose View / Toolbars / Debug. Have a look at the features available here by hovering you mouse over the icons. Use Microsoft help for more detailed information about these features. Press the F1 key to activate the help menu when needed.

Open the sample workbook and follow along In the example provided we are going to calculate the average sales per customer and show the result in a message box. I will run you through all of the errors that could occur and solutions that can be used to deal with them.

141 | P a g e


Chapter 12:


If all runs correctly you should expect to see a message box with the result of total sales divided by customers to give the average sales per customer. As shown in the illustration below.

We need to understand the On Error Statement. Here is an explanation for the three forms of this statement.

Understanding the On Error Statement At the core of error handling is the On Error statement. The On Error statement can be used to control Run-Time errors in VBA. We can use this statement to tell the Visual Basic Editor what to do when an error occurs. There are 3 forms of the statement. 1. On Error GoTo 0 2. On Error Resume Next 3. On Error GoTo [label or line] We will dissect them on at a time.

1. On Error GoTo 0 Statement On Error GoTo 0

Description Disables any enabled error handler in the current procedure.

The first On Error statement is the default setting for VBA. On Error GoTo 0 will reset VBA to the standard Run-time error message box. This is the same as if you had no error 142 | P a g e


Chapter 12:


handling. The default message box is illustrated below showing that the procedure has been terminated giving us an option to End the error handler or to choose Debug and go to the line of code causing the error if it is in the same procedure. The example below clears the value for customers and then attempts to divide Total Sales by Customer (0) thereby causing Run-time Error 11. The execution of the code is terminated at this line Result = Sales / Customers.

Run the code in the sample file that you downloaded.

VBA Workbook Example 3 Sub Error_Default3() 'Default error handling 'Declare variables Dim Sales As Range, Customers As Range Dim Result As Long 'set range objects as variables Set Sales = Range("C4") Set Customers = Range("D4") 'Clear the value to cause the error Customers.Value = "" 'div#0 error here Result = Sales / Customers 143 | P a g e


Chapter 12:


'result in the message box MsgBox Result End Sub Note: The two valuable pieces of information and the three options given to us in the message box. We are told that it is a runtime error (91) (Division by zero) and that we can ignore it by clicking End, find the cause by selecting Debug or go to the Help menu. This is extremely helpful.

2. On Error Resume Next Our second On Error statement is the On Error Resume Next statement. This statement skips the error and continues to process the code. There are times when we can use this very effectively by fixing the error if it occurs and then moving to the next statement. However this statement skips the error and does not fix it. Caution should be used in using this statement as it may allow some very undesirable effects to go uncorrected. Note: You should reset to default error handling as soon as possible with On Error GoTo 0. As you run the code below you will see that the error is skipped and our procedure continues to run and executes the result in a message box. Run the code in the sample file that you downloaded.

144 | P a g e


Chapter 12:


VBA Workbook Example 4 Sub Error_Resume_Next4() 'Resume next error handling Dim Sales As Range, Customers As Range Dim Result As Long 'Error handler to ignore the error On Error Resume Next 'set range objects as variables Set Sales = Range("C4") Set Customers = Range("D4") Customers.Value = "" 'div#0 error here Result = Sales / Customers 'result in the message box MsgBox Result On Error GoTo 0 End Sub If you were to check the Microsoft help files you would see this table of information. Statement

Description

On Error Resume Next

Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred where execution continues. Use this form rather than On Error GoTo when accessing objects.

3. On Error GoTo [label or line] On Error GoTo [label or line] instructs VBA to transfer to the line following the label line when an error occurs. All of the code between the error and the line following the label is skipped. In the example below a message box occurs notifying the user that a problem exists on the worksheet. The same label name must be used in our code at the start and in the error block. You can use any single word name that you want. This statement can be used effectively to control and correct Run-time errors.

145 | P a g e


Chapter 12:


Note: We need to exit the procedure prior to our label this will prevent the error handler from running if no errors occur. To do this we use the VBA keywords Exit Sub.

Statement

Description

On Error GoTo [Label]

Enables the error-handling routine that starts at line specified in the required line argument. The line argument is any line label or line number. If a run-time error occurs, control branches to line, making the error handler active. The specified line must be in the same procedure as the On Error statement; otherwise, a compiletime error occurs.

VBA Workbook Example 5 Run the code in the sample file that you downloaded.

Sub Error_Handler5() 'Error hanldling with a label Dim Sales As Range, Customers As Range Dim Result As Long 'Error handler label to exit and give a message 146 | P a g e


Chapter 12:


On Error GoTo Errhandler: 'set range objects as variables Set Sales = Range("C4") Set Customers = Range("D4") 'Clear the value to cause the error Customers.Value = "" Result = Sales / Customers 'result in the message box MsgBox Result 'Exit the procedure Exit Sub 'Error handler label Errhandler: Msgbox “Values are missing from the worksheet” End Sub

Appling Generic VBA Error Handling You may wish to use generic error handling in your code that fits most situations. To do this you could set the error handler with a label as shown above and replace the message box section with the code that you see below. This error message presents a couple of small messages along with the error number and a description to the user. It then directs them to take specific action. Note: Change the text between the double quotes to suit your application needs. It is helpful to include the procedure name and module in the text. VBA Workbook Example Generic MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _ & Err.Number & vbCrLf & Err.Description & vbCrLf & _ "Please notify the administrator"

147 | P a g e


Chapter 12:


VBA Resume Statement Explained By combining the On Error Go to Label statement with the Resume statement we are able to correct errors with our error handler block. The Resume Statement takes on three forms. It tells VBA to resume the procedure at a specific point in our code.   

Resume - From the error Resume Next - Immediately after the error Resume [line or label] - From a label Let’s look at these one at a time and combine the Resume Statement with On Error Go to Label.

Resume In our next example below when the error occurs we are directed to the label and an input box is presented to enable the user to correct the error by adding a number to customers. Then we use the Resume Statement to run the code with the values added by the user. You will be only permitted to use the Resume Statement within an error handling block. Run the code in the sample file that you downloaded.

148 | P a g e


Chapter 12:



Sub Error_Handler_Resume6() 'Resume error handing Dim Sales As Range, Customers As Range Dim Result As Long 'Error handler label On Error GoTo Errhandler: 'set range objects as variables Set Sales = Range("C4") Set Customers = Range("D4") 'Clear the value to cause the error Customers.Value = "" 'div#0 error here Result = Sales / Customers 'result in the message box MsgBox Result 'Exit the procedure Exit Sub 'Error handler label Errhandler: 'Correct the error with an input from the user If Err.Number = 11 Then Correct = Application.InputBox("Add the number of Customers:", Type:=1) Range("D4") = Correct 'Resume the code from the error Resume End If End Sub

149 | P a g e


Chapter 12:


Resume Next The Resume Next Statement will cause the execution to recommence at the line immediately following the error. If you were to use this statement in the error handling block you would need to both repair the error and run the line of code to process the result.

Resume [line or label] A preferred way to achieve the desired result would be to use the Resume Label in the error handling block. In the example below we use the label Fixit: as the point in the code to return to and in the error handler we call a statement Resume Fixit: to take us back to that point in the code. Note: We are using two labels and two statements. The second statement is called from the error handling block. Run the code in the sample file that you downloaded. VBA Workbook Example 7

Sub Error_Handler_Resume_Label7() 'Resume label Dim Sales As Range, Customers As Range Dim Result As Long 'Error handler label On Error GoTo Errhandler: 'set range objects as variables Set Sales = Range("C4") Set Customers = Range("D4") 'Clear the value to cause the error 150 | P a g e


Chapter 12:


Customers.Value = "" 'Resume label Fixit: 'div#0 error here Result = Sales / Customers 'result in the message box MsgBox Result 'Exit the procedure Exit Sub Errhandler: 'Correct the error with an input from the user If Err.Number = 11 Then Correct = Application.InputBox("Add the number of Customers:", Type:=1) Range("D4") = Correct 'Resume label Resume Fixit: End If End Sub

151 | P a g e


Chapter 12:


Good habits to minimize errors I have listed some of the core practises that will help to minimise errors.

1. Using error handling will enable you to trap and fix errors as they occur in your code. It is a good habit to get into to make sure that we use some form of error handling in our applications. 2. Keeping our procedures a manageable size and indenting the code will also aid in minimising errors. It is better to have many small procedures to accomplish our goal than one large one. Not only will it minimise errors for us, but our code will run more efficiently. 3. Trial your application with a single or multiple users and log all errors that occur. Everybody approaches Excel with different habits and you’re more likely to pick up the full spectrum of possible errors this way. The approach that I have noticed that often fails is to try to correct problematic code after it is released to all users. These practices will have the end users giving the thumbs up.

152 | P a g e


Page | 153

Chapter 13: Build your First Userform Database OVERVIEW        

What is a Userform? Why use a Userform? Userform Project Insert a Userform Userform Properties Toolbox Adding Controls Writing our code

What is a Userform? A userform is a dialog box that you create and customize in the Visual Basic Editor. It is an object that can contain VBA code and events. You can add a large variety of controls to the userform.

153 | P a g e


Chapter 13:

Build your First Userform Database Page | 154

Open the resource file Open the resource file that came with this eBook and navigate to the Userform worksheet. This will enable you to test each of these examples.

Here is the form that we will be using to collect and add our receipts.

154 | P a g e


Chapter 13:


Why use a Userform? A userform presents a user-friendly interface. From this platform you can control the options available to the end-user. You do not have to go to the worksheet. It is often good to prevent this. Userforms enable a variety of possible uses that otherwise would be difficult to achieve at a sheet level.

Common Userform Uses        

Data entry control Selecting ranges Splash screens Filtered worksheet data Add multiple pages to the userform Menu for your application Popup Calendars Navigation for all sheets

155 | P a g e


Chapter 13:


In harmony with the spirit of Online PC Learning we will be learning how to use userforms by developing a project.

Userform Project Our project is “My Tax Receipts”. It will enable you to add and sort your tax receipts for years into a database without leaving the userform. You can then filter the tax receipts between dates from an interface sheet. This is a great little project to get started with learning to use userforms in VBA. The illustration at the beginning of this chapter shows the interface sheet and the userform ready for action.

Adding three dynamic named ranges Before we get started we need to add three dynamic named ranges to the workbook. Change the name of Sheet3 in your workbook to Lists and in cells D4/H4/F4 add the headers that you see below. 1. Tax Category

2.

Stored Location

3.

Company

Note: Make sure that there is data under the headers as our named ranges are going to be dynamic. If there is no data for VBA to find, an error will occur. Copy the information from the illustration.

These three dynamic named ranges will be the values that populate our three combo boxes so we will add them at the beginning of this project. From the ribbon on your Excel spreadsheet choose Formulas/Name Manager/New then add the name below and the formula to create three dynamic named ranges. Category/Company/Location Category 156 | P a g e

=OFFSET(Lists!$D$5,,,COUNTA(Lists!$D$5:$D$100)) Copyright © 2013 Trevor Easton Online PC Learning

Chapter 13:


Company

=OFFSET(Lists!$H$5,,,COUNTA(Lists!$H$5:$H$100))

Location

=OFFSET(Lists!$F$5,,,COUNTA(Lists!$F$5:$F$100))

Here is the link to the website article for understanding dynamic named ranges Title: Excel Dynamic Named Ranges: Add Power and Punch to Excel Link: http://www.onlinepclearning.com/excel-dynamic-named-ranges/ Note: You can use a static named range however you would need to redefine it each time new data is added.

Visual Basic Editor Previously I have discussed key elements of the Visual Basic Editor in Chapter 2. If you have not read that chapter or the information is a little hazy then please revisit this chapter.

Insert a Userform How to create a userform! Open Microsoft Excel and use the shortcut key Alt + F11 to open the Visual Basic Editor. 1. Click the Insert Tab 2. Choose Userform 3. The userform will appear. The white boxes on the side of the userform will enable you to resize the form to your approximate needs. 4. VBA will hide the toolbox if it thinks you are not using it. Click inside the userform and it should appear. If it is not visible then choose the View tab and click Toolbox. 5. From the View tab click Properties if it is not visible We will need to have quick access to the Toolbox and the Properties dialog box.

157 | P a g e


Chapter 13:


Userform Properties When you click inside the userform you should see a dotted border around it. When this occurs the properties dialog box will display the userform properties. For our project we want to change the name of the userform from Userform1 to a name that will help us to understand the userform and its purpose. To do this we will use a basic naming convention. When working in VBA you should use a naming convention that is consistent. In the properties box name the userform frmTax_Receipt (no spaces allowed) and add a caption My Tax Receipts (spaces are allowed here) Here is a sample of the naming convention that is commonly used.    

Userforms start with frm Text boxes start with txt Combo boxes start with cbo Labels start with lbl (if you are not referring to the labels in you code there is no need to give them a special name) The only rules that apply are not to use a reserved VBA keyword nor add spaces in the name. Note: At this stage of the project your userform should now have a name and a caption. If you want to see what the userform will look like in Excel then push the F5 key or click the Run button in the menu at the top. To return to the VBE, close the userform by clicking the x in the top right hand corner of the userform.

158 | P a g e


Chapter 13:


Note the dotted border when the userform is selected and the new name for the userform in the Project Explorer.

Toolbox The Toolbox contains all of our controls that can be added to the userform. Place the curser over the controls to reveal the name of the control. If you need to use controls that are not in the toolbox then right click inside of the toolbox and choose Addition Controls. The Additional controls dialog box will appear. As you scroll down you will see some controls have an (x) in the box on the left hand side. These are the available controls in the toolbox. To add additional controls add an (x) in the box and click OK. To remove a control, click inside the square to remove the x and then click OK. Note: Not all of these controls are available to VBA Excel.

159 | P a g e


Chapter 13:


Adding Controls Adding Labels We have 12 labels to add to the userform. To add a label click on the label in the Toolbox and then return to your Userform and click down on the left mouse button and drag the label over the form until the control is the desired size. Here are nine labels that you will need to add to the Userform for now. You will notice that spaces are allowed between the words in the labels and that you are able to change the font size and colour and style to suit your taste.

160 | P a g e


Chapter 13:


Now that you have your label, right click and choose Properties. You will be able to change the label name but most importantly we want to change the Caption. While the properties box is open set the size of the font that you want and the font type and style. Note: In the properties box you are able to select the way that the properties for controls in our userform are formatted and behave. You have the choice of Alphabetic or Categorized tabs at the top of the Properties dialog box to choose from. Top Tip: Once you have formatted one label right click and copy it eleven times onto the form. Align the labels to suit and change the Captions to reflect the nine labels that you see in the illustration above. The labels will not be referenced in the VBA project.

As well is the nine text labels we have three labels that will indicate the fields that will be mandatory. These labels simply have an asterisk inside of them that is formatted to a larger font size and colored red. Create one and copy the other two and move them to the correct place on the userform, as shown in the illustration below.

161 | P a g e


Chapter 13:


List for control names As you can see from the illustration below we have: 12 4 3 2 3 1

labels text boxes combo boxes option buttons that are grouped command buttons image frame

This gives us a total of 25 controls on the Userform. Make sure that the text boxes, combo boxes, option buttons and command buttons have exactly the same names as you see below or the VBA code that you will be adding will not work.

162 | P a g e


Chapter 13:


Adding Text Box Controls To add a text box, click on the text box icon in the Toolbox and then return to your Userform and click down on the left mouse button and drag the text box over the form. The only thing that we need to do to our text box controls is to add the correct name to them. Note: Copy the control names from the illustration above.

Adding Combobox controls Add the combo box controls to the userform in the same manner as the text boxes and labels. Make sure that the name is correct for each of the three combo box controls. As you can see from the illustration below we are going to populate each combo box with a dynamic named range that you previously added into the name manager. The illustration below shows where the dynamic named range should be typed in the RowSource of the properties of the combo box. Make sure that the ranges have data in them. VBA would be looking for a named range that as yet does not exist if the ranges are empty.

You can set the option to allow a user to add different values from those in the range to a combo box if you wish. In the properties select Match Entry and choose 2MatchEntryNone (illustration below). If you allow this then analyzing the data at a later date can be a little difficult. 163 | P a g e


Chapter 13:


Option Buttons As well as assigning the correct names to our option buttons we need to group the two buttons together. To accomplish this select both buttons by dragging the curser over both of them or by holding down the shift key and clicking on the both option buttons. Right click on the two selected buttons and from the drop-down menu choose Group.

Adding a Picture If you wish to add a picture or a logo to your userform then follow these two steps. 1. First prepare the picture or logo and make sure that it is a very small image. The image that I used in this Userform is a 7 kB. I will briefly describe how I formatted this image. This image was formatted by opening a Microsoft PowerPoint and inserting clipart or your image (logo) that you feel would be appropriate to the theme of this application. The background was then removed in PowerPoint and the image was saved to a location on the desktop by right clicking and choosing Save Image As. On the desktop the image was right clicked and opened in Microsoft Picture Manager where it was resized to 7 kb. 2. Second add an image control to the userform. As you can see from the illustration below we can add the picture by clicking Picture and then locating our image to import it into the control. Note the two properties below: 164 | P a g e


Chapter 13:


a. PictureAlignment b. PictureSizeMode. These properties will allow you to manipulate the image within the control.

Tab order To set the tab order for the controls in your userform, click on the View Tab and choose Tab Order (illustration below). Move the controls up and down to suit the order that you would like the Tab key to move through them.

165 | P a g e


Chapter 13:


Writing the VBA Code The first thing that we want to do is to run a small piece of code to open the Userform from a button on the worksheet. From the Insert tab in the VBE choose Module. Into the newly created module add this small piece of VBA code.

Sub Showme() frmTax_Receipts.Show End Sub Name the three worksheets Interface, Database and Lists. On the Interface sheet insert a rectangle shape then right click on the shape and choose Assign Macro. Select the macro “Showme” and click OK.

Test the userform Click on the button and the Userform should appear above the worksheet. Do not move forward with the rest of this project until this is successfully working. Check that the three combo boxes have the values contained in the three dynamic named ranges.

Adding our code In the VBE double-click on the userform in the Project explorer. Now double-click on the cmdAdd button. The click event procedure below will appear in the VBE. Private Sub cmdAdd_Click() (VBA code goes here) End Sub Our VBA code which will take the information from the userform and add it to the database list will appear between these two lines.

Six things that this procedure will accomplish: 1. Declare our variables 166 | P a g e


Chapter 13:


2. Add 2 variables 3. Add 2 If statements to check that a date is added and that our mandatory fields have been filled in. 4. Move the information from the Userform to the database. 5. Sort of the database 6. Reset the Userform

Declare our variables This code is dimensioning our variables which simply means telling VBA the data type for each variable. Dim ws As Worksheet Dim addme As Range

Add the variables These two variables are for the worksheet that contains the database and for the next empty row in our database list. Set ws = Sheet2 Set addme = ws.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0)

Add two If statements to check that a date is added and that our mandatory fields have been filled in. A common mistake that many make with Userforms is to add a date incorrectly. We are going to check the date is in fact a date before we allow the user to proceed. If Not IsDate(txtDate.Value) Then MsgBox " The date field must be a proper date", Visual Basic Editorxclamation, "Date format error" Me.txtDate.Value = "" Me.txtDate.SetFocus Exit Sub End If

167 | P a g e


Chapter 13:


Our Userform has three mandatorily fields that need to be checked to see if a value exists in each before we can process the userform. The If statement below checks these three fields.

If Me.txtDate.Value = "" Or Me.cboCompany.Value = "" Or Me.txtAmount.Value = "" Then MsgBox "There is insufficient data. Madatory fields must be added (*)", Visual Basic Editorxclamation, "Mandatory fields are incomplete" Exit Sub End If

Move the information from the Userform to the database. We reference our variable addme to find the next available cell to add our first value from the userform. When we find that cell we add our txtDate and then offset one column in the same row and add our cboCompany then offset one column and add txtDescription then offset one column and add our cboCategory et cetera. When we get to our option button we run an If /ElseIf /Else statement to check and to send the right value to our database. Notice that we format the txtDate. You will need to change this depending on the location in which you live in the world. And we also format our txtAmount as currency. With ws 'set the date format to suit your area addme.Value = Format(txtDate.Value, "mm/dd/yy") addme.Offset(0, 1).Value = Me.cboCompany.Value addme.Offset(0, 2).Value = Me.txtDescription addme.Offset(0, 3).Value = Me.cboCategory addme.Offset(0, 4).Value = Format(Me.txtAmount.Value, "$##,###.00") If OPT1.Value = True Then addme.Offset(0, 5).Value = "Paper Copy" ElseIf OPT2.Value = True Then addme.Offset(0, 5).Value = "Scanned Copy" Else addme.Offset(0, 5).Value = "" End If addme.Offset(0, 6).Value = cboLocation addme.Offset(0, 7).Value = txtRemarks End With 168 | P a g e


Chapter 13:


Sorting the database Living in the real world we know that our dates are not always going to be added in consecutive date order. So to deal with this problem we are going to sort the data each time we add a new tax receipt. The data is sorted by dates and in ascending order. ws.Select With ws ws.Range("D4:K10000").Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlGuess End With 'return to sheet Sheet1.Select

Resetting the Userform There are three ways to reset the Userform. 1. Add each control name and assign blank as the value a. Me.txtDate.value = ”” 2. Reference each control type in the Userform and assign blank as the value a. For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then ctl.Value = "" Next ctl 3. Close the Userform and reopen it a. Unload Me frmTax_Receipts.Show

We are going to choose the final option (3) because it is the easiest.

Below is the complete cmdAdd procedure Remember this code goes into the userform not the module. Double-click on the cmdAdd button to access the code for the userform. Take the time to look through the code again and make sure that you understand exactly what each part of the code is doing. 169 | P a g e


Chapter 13:


Private Sub cmdAdd_Click() 'dimention the variable Dim ws As Worksheet Dim addme As Range 'set the variable Set ws = Sheet2 'set variable for the destination Set addme = ws.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) 'hold in memory Application.ScreenUpdating = False If Not IsDate(txtDate.Value) Then MsgBox " The date field must be a proper date", Visual Basic Editorxclamation, "Date format error" Me.txtDate.Value = "" Me.txtDate.SetFocus Exit Sub End If If Me.txtDate.Value = "" Or Me.cboCompany.Value = "" Or Me.txtAmount.Value = "" Then MsgBox "There is insufficient data. Madatory fields must be added (*)", Visual Basic Editorxclamation, "Mandatory fields are incomplete" Exit Sub End If 'send the values to the database With ws 'set the date format to suit your area addme.Value = Format(txtDate.Value, "mm/dd/yy") addme.Offset(0, 1).Value = Me.cboCompany.Value addme.Offset(0, 2).Value = Me.txtDescription addme.Offset(0, 3).Value = Me.cboCategory addme.Offset(0, 4).Value = Format(Me.txtAmount.Value, "$##,###.00") If OPT1.Value = True Then addme.Offset(0, 5).Value = "Paper Copy" ElseIf OPT2.Value = True Then addme.Offset(0, 5).Value = "Scanned Copy" Else addme.Offset(0, 5).Value = "" End If addme.Offset(0, 6).Value = cboLocation 170 | P a g e


Chapter 13:

Build your First Userform Database Page | 171 addme.Offset(0, 7).Value = txtRemarks

End With 'sort the data Sheet2.Select With ws ws.Range("D4:K10000").Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlGuess End With 'return to sheet Sheet1.Select 'reset the form Unload Me frmTax_Receipts.Show 'update the sheet Application.ScreenUpdating = True End Sub

Close the Userform Double-click on the cmdClose button and add the VBA code between the two lines. Unload Me

Private Sub cmdClose_Click() Unload Me End Sub

Reset the Userform This code will reset the userform Private Sub cmdReset_Click() Unload Me frmTax_Receipts.Show

171 | P a g e


Chapter 13:


End Sub

Set up the Database Worksheet On the database sheet, add these headers to the cell references shown below. (Refer to illustration below) Cell D4

Receipt Date

Cell E4 Cell F4 Cell G4 Cell H4 Cell I4 Cell J4 Cell K4

Company Description Tax Category Amount Type Location Remarks

Test the userform with data Return to the interface sheet and click on the button that you added to previously to open your Userform. You will now be able to add values to your Userform and when you click the Add button those values should be moved to the database. Test all the functionality within the code and make sure that the mandatory fields work. Test the form and data entry thoroughly. Well that’s it for our Userform part of the project. I am now going to show you how you can filter that database to be able to see all of the tax receipts between any two given dates. This will give our program more functionality. We could use another Userform to do this but for now we will run this at a sheet level.

172 | P a g e


Chapter 13:


Setting up The Interface Filtered results (CopyTo range) On the interface sheet add exactly the same headers that you added to the database sheet but this time start at cell reference C7. Note: These headers must have exactly the same words that you used in the database. It is best to copy and paste them to ensure accuracy. This is where your filtered data will be copied to. It is not necessary to visit the database.

Start and finish dates on the Interface Worksheet Directly above these headers add these values and formats to the cells shown below. This is where you will enter your starting and finishing dates. Cell C5

Start Date

Cell D5

Finish Date

Cell C6

Format this cell as a date type

Cell D6

Format this cell as a date type

Advanced filter criteria block To the right of these headers add the values below to the cell references and then the two formulas to the cell references indicated. The text must be exact as this will form our criteria block for the advanced filter. 173 | P a g e


Chapter 13:


Cell M5

Receipt Date

Cell N5

Receipt Date

Cell M6

=If(C6="","",">" &C6)

Cell N6

=If(D6="","","<" &D6)

These two formulas will pick up your start and finish date and add a greater than (>) or a less (<) than to each date. In addition if the dates are blank the cells will be blank.

Running the advanced filter Return to the VBE and double click on the module in the Project Explorer and insert the VBA code that you see below. Note: This goes into the module not the userform. Sub Filterme() Sheet2.Range("D4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("M5:N6"), CopyToRange:=Range("C7:J7"), Unique:=False End Sub Go back to the interface sheet and insert a shape, right click on the shape and choose Assign Macro and select the macro “FilterMe”. After you have added multiple values to the database, run some tests with the advanced filter by adding start and finishing dates.

In Conclusion In this project we have learned many things about how a Userform works and how we can use it to speed up our data entry process. This is just the tip of the iceberg. You will find 174 | P a g e


Chapter 13:


that you will be using Userforms all the time in order to enhance the user experience, control data movement and data entry in your applications.

175 | P a g e


Page | 176

Chapter 14: Developing your first Application OVERVIEW       

Where do I start? Great Idea – Bad Design I’m going to share with you my recipe for success. In the line of fire Here is the nitty gritty stuff Map the application and its development Develop an effective user interface

How do we develop bullet proof Excel Applications? As your VBA skills improve you will be developing your own Excel applications. This article will set you on the right course. The most common approach is to get an idea for what we want to do and then open Excel and start to add the data and build the application, through a process of evolution. I am sure we have all done that. Is this the most effective way to develop an application? Will it produce the best results? Does it underpin good development practice?

Where do I start? I have a lot of applications sent to me where people are having trouble accomplishing their goals. Sometimes the solution is simple but impossible to implement in the application because of the initial application design. The thoughts of many can be summed up with the idiom “I feel like I’m banging my head 176 | P a g e


Chapter 14:

Developing your first Application Page | 177

against a brick wall”. It feels good when you stop. The way to stop is to start with good design. Have you ever played the game pin the tail on the donkey? You have to accomplish this goal blindfolded and disorientated. Don't start developments blind, don't go in blindfolded and have a stab in the dark.

“Don’t play pin the tail on the donkey with your work” Go in with your eyes wide open and realise that your focus early in the project’s development will prevent future failure. A stab in the dark probably won't hit the spot. I remember the first application that I developed was a patient handover and transfer program that enabled a ward in a very large hospital to be able to transfer patients from bed to bed and from ward to ward. I spent two weeks researching how I could accomplish a multitude of features that were necessary for this application to work. I listed all off the requirements and developed little test files for each of the sections. I'm happy to say after many years that application is still in use to this day and has saved them huge amounts of time and paperwork. That set a pattern for me and gave me the encouragement that I needed to continue developing applications. It really is a good feeling when you see something that you have developed being used by multiple users and saving heaps and heaps of time.

Great Idea – Bad Design Good design is essential for successful outcomes or as Mr Franklin said “An ounce of prevention is worth a pound of cure”. The concept for our application can be excellent but if the foundation is poor or the structure it is built upon is substandard then we are setting ourselves up for catastrophic failure with our Excel applications.

I’m going to share with you my recipe for success. I follow a set of guidelines when I am developing Excel applications, small or large. I will outline this process below. After reading this you may be thinking this is the long way to do things but let me assure you from my own bitter experiences that if you tick these boxes first then you will have a better chance of success with less heartache. A bullet proof application is the target. Aim for zero support and a pleasant user experience.

177 | P a g e


Chapter 14:


In the line of fire Remember that the finished app with all its bells and whistles will be in the line of fire. You will not believe what people can do. Many of the apps I have developed are in use as shared programs on network drives and accessed by multiple uses; some with little or no Excel skills. I think I can say I’ve seen it all, the good, the bad and the downright ugly. We can prevent many physical health problems by living a healthy lifestyle. It is not a lot different when it comes to Excel applications. Yes we need to have a development style that employs good Excel habits that avoid poorly designed applications. There are times when we need to use what I call “quick and dirty” files especially when we are testing. However our finished product needs to be free of deleterious material and be structured for a long user friendly life.

Here is the nitty gritty stuff Here are some simple steps that you can take.             

Contact all the stakeholders or client and users to find the scope for your application. Now make a list of all of the requirements. Look at what is currently being used. Estimate data volume over time. Choose the right platform. Draw the relationships on paper or create the flowchart on your computer. Use sound database principles. Add all the sheets from the design. Develop the core first. Provide an effective user interface. Test! Test! Test at every stage. Setup a user training program. Run a trial.

Let’s go through these one at a time. Contact the stakeholders or client and users to find the scope for your application (If applicable). Make sure that you are on the same wave length with the application owner and especially with the end users. Meet and discuss what is required. Be very specific and listen twice as 178 | P a g e


Chapter 14:


much as you talk. Ask for a list of the proposed outcomes for the application and a list of functions and requirements. Now make your own list of all of the requirements, starting from the most important. This will be your future check list and will assist with other key elements. 1)

Most important requirement Sub point 1 Sub point 2 Sub point 3

2)

Second most important requirement Sub point 1 Sub point 2 Sub point 3

Ask to look at what is currently being used to get a feel for what is needed. Have the current file that is in use sent to you. You will learn a lot from this file about what is required and more importantly about user habits. Estimate data volume over time. Do this now not later I have an Excel application to calculate data volume. Try to estimate how much data will be entered daily then multiple this number by your expected life of the application. 150 rows daily over 5 yrs. = approx. 300,000 rows 150 does not sound like much but 300,000 is a bit more significant Choose the right platform for the development. Ask the question. Will Excel handle this or is it a job for Microsoft Access? This will depend on what version of Microsoft Office you are running. With Office 2007, Microsoft changed from a binary format to an xml format. Hence the “x”. [xlsx] at the end of the file extension. This now permits greater volumes and less file corruption. But the point still remains, why spend days developing an application that will not handle the data volume or run the test of time.

179 | P a g e


Chapter 14:


Draw the relationships on a piece of paper or create a flowchart in PowerPoint.

This is the nuts and bolts of your application. Get out pen and paper and turn on your artistic flare. Have a look at the illustration above and below. As an example show where the data is stored and then the sheet that it will be filtered to and any other relationships that exist. This is ever so important because at this stage you are thinking about what processes you have available to accomplish the desired outcomes. If you cannot work out how it will work on a flowchart, then it will not work in the actual application. Don’t fall into the trap of saying, I’ll work that out when I get to it. Redesign the relationships now, in the design stage to facilitate the needs of the application. Map the application and its development

180 | P a g e


Chapter 14:


Set up a project development schedule. Stage the project and all its components. This can be simple or complex depending on the size of the application. This could be as simple as breaking the project into stages and days allocated for completion. Send this to the stakeholders and or client for review. Just to make sure that you are both on the same page. Use sound database principles 1. A big mistake that many make is in the way data is viewed and presented. 2. Set out your data in a flat file where possible. Think of rows as records and columns as fields. Set your data to run down the worksheet where possible. 3. Avoid merged cells, they are road blocks to analysis. 4. Filter your data first then move and analyse it in another sheet if you can. This will dramatically improve the performance of the application. 5. In your columns [fields] do not mix data types. For example don’t put text and numbers in the same column.

181 | P a g e


Chapter 14:


Now open Excel!!! Have you noticed how much work we have done and not one bit of it has been in Excel. Now our development will be with purpose and move along very quickly. We know the relationships and have a good idea of how it all fits together. Develop the most important aspects first and work toward the least important I guess this is just common sense. Remember if the engine doesn’t work it does not matter good looking car is. So get the core elements up and running first. Develop an effective user interface Sad to say this is often omitted with Excel programs. You can have the best application there is, but if consumer sentiment is negative, it will fail. If it can move global markets then I'm sure consumer sentiment can sink our little file. Think of the user’s experience and ask “Is it clearly defined how to operate this program?” Is there a help menu available? Get some fresh eyes to have a look at it and ask for honest feedback. Train the trainer I use a video set to assist with this. Make sure you do not throw the end user in there without any training. Remember we are all a little intimidated by new things and especially things we do not understand. The human response is often to degrade or even belittle things that may be presented as intimidating. Offer personal training and follow up with support contact if possible. Test! Test! Test at every stage. Try to break it. Find the weaknesses and develop strategies to overcome or work around them. Testing can take a long time but is well worth the heartache that an untested application can cause. Don’t fall into the trap of thinking that you can put the application to use and fix problems as they occur. You will lose the trust and confidence of the end users.

182 | P a g e


Chapter 14:


Run a trial When the application is completed and working as you want, then run a trial deployment. If the application/s can be deployed in sections then try to do so. When you develop your next application why not try to use some of these suggestions.

183 | P a g e


In Conclusion Page | 184

In Conclusion There has been a lot to learn in this book. We have looked at the basic VBA Functions techniques and procedures.

Take a bow, pat yourself on the back, and shout yourself a cup of coffee. Why? “Because you are learning and learning is a gift that many forget to unwrap.”

Keep challenging yourself to learn new skills and link your skills together to build more complex scenarios.

Always remember this: If the work that you create pleases you then it is good work. It may not appeal to everybody but that does not matter.

You may remember the parable of the old man, the young boy and the donkey. No matter what they did, no matter how hard they tried it was impossible to please everybody. So don’t try to please all.

Be willing to accept criticism of your work because it is an open door to improvement. Feedback from others will help you identify weaknesses and strengths from which you can adjust and build your skills and expertise.

I wish you well in your learning endeavors and feel free to contact me on the website to pass on your feedback, comments and if necessary request support.

Remember that learning is a gift that many forget to unwrap. 184 | P a g e


In Conclusion Page | 185

185 | P a g e


Page | 186

Appendix - Macros in this eBook Message Box Macros

186

Variable Scope

187

If Function Macros

188

Select Case Macros

193

Message Box Macros

199

Input Box Macros

203

Loops Macros

209

My Tax Receipt Macros

223

186 | P a g e


Appendix - Macros in this eBook Page | 187

Variable Scope Procedure variable scope Sub Macro1 Dim MyVar as Range Set MyVar = Range(“A1”) MsgBox MyVar End Sub

Module level scope Dim MyVar as Range Sub Macro1 Set MyVar = Range(“A1”) End Sub

Project level scope Public MyVar as Range Sub Macro1 Set MyVar = Range(“A1”) MsgBox MyVar End Sub

187 | P a g e



If Function Macros If – Then Macro Sub If_Range_Single() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value > 50 Then Rng2.Value = 50 + Rng1.Value End Sub

If – The Multiple Sub If_Range_Multiple() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value > "" Then Rng2.Value = 20 + Rng1.Value If Rng1.Value < 50 Then Rng2.Value = 50 + Rng1.Value If Rng1.Value >= 50 Then Rng2.Value = 70 + Rng1.Value End Sub

If – The – End If Sub If_EndIf() Dim Rng1 As Range, Rng2 As Range 188 | P a g e


Appendix - Macros in this eBook Page | 189 Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value < 50 Then Rng2.Value = 50 + Rng1.Value End If End Sub

Multiple If - Then - End If Sub If_EndIf_Multiple() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") ‘Condition 1 If Rng1.Value <= 20 Then Rng2.Value = 20 + Rng1.Value End If ‘Condition 2 If Rng1.Value >20 Then Rng2.Value = 50 + Rng1.Value End If ‘Condition 3 If Rng1.Value >= 50 Then Rng2.Value = 70 + Rng1.Value End If End Sub

Input box example If - Then Sub If_Input() Dim Amount As Variant Amount = InputBox("Enter an Amount:") If Amount >= 50 Then 189 | P a g e


Appendix - Macros in this eBook Page | 190 MsgBox "Amount: " & Amount End If End Sub

Input box example If – Then – Else Sub If_Input_Else() Dim Amount As Variant Amount = InputBox("Enter an Amount:") If Amount >= 50 Then MsgBox "Amount: " & Amount Else MsgBox "The Amount is less than 50" End If End Sub

If – Then - ElseIf – Else Sub If_Range_ElseIf() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value < 20 Then Rng2.Value = 20 + Rng1.Value ElseIf Rng1.Value < 50 Then Rng2.Value = 50 + Rng1.Value Else Rng2.Value = 70 + Rng1.Value End If End Sub

190 | P a g e


Appendix - Macros in this eBook Page | 191 Nested If - Then – Else Sub If_Nested_Multiple() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value < 20 Then Rng2.Value = 20 + Rng1.Value Else If Rng1.Value < 50 Then Rng2.Value = 50 + Rng1.Value Else If Rng1.Value >= 50 Then Rng2.Value = 70 + Rng1.Value End If End If End If End Sub

If – And –Then Example 1 Sub If_And_Single() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value > 50 And Rng1.Value < 100 Then Rng2.Value = 50 + Rng1.Value End Sub

Example 2 Sub If_Input_ElseIf() 191 | P a g e


Appendix - Macros in this eBook Page | 192 Dim Amount As Variant Dim discount As Double Amount = InputBox("Enter an Amount:") If Amount >= 0 And Amount < 25 Then discount = 0.1 ElseIf Amount >= 25 And Amount < 50 Then discount = 0.2 Else discount = 0.3 End If MsgBox "Savings: " & discount End Sub

If – Or – Then If Rng1.Value < 50 or Rng1.Value >100 Sub If_Range_Single() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") If Rng1.Value < 50 Or Rng1.Value > 100 Then Rng2.Value = 50 + Rng1.Value End Sub If – Not – Then Sub If_MsgBox() If Not 500 < 499 Then MsgBox "500 is not less than 499" End If End Sub

192 | P a g e


Appendix - Macros in this eBook Page | 193 IIf Function Sub IIf_Msg() Dim rng As Range Set rng = Range("a1") MsgBox IIf(IsEmpty(rng), "Cell is empty", "Cell is not empty") End Sub

Select Case Macros Select Case - (Single condition) Sub Case_Single() Select Case Range("A1").Value Case 5: Range("B1") = 50 End Select End Sub

Select Case - (Multiple conditions) Sub Case_Multiple() Select Case Range("A1").Value Case 5 Range("B1") = 50 Case 10 Range("B1") = 100 193 | P a g e


Appendix - Macros in this eBook Page | 194 Case 15 Range("B1") = 150 Case 20 Range("B1") = 200 End Select End Sub

Select Case – Else Sub Case_Else() Select Case Range("A1").Value Case 5 Range("B1") = 50 Case 10 Range("B1") = 100 Case 15 Range("B1") = 150 Case 20 Range("B1") = 200 Case Else Range("B1") = "" End Select End Sub

Multiple Conditions (Comma separator) Sub Case_Comma() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case 5, 10, 15, 20 Rng2 = 50 194 | P a g e


Appendix - Macros in this eBook Page | 195 Case Else Rng2 = "" End Select End Sub

Multiple Conditions Between (To) Sub Case_To() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case 5 To 14 Rng2 = 50 Case 15 To 20 Rng2 = 100 Case Else Rng2 = "" End Select End Sub

Combining Multiple Conditions (To + Separator) Sub Case_To_Comma() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case 5 To 10, 15 To 20 Rng2 = 50 Case 30 To 40, 50 To 60 Rng2 = 100 Case Else 195 | P a g e


Appendix - Macros in this eBook Page | 196 Rng2 = "" End Select End Sub

Using Operators with Select Case Sub Case_Operators() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case Is < 20 Rng2 = 20 Case Is < 50 Rng2 = 50 Case Else Rng2 = "" End Select End Sub

Select case and Text Conditions Sub Case_Text() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case "Online" Rng2 = "Online is there" Case Else Rng2 = "Wrong text" End Select End Sub 196 | P a g e


Appendix - Macros in this eBook Page | 197 Option Compare Text Sub Case_Text_Compare() Dim Rng1 As Range, Rng2 As Range Set Rng1 = Range("A1") Set Rng2 = Range("B1") Select Case Rng1 Case "Online" Rng2 = "Online is there" Case Else Rng2 = "Wrong text" End Select End Sub

Alphabetically with operators Option Compare Text Sub Case_Text_Compare2() Select Case "robert" Case Is < "Peter" MsgBox "Is less than" Case Is > "Peter" MsgBox "Is greater than" End Select End Sub

Alphabetically between letters Option Compare Text Sub Case_Text_Compare3() Select Case "R" Case "a" To "m" MsgBox "between a and m" 197 | P a g e


Appendix - Macros in this eBook Page | 198 Case "n" To "z" MsgBox "between n and z" End Select End Sub

Combining Numbers and Text Conditions Option Compare Text Sub Case_Text_Number() Var = "R"

' 22 'must set a variable here

Select Case Var Case 1 To 10, "a" To "m" MsgBox "between a and m or 1 to 10" Case "n" To "z", 20 To 30 MsgBox "between n and z or 20 to 30" End Select End Sub

Combining Select Case (Magic combinations) Option Compare Text Sub Case_Combo() Set Rng1 = Range("A1") Select Case Rng1 Case 1 To 10 MsgBox "between 1 to 10 the number is " & Rng1 Case "n" To "z" MsgBox "between n and z the word starts with " & Rng1 Case Is >= 11 MsgBox "greater than 10 the number is " & Rng1 End Select End Sub

198 | P a g e



Message Box Macros Simple VBA message box Sub MessageBox() Range("A1").Value = 50 MsgBox "The value was added successfully" End Sub

Adding a variable for the buttons and icons Sub MessageBox() Dim ButtonType As Integer ButtonType = vbYesNoCancel + vbInformation + vbDefaultButton2 Range("A1").Value = 50 MsgBox "The value was added successfully", ButtonType End Sub

Displaying a Message Sub MessageBox() 'any code MsgBox "Hello Everybody" End Sub A Message Box with Three Buttons and In Icon Sub MessageBox() 199 | P a g e


Appendix - Macros in this eBook Page | 200 Dim ButtonType As Integer ButtonType = vbYesNoCancel + vbInformation ' any code MsgBox "The value was added successfully", ButtonType End Sub

A Message Box with an Option Sub DeleteCheck() Dim CheckDelete As VbMsgBoxResult CheckDelete = MsgBox("Are you sure you want to delete this data?", vbYesNo) If CheckDelete = vbYes Then Range("A1").ClearContents End Sub

Message Box Option and Confirmation Message Sub DeleteCheckMsg() Dim CheckDelete As VbMsgBoxResult CheckDelete = MsgBox("Are you sure you want to delete this data?", vbYesNo) If CheckDelete = vbYes Then Range("A1").ClearContents MsgBox "Your data has been deleted" End If End Sub

Message Box Default Button Sub DeleteCheckNo() Dim CheckDelete As VbMsgBoxResult CheckDelete = MsgBox("Are you sure you want to delete this data?", vbYesNo + vbDefaultButton2) If CheckDelete = vbYes Then Range("A1").ClearContents MsgBox "Your data has been deleted" 200 | P a g e


Appendix - Macros in this eBook Page | 201 End If End Sub

Message Box with If / Else Sub DeleteCheckValue() Dim CheckDelete As VbMsgBoxResult CheckDelete = MsgBox("Are you sure you want to delete this data?", vbYesNo + vbDefaultButton2) If CheckDelete = vbYes Then Range("A1").ClearContents MsgBox "Your data has been deleted" Else MsgBox "The value was not deleted" End If End Sub

Message Box with Three Options Sub AddCheckShow() Dim CheckAdd As VbMsgBoxResult Dim rng As Range Set rng = Range("A1") CheckAdd = MsgBox("Do you want to add the word (Yes)", vbYesNoCancel + vbDefaultButton2, "Add Value") If CheckAdd = vbYes Then rng.Value = "Yes" MsgBox "Your data is added. The Value is

" & rng

ElseIf CheckAdd = vbNo Then rng.Value = "No" MsgBox "Your data is added. The Value is

" & rng

Else rng.Value = "" 201 | P a g e


Appendix - Macros in this eBook Page | 202 MsgBox "The value is blank" End If End Sub

Message Box with the Select Case Function Sub AddCheckCase() Dim CheckAdd As VbMsgBoxResult Dim ButtonType As Integer Dim rng As Range ButtonType = vbYesNoCancel + vbInformation + vbDefaultButton2 Set rng = Range("A1") CheckAdd = MsgBox("Do you want to add Yes", ButtonType, "Add Value") Select Case CheckAdd Case vbYes: rng.Value = "Yes" MsgBox "Your data is added. The Value is

" & rng

Case vbNo: rng.Value = "No" MsgBox "Your data is added. The Value is

" & rng

Case vbCancel: rng.Value = "" MsgBox "The value is blank" End Select End Sub

202 | P a g e



Input Box Macros A Simple Input Box Sub InputSimple() 'Input variable ShowBox = InputBox("Type Your first name", "Add Name", "Type name here") End Sub

Add a name Sub InputName() 'Declare the variable Dim AddName As String 'Input variable AddName = InputBox("Type Your first name", "Add Name", "Type name here") 'Add name to the worksheet Range("A1").Value = AddName End Sub

Create a login Sub Login() 'Declare the variables Dim AddData As Range Dim AddName As String 203 | P a g e


Appendix - Macros in this eBook Page | 204 'Copy location Set AddData = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'Input variable AddName = InputBox("Type Your first name", "Add Name") 'Check for a value If AddName = Empty Then Exit Sub 'Add data to the worksheet AddData.Value = AddName AddData.Offset(0, 1).Value = Now End Sub

Create a login dynamic Sub Login2() 'Declare the variables Dim AddData As Range Dim AddName As String 'Copy location Set AddData = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'Input variable AddName = InputBox("Type Your first name", "Add Name") 'Check for a value If AddName = Empty Then Exit Sub 'Check for the right name Select Case AddName Case "Trevor" AddData.Value = AddName AddData.Offset(0, 1).Value = Now MsgBox "Welcome: - " & AddName Case "Harry" AddData.Value = AddName AddData.Offset(0, 1).Value = Now 204 | P a g e


Appendix - Macros in this eBook Page | 205 MsgBox "Welcome: - " & AddName Case "Mary" AddData.Value = AddName AddData.Offset(0, 1).Value = Now MsgBox "Welcome: - " & AddName Case Else MsgBox "Name not authorised" End Select End Sub

Login with 3 attempts Sub LoginCheck() 'Declare the variables Dim AddData As Range, Check As Range Dim Nme As String 'Count attempts Set Check = Sheet1.Range("E2") 'Destination location Set AddData = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'Input variable Nme = Application.InputBox("Add your login code name:- You have 3 attempts", Type:=2) 'Variable for authorise Auth = 0 'Loop through range of names For Each cName In Sheet1.Range("D2:D14") If cName.Text = Nme Then AddData.Value = Nme AddData.Offset(0, 1).Value = Now MsgBox "Welcome: - " & Nme Auth = 1 205 | P a g e


Appendix - Macros in this eBook Page | 206 Exit For End If Next cName 'Not authorised after 3 attempts and close workbook If Auth = 0 Then If Check.Value = 3 Then ActiveWorkbook.Close False MsgBox "You are not authorised" Check.Value = Check.Value + 1 'Call macro again LoginCheck End If End Sub

Collect employee age Age list (Input Function) Sub InputAge() 'Declare the variable Dim AddAge As Long 'Input variable AddAge = InputBox("Type Your Age", "Add Age", "Type your age") 'Copy location Cells(Rows.Count, 5).End(xlUp).Offset(1, 0) = AddAge End Sub

Age calculator (Input Method) Sub InputAge() 'Declare the variables Dim AddAge As Long Dim mMyAge As Range 'Set the the result range 206 | P a g e


Appendix - Macros in this eBook Page | 207 Set MyAge = Sheet1.Range("G8") 'Variable for input box AddAge = Application.InputBox("Type Your Age", "Add Age", "Type your date of birth", Type:=1) 'Check for value If IsNumeric(AddAge) And AddAge <> 0 Then 'Add date of birth to worksheet Sheet1.Range("F8") = AddAge 'Show formula result in a message box MsgBox "Your age is:- " & MyAge End If End Sub

BMI Calculator Basic BMI Calculator (Input Method) Sub BMI_Calculator() 'Declare the variables Dim Height As Long Dim Weight As Long Dim BMI As Range 'Variable for result Set BMI = Sheet2.Range("H8") 'Input variable for height Height = Application.InputBox("Add your height in centermetres:", Type:=1) 'Check for value and add value If IsNumeric(Height) And Height <> 0 Then Sheet2.Range("F8") = Height Else MsgBox "You must add a Height" Exit Sub End If 207 | P a g e


Appendix - Macros in this eBook Page | 208 'Input variable for weight Weight = Application.InputBox("Add your weight in kilograms:", Type:=1) 'Check for value and add value If IsNumeric(Weight) And Weight <> 0 Then Sheet2.Range("G8") = Weight Else MsgBox "You must add a Height" Exit Sub End If 'Show the BMI result in a message box MsgBox "Your BMI is: " & Format(BMI, "#,##0.0") End Sub

Working with Ranges (Input Method for Objects) Dynamic print area Sub InputPrint() 'Declare the variable Dim PrintArea As Range 'Set error handler On Error Resume Next 'Input variable Set PrintArea = Application.InputBox("Select the area to print", "Print area", "Scroll over print area", Type:=8) 'Check for value If PrintArea Is Nothing Then Exit Sub 'Set the print area ActiveSheet.PageSetup.PrintArea = PrintArea.Address 'Show the print dialog box Application.Dialogs(xlDialogPrint).Show 'Reset error handler 208 | P a g e


Appendix - Macros in this eBook Page | 209 On Error GoTo 0 End Sub

Loops Macros Do Loop Sub Do_Loop() 'loops until a condition is met 'fills cells A1:A30 with the value of "num" 'increase the variable "num" by 1 with each loop Dim num As Integer num = 1 Do Range("A" & num).Value = num num = num + 1 Loop Until num > 30 End Sub Do Until Loop Sub Do_Until() 209 | P a g e


Appendix - Macros in this eBook Page | 210 'loops until a condition is met 'fills cells B1:B30 with the value of "num" 'increase the variable "num" by 1 with each loop Dim num As Integer num = 1 Do Until num > 30 Range("B" & num).Value = num num = num + 1 Loop End Sub

Do While Loop Sub Do_While() 'loops while a condition is met 'fills cells C1:C30 with the value of "num" 'increase the variable "num" by 1 with each loop Dim num As Integer num = 1 Do Range("C" & num).Value = num num = num + 1 Loop While num < 31 End Sub

Do While Loop: Example 2 Sub Do_While2() 'loops while a condition is met 210 | P a g e


Appendix - Macros in this eBook Page | 211 'fills cells D1:D30 with the value of "num" 'increase the variable "num" by 1 with each loop Dim num As Integer num = 1 Do While num < 31 Range("D" & num).Value = num num = num + 1 Loop End Sub

For Loop For Loop: Example 1 Sub Loop_For() 'loops 56 times 'fills cells F1:F56 with background colors 'adds a backgroud color with each loop Dim ColCell As Integer For ColCell = 1 To 56 Range("F" & ColCell).Select With Selection.Interior .ColorIndex = ColCell .Pattern = xlSolid End With Next ColCell End Sub 211 | P a g e


Appendix - Macros in this eBook Page | 212 For Loop: Example 2 Sub Loop_For2() 'loops 30 times 'fills cells G1:G56 with 'adds a number and colors the number with each loop Dim ColNum As Integer Dim Rng As Range For ColNum = 1 To 56 Set Rng = Range("G" & ColNum) Rng.Value = ColNum Rng.Font.ColorIndex = ColNum Next ColNum End Sub

For Loop with Step Plus +: Example 3 Sub Loop_For_Step() 'loops 15 times 'fills every second cell in range H1:H30 with numbers 'changes the font size by 1point with each loop Dim Rng As Range Dim num As Integer For num = 2 To 30 Step 2 Set Rng = Range("H" & num) Rng.Value = num Rng.Font.Size = num Next num

212 | P a g e


Appendix - Macros in this eBook Page | 213 End Sub

For Loop with Step Minus: Example 4 Sub Loop_For_Step_Minus() 'loops 30 times from highest to lowest 'fills every cell in range I1:I30 with numbers from highest to lowest 'changes the font size by 1point with each loop Dim Rng As Range Dim num As Integer For num = 30 To 1 Step -1 Set Rng = Range("I" & num) Rng.Value = num Rng.Font.Size = num Next num End Sub

For Loop with Exit: Example 5 Sub Loop_For_Exit() 'loop stops in the middle if condition is met 'exit for loop Dim num As Integer For num = 1 To 1000 Range("J" & num).Value = num If num = 30 Then MsgBox "I will stop at 30" Exit For End If Next num 213 | P a g e



End Sub

For loop changing data: Example 6 Sub Loop_For_2() 'allows for a variable amount of data in the range 'bolds all numbers in the rfange Dim num As Integer For num = 1 To Cells(Rows.Count, 1).End(xlUp).Row Range("A" & num).Font.Bold = True Next num End Sub

For Each Loop For Each Loop: Example 1 Sub For_Each() 'loops through the object range 'changes the font to bold Dim mycell As Range For Each mycell In Range("J1:J30") mycell.Font.Bold = True Next End Sub

For Each Loop: Example 2 Sub For_Each_Protect() 'loops through each worksheet and protect the sheet 214 | P a g e


Appendix - Macros in this eBook Page | 215 Dim sht As Worksheet For Each sht In ThisWorkbook.Worksheets sht.Protect Next sht End Sub

For Each Loop: Example 3 Sub For_Each_UnProtect() Dim sht As Worksheet 'loops through each worksheet and unprotect each sheet For Each sht In ThisWorkbook.Worksheets sht.Unprotect Next sht End Sub

Nesting Loops Nesting Loops Example 1 Sub For_Each_Nest() 'loops through each worksheet 'adds the cell address to the range Dim sht As Worksheet Dim mycell As Range For Each sht In ThisWorkbook.Worksheets For Each mycell In sht.Range("K1:K30") mycell.Value = mycell.Address 215 | P a g e


Appendix - Macros in this eBook Page | 216 Next mycell Next sht End Sub

Nesting Loops Example 2 Sub For_Each_Nest2() 'loops through each worksheet 'checks for a value in a range 'colors the background if it exists Dim sht As Worksheet Dim mycell As Range For Each sht In ThisWorkbook.Worksheets For Each mycell In sht.Range("A1:A30") If mycell > 15 And mycell < 25 Then mycell.Interior.ColorIndex = 6 End If Next mycell Next sht End Sub

Nesting Loops Example 3 Sub Nested_For() 'adds numbers to multiple columns 'increases numbers by 1 for each column Dim MyRows, MyColumns As Integer For MyRows = 1 To 30 For MyColumns = 1 To 5 216 | P a g e


Appendix - Macros in this eBook Page | 217 Sheet2.Cells(MyRows, MyColumns).Value = MyRows + MyColumns Next MyColumns Next MyRows End Sub

Nesting Loops Example 4 Sub Nested_For2() 'loops through multiple columns 'checks for a value and colors the background if it exists Dim MyRows, MyColumns As Integer Dim Rng As Range For MyRows = 1 To 30 For MyColumns = 1 To 5 Set Rng = Sheet2.Cells(MyRows, MyColumns) If Rng.Value = 20 Then Rng.Interior.ColorIndex = 6 End If Next MyColumns Next MyRows End Sub

217 | P a g e



Error Handling Macros Compile Error Sub Compile_Error1() 'compile error 'wrong data type Dim rng As String rng = Range("D4") rng.Value = 20 End Sub Run Time Error Sub RunTime_Error2() 'Run time error Dim rng As Range 'Object variable not set rng = Range("D4") rng.Value = 20 End Sub Default Error Handler Sub Error_Default3() 'Default error handling 'Declare variables Dim Sales As Range, Customers As Range Dim Result As Long 'set range objects as variables Set Sales = Range("C4") 218 | P a g e


Appendix - Macros in this eBook Page | 219 Set Customers = Range("D4") 'Clear the value to cause the error Customers.Value = "" 'div#0 error here Result = Sales / Customers 'result in the message box MsgBox Result End Sub Resume Next Sub Error_Resume_Next4() 'Resume next error handling Dim Sales As Range, Customers As Range Dim Result As Long 'Error handler to ignore the error On Error Resume Next 'set range objects as variables Set Sales = Range("C4") Set Customers = Range("D4") Customers.Value = "" 'div#0 error here Result = Sales / Customers 'result in the message box MsgBox Result On Error GoTo 0 End Sub Label Error Handler Sub Error_Handler5() 'Error handling with a label Dim Sales As Range, Customers As Range Dim Result As Long 219 | P a g e


Appendix - Macros in this eBook Page | 220 'Error handler label to exit and give a message On Error GoTo Errhandler: 'set range objects as variables Set Sales = Range("C4") Set Customers = Range("D4") 'Clear the value to cause the error Customers.Value = "" Result = Sales / Customers 'result in the message box MsgBox Result 'Exit the procedure Exit Sub 'Error handler label Errhandler: MsgBox "Values are missing from the worksheet" End Sub Generic Error handler Sub Error_Handler_Generic() 'Error hanldling with a label Dim Sales As Range, Customers As Range Dim Result As Long 'Error handler label to exit and give a message On Error GoTo Errhandler: 'set range objects as variables Set Sales = Range("C4") Set Customers = Range("D4") 'Clear the value to cause the error Customers.Value = "" Result = Sales / Customers 'result in the message box MsgBox Result 220 | P a g e


Appendix - Macros in this eBook Page | 221 'Exit the procedure Exit Sub 'Error handler label Errhandler: MsgBox "An Error has Occurred " & Chr(10) & "The error number is: " _ & Err.Number & Chr(10) & Err.Description & Chr(10) & _ "Please notify the administrator" End Sub Error Handler Resume Sub Error_Handler_Resume6() 'Resume error handing Dim Sales As Range, Customers As Range Dim Result As Long 'Error handler label On Error GoTo Errhandler: 'set range objects as variables Set Sales = Range("C4") Set Customers = Range("D4") 'Clear the value to cause the error Customers.Value = "" 'div#0 error here Result = Sales / Customers 'result in the message box MsgBox Result 'Exit the procedure Exit Sub 'Error handler label Errhandler: 'Correct the error with an input from the user If Err.Number = 11 Then Correct = Application.InputBox("Add the number of Customers:", Type:=1) 221 | P a g e


Appendix - Macros in this eBook Page | 222 Range("D4") = Correct 'Resume the code from the error Resume End If End Sub

Error Handler Resume Label Sub Error_Handler_Resume_Label7() 'Resume label Dim Sales As Range, Customers As Range Dim Result As Long 'Error handler label On Error GoTo Errhandler: 'set range objects as variables Set Sales = Range("C4") Set Customers = Range("D4") 'Clear the value to cause the error Customers.Value = "" 'Resume label Fixit: 'div#0 error here Result = Sales / Customers 'result in the message box MsgBox Result 'Exit the procedure Exit Sub Errhandler: 'Correct the error with an input from the user If Err.Number = 11 Then Correct = Application.InputBox("Add the number of Customers:", Type:=1) Range("D4") = Correct 222 | P a g e


Appendix - Macros in this eBook Page | 223 'Resume label Resume Fixit: End If End Sub

My Tax Receipt Macros Open userform (code for Module) Sub Showme() frmTax_Receipts.Show End Sub Sub Filterme()

Filter (code for Module) Sheet2.Range("D4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("M5:N6"), CopyToRange:=Range("C7:J7"), Unique:=False End Sub

Add Values to database (code for Userform) Private Sub cmdAdd_Click() 'dimention the variable Dim ws As Worksheet Dim addme As Range 'set the variable Set ws = Sheet2 223 | P a g e


Appendix - Macros in this eBook Page | 224 'set variable for the destination Set addme = Sheet2.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) 'hold in memory Application.ScreenUpdating = False If Not IsDate(txtDate.Value) Then MsgBox " The date field must be a proper date", Visual Basic Editorxclamation, "Date format error" Me.txtDate.Value = "" Me.txtDate.SetFocus Exit Sub End If If Me.txtDate.Value = "" Or Me.cboCompany.Value = "" Or Me.txtAmount.Value = "" Then MsgBox "There is insufficient data. Madatory fields must be added (*)", Visual Basic Editorxclamation, "Mandatory fields are incomplete" Exit Sub End If 'send the values to the database With ws 'set the date format to suit your area addme.Value = Format(txtDate.Value, "mm/dd/yy") addme.Offset(0, 1).Value = Me.cboCompany.Value addme.Offset(0, 2).Value = Me.txtDescription addme.Offset(0, 3).Value = Me.cboCategory addme.Offset(0, 4).Value = Format(Me.txtAmount.Value, "$##,###.00") If OPT1.Value = True Then addme.Offset(0, 5).Value = "Paper Copy" ElseIf OPT2.Value = True Then addme.Offset(0, 5).Value = "Scanned Copy" Else addme.Offset(0, 5).Value = "" End If 224 | P a g e


Appendix - Macros in this eBook Page | 225 addme.Offset(0, 6).Value = cboLocation addme.Offset(0, 7).Value = txtRemarks End With 'sort the data Sheet2.Select With Sheet2 Sheet2.Range("D4:K10000").Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlGuess End With 'return to sheet Sheet1.Select 'reset the form Unload Me frmTax_Receipts.Show 'update the sheet Application.ScreenUpdating = True End Sub

Close Userform (code for Userform) Private Sub cmdClose_Click() Unload Me End Sub

Reset Userform (code for Userform) Private Sub cmdReset_Click() Unload Me frmTax_Receipts.Show End Sub

225 | P a g e



Always remember this when you are learning: If the work that you create pleases you then it is good work. Be willing to accept criticism of your work because it is an open door to improvement. Feedback from others will help you identify weaknesses and strengths from which you can adjust and build your skills and expertise. Remarkable effects can be achieved when you challenge yourself with VBA project based learning.

226 | P a g e


VBA for Beginners eBook Learn VBA -

Recommend Documents