excel 2013 - visual basic programming for · pdf fileonly excel 2013 - visual basic...

For Eva

luatio

n Only

Excel 2013 - Visual Basic

Programming For Excel

Supports:

Excel 2010

Excel 2013

Excel 2016

Jeff Hutchinson

Excel Networks

www.excel-networks.com

For Eva

luatio

n Only

Page 1

Excel 2010 / 2013 / 2016 - Visual Basic for Excel

This course will employ the Visual Basic Application (VBA) programming language that is built in to Excel. It will help to automate repeating operations and redundant tasks. Students will begin by recording macros and storing the results in Visual Basic code. Minor changes can then be made using the Visual Basic Editor to trouble shoot problems. Techniques will be discussed to demonstrate how to select cells in order to format areas in an automated way. Variables will also be covered. These are great methods to help remember specific items for use at a later time. “If” logic will be discussed to help automate decisions and provide the logic necessary to make adjustments. Message and Input Boxes are ways to provide input and store results in a cell. Loops will provide the logic to repeat steps in multiple cells. Worksheet and Workbook manipulation can allow access of data from multiple worksheets. The use of all these functions will automate the overall efficiency of a project. Furthermore, once you take the Excel VBA training class, you’ll be able to apply your VBA programming knowledge to other MS Office products, including Microsoft Access. The courseware will support Excel 2010, 2013 and 2016 because the VBA commands are very similar. However, any differences will be noted in the courseware.

This manual is designed in conjunction with an Online Instructor-Led course (for more information see: www.excel-networks.com). Unlike other manuals, students will not need to review lengthy procedures in order to learn a topic. All that is needed are the brief statements and command paths located within the book that demonstrate how a concept is used. Furthermore, students will find that this book can often be used as a reference guide to help comprehend a concept quickly. An index is also provided on the last page to reference topics/concepts as necessary. However, if more detail is needed, students can always use the Internet to search a concept. If skills are weak due to lack of use, students can refresh their memory quickly by visually scanning the concepts needed and then testing them out using the applications.

Table of Contents Chapter 1 - Excel Macros .............................................................................................................. 3 Chapter 2 - Visual Basic Editor .................................................................................................. 11 Chapter 3 - Cell Manipulation .................................................................................................... 16 Chapter 4 - Formatting Techniques ........................................................................................... 22 Chapter 5 - Variables ................................................................................................................... 25 Chapter 6 - “If” Statements ........................................................................................................ 30 Chapter 7 - Message and Input Boxes ........................................................................................ 35 Chapter 8 - Loop Control Structures ......................................................................................... 44 Chapter 9 - Worksheet and Workbook Manipulation ............................................................. 49 Chapter 10 - Functions ................................................................................................................ 55 Chapter 11 - Custom Programming Techniques ...................................................................... 59 Appendex A – Quick Reference Guide ...................................................................................... 63 Excel 2010 / 2013 VB Excel Index............................................................................................... 65

For Eva

luatio

n Only

Page 2

Exercise Download Exercises are posted on the web site and can be downloaded to your computer. Please do the following: Open Internet Explorer.

Type the web address: excel-networks.com/download/excel2013-5.exe You might get several security warnings, but answer yes and run through each one. When you click “Unzip,” the files will be located in C:\Data\Excel2013-5 folder.

If there are any questions or problems, please contact Jeff Hutchinson at: [email protected]

Note: For Mac users, download the file at: excel-networks.com/download/excel2013-5.zip

About the Author Jeff Hutchinson is a computer instructor teaching a variety of classes around the country. He has a BS degree from BYU in Computer Aided Engineering and has worked in the Information Technology field supporting and maintaining computers for many years. He also owned a computer training and consulting firm in San Francisco, California, and after selling his business in 2001, he has continued to work as an independent computer instructor in California and Utah. Mr. Jeff Hutchinson lives in Utah and provides training for the Utah Valley University Community Education system, offering valuable computer skills for the general knowledge of students, career development, and career advancement. Understanding the technology and the needs of students has been the basis for developing this material. Jeff Hutchinson can be contacted at [email protected] or (801) 376-6687.

Copyright and Release Information This document was updated on 9/30/2016 (Version 2) and is designed for Excel 2013. However, it supports Excel 2010, 2013 and 2016. This material is the sole property of Jeff Hutchinson and Excel Networks. Any emailing, copying, duplication or reproduction of this document must be approved by Jeff Hutchinson in writing. However, students who take a class or purchase the material can use this document it for personal development and learning.

Legend Numbered sections on the left are the Concepts. Italic text is used to highlight commands that will perform the concept or procedure in completing the practice exercises. Practice Exercises are a Step-by-Step approach to performing the concept. Student Projects are a comprehensive approach to performing the concept. Dark, grayed-out sections are optional/advanced concepts. Bolded items are ribbons or commands used. Working code will be in non-bold Italic font. All Practice Exercises do not start with Sub and Variables have not been declared.

http://www.excel-networks.com/download/acrobat11-1.exe

mailto:[email protected]

http://www.excel-networks.com/download/acrobat11-1.zip

mailto:[email protected]

For Eva

luatio

n Only

Page 3

Excel 2010 / 2013 - Visual Basic for Excel Chapter 1 - Excel Macros

Macros will allow you to automate routine tasks at the push of a button.

Learning Objective – In this chapter, you will learn how to write a macro, open a macro with the proper security, and determine the difference between Absolute and Relative, learn debugging techniques, assign macros to form buttons, Shape Buttons, graphic objects, Quick Access toolbar, Customized Ribbons, keyboard commands, and understanding Visual Basic Code.

Concept Explanation / Command String in italics. 1.1 Security Level This will define the Security Level when opening a file that contains Macros.

The recommended option is: Disable All Macros with Notification. Practice Exercise 1 In Excel: File TabOptionsTrust CenterTrust Center SettingsMacro

Settings. 1.2 Run A Macro Once a Macro is created, it can be executed to perform several actions. Practice Exercise 2 In Excel: File TabOpenMacro City57.xlsm.

View Ribbon TabMacrosView MacrosAtlanta Run. View Ribbon TabMacrosView MacrosChicagoRun. Note: If the macro does not run because of security reasons, continue on to the next exercise to create a new macro.

1.3 Developers Tab The Developers Tab contains additional features and the Code Ribbon Group contains the Macro creation tools. In Excel: File TabOptionsCustomized Ribbon (Right Side).

1.4 Macro Creation When you create a new Macro press the Record Macro button and a dialog box will ask several questions. We will describe those questions and provide some comments concerning each one: Macro Name This is a single word name and can contain no spaces. However, it can contain underscore characters. Dashes are not allowed. Assigning a Shortcut Key This will assign a short cut key to run the Macro. If you use Ctrl B (a predefined key), it will temporarily reassign the key to the Macro. When you delete the Macro or reassign the key, the Bold will return. It is suggested that you assign the Macro to a Shift Ctrl Key combination to avoid using popular pre-assigned keys. To add a shortcut key at a later time: Developer Ribbon TabMacros(Select Macro)Options. Location of Macro This Workbook – This is the current opened document. New Workbook – This creates a new Bookx.xlsx workbook. When two workbooks are open, you can run macros from either one. Personal Macro Workbook – This is available in any workbook. Description This is the description of the Macro.

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 1 - Excel Macros

Page 4

Practice Exercise 3 Open File

Create a Macro to open a file. 1. View Ribbon TabMacrosRecord Macro a. Macro Name: OpenFile b. Short Cut Key: c. Store Macro In: This Workbook d. Description: Open File e. OK 2. File Tab OpenMacro Atlanta.xlsxOpen Button. 3. Developer Ribbon Tab Stop Recording. Run the Macro: File TabCloseDeveloper Ribbon TabMacros Select: OpenFileRun.

Practice Exercise 4 Zoom

Create a Macro to zoom 200% in a worksheet. 1. Developer Ribbon TabRecord Macro a. Macro Name: Zoom200 b. Short Cut Key: c. Store Macro In: Personal Macro Workbook d. Description: Zoom e. OK 2. View Ribbon TabZoom Button 200% Ok 3. Developer Ribbon TabStop Recording. Run the Macro: View Ribbon Tab100% ButtonDeveloper Ribbon Tab MacrosSelect: Zoom200Run.

1.5 Absolute Macro

This will always refer to the exact cell where you first created the Macro. If there is no box around the relative icon, it is Absolute or unselected. Guidelines: When you run the Macro, it will always put the information at the same cell. If there is no box or not highlighted around the relative icon, it is Absolute. The starting point cannot be where you want to enter information.

Practice Exercise 5 Absolute Macro

Example: Type in your address in a specific location or in the same cell. Place your cursor in cell B1 and the first command (or click) will be the Absolute starting point.

1. Open Sheet2 or Open a new blank worksheet. 2. Select Cell B1. 3. Developer Ribbon Tab No orange box around “Use Relative Reference”

Icon (UnSelected) . 4. Developer Ribbon TabRecord Macro a. Macro Name: AddressABS b. Short Cut Key: c. Store Macro In: Personal Macro Workbook d. Description: e. OK 5. Select cell A1. 6. Type in cell A1: Jeff Hutchinson Enter Key. 7. Type in cell A2: 12345 Jackson Road Enter Key. 8. Stop Macro: Developer Ribbon TabStop Recording. Test it out: Place Curser in cell D1Developer Ribbon TabMacros Select AddressABSRun. Test it out: Place Curser in cell D9, Developer Ribbon TabMacros Select AddressABSRun.

Note: Text will always be placed in the absolute same location.

For Eva

luatio

n Only


Page 5

1.6 Relative Macro

This will place the information in the active selected cell. If there is a box around the relative icon, it is Relative or Selected. Guidelines: If there is a box around the relative icon, it is Relative. The starting point is any cell position.

Practice Exercise 6 Relative Macro

This will type your address where ever you place the cursor.

1. Open Sheet3 or Open a new blank worksheet. 2. Select Cell A1. 3. Developer Ribbon TabOrange box around “Use Relative Reference” Icon

(Selected ). 4. Developer Ribbon TabRecord Macro a. Macro Name: AddressREL b. Short Cut Key: c. Store Macro In: Personal Macro Workbook d. Description: e. OK 5. Type in cell A1: Jeff Hutchinson Enter Key. 6. Type in cell A2: 12345 Jackson Road Enter Key. 7. Stop Macro: Developer Ribbon TabStop Recording. Test it out: Place Curser in cell A1Developer Ribbon TabMacros Select AddressRELRun. Test it out: Place Curser in cell D9Developer Ribbon TabMacros Select AddressRelRun. Note: It will place the text at the cursor location or relative position.

Practice Exercise 7 Absolute Macro

Create an Absolute Macro using the principles above. 1. Open Sheet2 or create a new blank worksheet. 2. Place your cursor in cell C1. 3. Developer Ribbon Tab No orange box around “Use Relative Reference” Icon

(UnSelected) . 4. Developer Ribbon TabRecord Macro a. Macro Name: ABSMacro b. Short Cut Key: c. Store Macro In: Personal Macro Workbook d. Description: e. OK 5. Place your cursor in cell A1 and type the following:

A B 1 Name Phone 2 Jeff 123 3 Jane 456 4 Tom 789

6. Stop Recording the Macro: Developer Ribbon TabStop Recording. 7. Test out Macro: Select cell D6 Developer Ribbon TabMacros Select ABSMacroRun.

For Eva

luatio

n Only


Page 6

Practice Exercise 8 Relative Macro

Create a relative Macro using the principles above. 1. Open Sheet3 or create a new blank worksheet. 2. Place the cursor anywhere to start. 3. Developer Ribbon TabOrange box around “Use Relative Reference”

Icon (Selected ). 4. Developer Ribbon TabRecord Macro a. Macro Name: RELMacro b. Short Cut Key: c. Store Macro In: Personal Macro Workbook d. Description: e. OK 5. Type the following phone extension list.

A B 1 Name Phone 2 Jeff 123 3 Jane 456 4 Tom 789

6. Stop Recording the Macro: Developer Ribbon TabStop Recording. 7. Test It: Select cell F7 Developer Ribbon TabMacros Select RELMacroRun.

Practice Exercise 9 Absolute

Create a standard title for a spreadsheet. 1. File TabNewBlank worksheet. 2. Place the cursor cell B1. 3. Developer Ribbon Tab No orange box around “Use Relative Reference”

Icon (UnSelected) . 4. Developer Ribbon TabRecord Macro a. Macro Name: MikeTitle b. Short Cut Key: c. Store Macro In: Personal Macro Workbook d. Description: e. OK 5. Place the cursor cell A1 and type: Mike’s Sporting Goods. 6. Press the Enter Key. 7. Format cell A1 with the following: Bold, Dark Blue Font, Select cells A1 to H1Merge and Center. 8. Stop Recording the Macro: Developer Ribbon TabStop Recording. 9. Test It: Open a new blank sheet Developer Ribbon TabMacros Select MikeTitleRun.

For Eva

luatio

n Only


Page 7

Practice Exercise 10 Relative

Create an ending salutation for a spreadsheet. 1. File TabNewBlank worksheet. 2. Developer Ribbon TabOrange box around “Use Relative Reference”

Icon (Selected ). 3. Developer Ribbon TabRecord Macro a. Macro Name: MikeEnd b. Short Cut Key: c. Store Macro In: Personal Macro Workbook d. Description: e. OK 4. Start typing: Mike’s Sporting GoodsPress the Enter Key. 5. Type: 2500 south street, New York, NY Press the Enter Key. 6. Select the text and format with the following: Bold and Dark Blue Font. 7. Stop Recording the Macro: Developer Ribbon TabStop Recording. Test It: Open a new blank sheet Developer Ribbon TabMacros Select MikeEndRun.

Practice Exercise 11 Create A Today Macro

Create a Today Macro to place the current date anywhere. 1. Open Sheet5 or create a new blank worksheet. 2. View Ribbon TabMacroOrange box around “Use Relative Reference”

Icon (Selected ). 3. Select A1 Developer Ribbon TabRecord Macro

a. Macro Name: Today b. Short Cut Key: c. Store Macro In: Personal Macro Workbook d. Description: Enters Today’s Date e. OK

4. BoldRedFont Size 12Type: Today() 5. Stop Recording the Macro: Developer Ribbon TabStop Recording. 6. Test It: Select cell G7 Developer Ribbon TabMacros Select TodayRun.

Practice Exercise 12 Format Area

Create a Macro to format an area that is selected. 1. File TabNewBlank Worksheet and select a small area. 2. Make sure the macro is in Relative mode 3. Developer Ribbon TabRecord Macro a. Macro Name: FormatArea b. Short Cut Key: c. Store Macro In: Personal Macro Workbook d. Description: Format Worksheet e. OK 4. Home Ribbon TabFont Size: 20Fill Area: Blue 5. Developer Ribbon TabStop Recording. Test the Macro: Select a different area or open another workbook.

1.7 Shape Buttons Shapes can be used as a button to execute a Macro. The shape can be formatted using the Formatting Ribbon Tab or by right-clicking on the shape. Hold the Alt key to fit or line up with the cells. This will help to line up multiple buttons.

For Eva

luatio

n Only


Page 8

Practice Exercise 13 Insert Ribbon TabShapes(Choose a shape)Draw shape on worksheetRight-Click on shapeAssign Macro.

1.8 Picture Buttons

Pictures, objects or any shape can be used as a button and a Macro can be assigned. The formatting of the picture is limited in Excel and modifying the actual picture must be made using a graphic editor program.

Practice Exercise 14 Insert Ribbon TabPictureFrom File: Macro Click Me.png InsertPlace graphics image. After the image is placed, you can assign a Macro: R-Click on the Graphics ImageAssign Macro.

1.9 Quick Access Toolbar

Macros can be assigned to the Quick Access toolbar located in the upper left corner of the screen.

Practice Exercise 15 Quick Access Toolbar dropdown More Commands Choose command from: Macros(Select desired macro) Add.

1.10 Customized Ribbon

Create Macro Ribbon Tab - Excel 2010 added the capability to add new ribbon tabs on the top of the screen (not available in Excel 2007).

Practice Exercise 16 File TabOptionsCustomize RibbonChoose Commands from: MacrosSelect the View TabRight side choose Right-ClickRenameDisplay Name: MacrosSelect GroupAdd Macro.

1.11 Form Buttons This will create a Form Macro Button used to execute a Macro. Note: The formatting of the Form Button is limited.

Practice Exercise 17 Developer Ribbon TabInsert Dropdown Button(Form Control)Draw Button on worksheetAssign Macro. To edit a button: Right-Click on ButtonAssign Macro.

1.12 Unassigned Ctrl Commands Ctrl + Shift Commands Macros Keys CTRL+E

CTRL+J CTRL+M CTRL+Q

Ctrl + Shift + b Ctrl + Shift + c Ctrl + Shift + d Ctrl + Shift + e Ctrl + Shift + g Ctrl + Shift + h Ctrl + Shift + j

Ctrl + Shift + k Ctrl + Shift + l Ctrl + Shift + m Ctrl + Shift + n Ctrl + Shift + q Ctrl + Shift + r

Ctrl + Shift + s Ctrl + Shift + t Ctrl + Shift + v Ctrl + Shift + w Ctrl + Shift + x Ctrl + Shift + y

Create the following macros and review the code.

Student Project A Format Worksheet

This will create a Macro to format a worksheet. 1. File TabOpenMacro1.xlsx Australian sheet. View Ribbon TabMacroRecord Macro a. Macro Name: SheetFormat b. Short Cut Key: Ctrl M c. Store Macro In: This Workbook d. Description: Format Worksheet e. OK 2. Select A1Font size=24Font Color=Green. 3. Select A3:E3BoldItalic. 4. Select B4:E9FormatCellsNumber TabCurrency. 5. Stop Recording. 6. Select European sheetViewMacroView Macros.

Select SheetFormat MacroRun. 7. Select North American and Summary Ctrl m.

For Eva

luatio

n Only


Page 9

Student Project B Print Setup

This will create a Macro to setup your printer layout. 1. Developer Ribbon TabRecord Macro

a. Macro Name: PrintSetup b. Short Cut Key: Ctrl Shift P c. Store Macro In: Personal Macro Workbook d. Description: Sets up the Printer e. OK

2. Change the print setup. Page Layout Ribbon TabOrientationLandscape. Make any other change as desired. 3. Developer Ribbon TabStop Macro. 4. Test change orientation to Portrait. 5. Create a Button: Insert Ribbon TabShapes DropdownRectangle.

Student Project C Macro Formatting

Format the multiple worksheets using a macro. Part 1 – Create Macro 1. File TabOpenFormat1.xlsx. 2. Open worksheet Qtr1. 3. Make sure you are recording in Absolute Reference. 4. Place the cursor in cell A2 to start an Absolute macro. 5. Developer Ribbon TabRecord Macro a. Macro Name: FormatQtr b. Short Cut Key: c. Store Macro In: This Workbook d. Description: Format All Sheets e. OK 6. Format A1 to be 16 pt and bold. 7. Format the labels to be 12pt and bold. 8. Format all numbers to Currency format. 9. Format the percentage numbers to Percentage format. 10. Developer Ribbon TabStop Macro. Test it out: Open and run macro on Worksheets Qtr2, Qtr3, and Qtr4. Part 2 – Modifying the Code 11. View the code: Developer Ribbon TabVisual Basic 12. Apply the color Blue to the labels. Place the following code in the first position before .Name = "Arial" and test the macro. Color = -4165632 13. If the step 12 didn’t work try placing the color blue code in a different position towards the end.

For Eva

luatio

n Only


Page 10

Student Project D Use ActiveSheet Name

Use the ActiveSheet object with the name property to extract the Worksheet name and place in an ActiveCell. 1. FileNewBlank Workbook. 2. Rename Sheet1 to MyWorkSheet. 3. Make sure you are recording in Absolute Reference. 4. Place the cursor in cell A2 to start an Absolute macro. 5. Developer Ribbon TabRecord Macro a. Macro Name: MyWorksheet b. Short Cut Key: c. Store Macro In: This Workbook d. Description: e. OK 6. Select cell A1 and type : The worksheet name is: 7. Press the Enter Key. 8. Type: MyWorksheet. 9. Press the enter key. 10. Developer Ribbon TabStop Macro. 11. Developer Ribbon TabVisual Basic.. 12. Make the following changes to the macro: Sub MyWorksheet() Range("A1").Select ActiveCell.FormulaR1C1 = “The worksheet name is” Range(“A2").Select ActiveCell.FormulaR1C1 = ActiveSheet.Name End Sub 13. Rename the Worksheet name and rerun the macro.

For Eva

luatio

n Only

Page 11

Chapter 2 - Visual Basic Editor Macros are recorded in Visual Basic code. You can review the code created and make minor changes in the Visual Basic Editor.

Lesson Objective – In this chapter, VB Editor, interface, modules, components of the code, debugging and running the Code will be covered.

Concept Explanation / Command String in italic. 2.1 VB Editor The Visual Basic Editor is used to edit the macros and code. You can open the

VB Editor in several ways: 1. Developer Ribbon TabVB Editor. Note: If you do not have the Developer tab open: File TabOptions Customized MenuOn the right side check the developer check box. . 2. View Ribbon TabMacrosView Macros(Select Macro)Edit. If there are no Macros defined in “This Workbook,” then you will need to use the Developer Ribbon Tab.

2.2 Switch To Excel Once you open the VB Editor, you may want to switch back to Excel. To do this, press Alt F11, the Windows Icon on the bottom of the screen, or click on

the Excel Icon in the VB Editor. You may also want to minimize the VB Editor to see the program display results in the Spreadsheet.

2.3 Minimize VB Editor

Often times, you want to see the results of the VB code on the worksheet. Open the WorksheetOpen the VB editorIf the VB editor is maximized, then click the minimize button in the upper right corner. Position the VB editor so you can see part of the worksheet as shown below.

2.4 Project Explorer

This window should always be open in order to see the opened Excel files and subroutine modules. View MenuProject Explorer.

2.5 Personal Macro Project

This is one of the project on the list in the Projects Window and contains the

macros/code stored at the system level. When a Macro and VB code is stored here, it is accessible from any opened Excel file and worksheet..

2.6 Workbook Projects

When a Macro and VB code is stored here, it is accessible from this workbook only. Each opened file is a separate

project. The projects are displayed at the top of the tree in the Project Explorer.

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 2 – Visual Basic Editor

Page 12

2.7 Modules Folder

The screen capture to the right is an Excel 2010 feature and the modules are located under the Modules Folder. In Excel 2013/2016 the modules are not stored under the folder, but in the Microsoft Excel Objects tree..

2.8 Create A Module

The screen capture to the right is an Excel VBA tree. The Code is stored in the Modules located under the Modules Folder.. To create a new module: Right-Click on any icon in the treeInsertModule or in the VB Editor: Insert MenuModule.

2.9 Properties Windows

This will display properties of various modules, forms, and other objects in the VB Project Explorer: ViewProperties Window.

2.10 Rename Module

The name of the module should reflect its contents or the type of sub routines they contain. In order to change the modules name, you must select the module and change the name in the Properties Window. View MenuProperties.

2.11 Locals Window

The Locals Window can be used to view variables as they are defined in the debugging mode. This is probably the most useful debugging screen. View MenuLocals Window.

Practice Exercise 18 View MenuLocals Window. Type the following code in a subroutine. Dim i As Integer For i = 1 To 10 Next

2.12 Immediate Window

This is most useful for executing VBA statements directly as well as testing statements and debugging code. You can monitor the progress of the results by inserting the command “Debug.Print ActiveCell.Value” View MenuImmediate Window.

Practice Exercise 19 View MenuImmediate Window. Type the following code in a subroutine. Dim i As Integer For i = 1 To 10 Debug.Print "I = " & i Next

2.13 Watch Window

This is similar to the Locals Window except each variable must be added and watched. The variables will automatically appear in the Locals Window. View MenuWatch WindowAdd Watch.

Practice Exercise 20 View MenuWatch Window. Right-Click on white areaAdd WatchExpression: i. Dim i As Integer For i = 1 To 10 Next

For Eva

luatio

n Only


Page 13

Coding Components

2.14 Sub End Sub

VB subroutines contain the code to run a program. The Subroutine names contain no spaces, but they can contain underscore, capital letters, the name must different from other subroutines, and cannot use reserved names such as Sum, Count, etc.. It is recommended that the name describes the propose/result of the code.. The VB Code follows the name which looks similar to the following: Sub MyFirstProgram() Range(“A1”).Value = “Test1” End Sub The last line of the Subroutine is End Sub..

Practice Exercise 21 To create a subroutine, type Sub ProgramName then press the Enter key. This will add the ( ) and the End Sub. Type the following code: Sub ProgramName() Range(“A1”).Value = “Test1” End Sub

2.15 VB Comments Or REM

Comments can be inserted in the VB code to provide information. In order to insert a comment, place an apostrophe character ‘ or the word REM in the beginning of the line. The following are a few examples: ‘This is a comment REM This is a comment

2.16 Object.Property Method

Each position of the code will follow the following structure: Object Property = Method Range(“A1”).Value = “Test1” Object – This is in the first position of the code in the example above such as: Range(“A1”). It describes components in the Excel worksheet such as cell, chart, form, or report. Some examples are: ActiveCell, Range, Selection, ActiveSheet, Columns, etc..

Property – This is in the second position of the code in the example above such as the word: .Value. It describe the color, size and screen location. The word CurrentRegion is the Property that will select all the cells within the region of the cell. Some examples are: Font, Interior, Select, Value, Name, Autofit, Address, etc..

Method – This is in the third position of the code in the example above such as: = “Test”. They are the action to be taken for the property and based on the Object location. Some examples are: rgbBlue, “Test”, True, False, etc..

Period - This will be placed between commands will separate the object name with the properties to determine the full path of the command..

For Eva

luatio

n Only


Page 14

Debugging and Running Code

2.17 Debug Toolbar

This toolbar is used to run and debug code. View TabToolbarsDebug.

2.18 Debug Step-Into

This will allow you to step through the code one command at a time. The keyboard command is F8.

2.19 Reset This will abort the current program in the middle and allow you to start over.. Practice Exercise 22 Add the following code to a subroutine:

Dim i As Integer For i = 1 To 10 Next View TabToolbarsDebug

to jump out press . 2.20 Run This will run the code from the beginning. VB Editor: RunRun Sub/UserForm

You can also press: Icon or press the F5 key. Practice Exercise 23 Select a cell A1 and type the word TEST. Use the following code to test the debug

features. Sub Test () Selection.Font.Underline = True Selection.Font.Bold = True Selection.Font.Italic = True Selection.Font.Color = rgbRed End Sub

2.21 Option Explicit

This is used to make sure variables are declared and mis-spelled rgb color names are correct. If the “Option Explicit” is left off, variables do not need to be declared. However, if there is a variable declaration problem (such as use of the wrong data type), the error will not be invoked. The word “Option Explicit” will be placed above the first sub routine in the module. Option Explicit Sub MySubroutine() Code End Sub

Practice Exercise 24 Option Explicit

1. Type the name rgbDarkBlue incorrectly (Such as rgbDrkBlue) and remove the command Option Explicit from the module and run the following code: Range("A1:C3").Font.Color = rgbDrkBlue 2. Add the word Option Explicit on top of the module and run the code. 3. Declare a variable and remove Option Explicit. VariableValue = Range(“A1”).Value 4. To fix the variable do the following: Dim VariableValue as String VariableValue = Range(“A1”).Value

For Eva

luatio

n Only


Page 15

Optional Topics

2.22 Auto Add Option Explicit

The following will put the “Option Explicit” at the top of the editor in every new module: VB Editor: ToolsOptionsEditorX Require Variable Declaration

2.23 Debug Step-Over

This runs just like “Step Into” except it will jump over a specific call procedure. Works in break mode only. Note: This does not seem to work as defined.

2.24 Debug Step-Out

You must start with “Step-Into.” However, when you choose “Step Out” it will run everything to the end of the code.

2.25 Debug Run To Cursor

Place the cursor in the code and click “Run To Cursor.” The debug will run the code up to the cursor location.

2.26 BreakPoint Place the cursor on the side wall of the editor to insert a Breakpoint. Click Run and it will stop or halt at the Breakpoint. The arrow in the code will show which line it is on. The keyboard command is F9.

2.27 Run Time Error

This will notify you if there is an error in your code. The cursor will stop and highlight the error.

Practice Exercise 25 Range(“A1”).Value = “Test” Remove a double quote “ or a parenthesis ) and run the compiler. The error will then be highlighted.

2.28 VB Module Font

This is used to change font in VB Editor: Tools MenuOptionsEditor format.

2.29 Find Subroutine

A quick way to find a subroutine in a Module is click the down arrow just above the code screen..

2.30 Turn Off Popup

This will prevent the auto-popup error to appear. If you are typing a lot of code and know you have errors, this will stop prompting with error messages. VB Editor: ToolsOptionsO Auto Syntax Check.

2.31 IntelliSense After you type in some code in the VB Editor you can press the Ctrl Spacebar to evoke the IntelliSense feature instead of typing in the full value. This includes a number of features: List Members, Parameter Info, Quick Info, and to complete a word.

Practice Exercise 26 Type: “ActiveCell.” with a . then press the Ctrl Spacebar to see a list of parameter options of necessary.

2.32 IntelliSense Symbols

To access the command options, press Ctrl Spacebar at the end of the formula. The following symbols will be displayed in front of the command name and these provide a way to categorize all commands:

Methods Command Example: ABS, Select Properties Command Example: ActiveCell, Range, Selection, Address, Value,

Name, and Offset. Command Example: VBCancel Command Example: Color, rgbRed

Command Example: ColorConstants Command Example: Debug Command Example: Excel Command Example: Global

For Eva

luatio

n Only

Page 16

Chapter 3 - Cell Manipulation This chapter will review the most common commands to identify a cell, select a cell, and manipulate data.

Learning Objective – In this chapter, we will discover different ways to identify location of information, identify locations and adding information to cells.

Concept Explanation / Command String in italics. Practice Exercise 27 In Excel: File TabOpenEmployment1.xlsx. 3.1 Range Object This is probably the most used command which selects or defines cells. It is

considered an Absolute Object because it points to a specific location od A1 Example: Range(“A1”).Value = “HR”

3.2 Active Cell Object

The active cell object is considered a relative Reference Object because it does not point to a specific location but identifies the location of an active cell.

3.3 Select/Selection Property

The Select Property can be used to select specific cells and then format them. The Selection is then used to identify the select property.

Practice Exercise 28

This will define the value in cell, select the cell, then format it Bold. Range("B1:G1").Select Selection.Font.Bold = True

3.4 Value Property This specifies the value of an active cell and allows you to define the cell value. Range(“A1”).Value = “Human Resource”

Practice Exercise 29 Value Property String

Range("B15").Value = "Jensen" Range("C15").Value = "Jack" Range("E15").Value = "Production" Add code to format similar to the above examples.

Practice Exercise 30 Value Property Number

Range("F15").Value = 5 Range("G15").Value = 54000

Practice Exercise 31 Value Property Date

Range("D15").Value = #1/12/2003#

3.5 FormulaR1C1 Property

Sometimes, the property FormulaR1C1 can be used in the place of Value. However, the format of FormulaR1C1 cannot be written as FormulaR2C1. Example: Range("A1").FormulaR1C1 = "R1C1"

3.6 Copy Property You can Copy information from a range of cells to a new range. ActiveCell.Copy 3.7 Cut Property You can Cut information from a range of cells to a new range. ActiveCell.Cut 3.8 Paste Property You can paste copied information. Paste method is applicable to the Worksheet

object ActiveSheet (do not use ActiveCell).. ActiveSheet.Paste Practice Exercise 32 This is an example of the Method (action) in the 2nd position.

Range("B1:B29").Copy Range("H1").Select ActiveSheet.Paste

Practice Exercise 33 Range("C1:C29").Cut Range("I1").Select ActiveSheet.Paste

Try using ActiveCell or ActiveCells in the place of Selection to compare results.

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 3 – Cell Manipulation

Page 17

Practice Exercise 34 This copies the range A1:A12 using the Select/Selection technique and paste it to the range beginning at cell C1: Range("A1:A12").Select Selection.Copy Range("C1").Select ActiveSheet.Paste

3.9 PasteSpecial Method

The Paste Special options can also be applied.

Practice Exercise 35 Range("G1:G29").Copy Range("K1").Select ActiveCell.PasteSpecial xlPasteValues

3.10 Clear Method This deletes content from a range of cells. There are two related methods such as ClearContents and ClearFormats.

Practice Exercise 36 The following remove everything from column D. Range("A1:G1").ClearFormats

3.11 Delete Method This differs from the Clear in that it will shift the remaining cells around to fill up the range you deleted. When you delete a group of cells Excel needs to know how to shift the cells such as xlToLeft, xlUp. Example: Range(B6:B10").Delete xlToLeft

Practice Exercise 37 Range("B29:G29").Delete or Range("B20:G20").Delete xlUp

Column and Row Techniques

3.12 Columns/Rows Object

The Columns command will reset the column width or format an area. Columns(“B:E”).Select or Columns("A").Select If you need to select a row or column, do the following: Rows("1:3").Select

Practice Exercise 38 The following will delete a column: Example 1: Columns("E:E").Delete or Columns("E").Delete Example 2: Select cell Columns(ActiveCell.Column).Select Example 3: Select Cell ActiveCell.EntireColumn.Delete The following will delete a row: Example 1: Rows("3:3").Delete or Rows("3").Delete Example 2: Select cell Rows(ActiveCell.Row).Select Example 3: Select Cell ActiveCell.EntireRow.Delete

3.13 Column/Row Property

This will extract the column/row number from an active cell. Although, the a number (such as 1,2,3) will be extracted, the data type is actually a string. In other words if you were to extract the number to a variable it must be declared as a string. MsgBox ActiveCell.Column MsgBox ActiveCell.Row

Practice Exercise 39 This will display the active cell row: MsgBox ActiveCell.Row 3.14 Column/Row Range

This will select and format the entire range of cells. Example: Range(“B:C”).Select

Practice Exercise 40 3.15 EntireRow/ EntireColumn Property

This will extract the row number from the active cell. Range(“A5”).EntireRow.Select This will extract the column number from the active cell. Range(“A5”).Entirecolumn.delete

3.16 Insert A Row/Column

Inserts an entire row. Selection.EntireRow.Insert Inserts an entire column. Selection.EntireColumn.Insert

Practice Exercise 41 This will display the active cell column: MsgBox ActiveCell.Column

For Eva

luatio

n Only


Page 18

Student Project E – Select Row where you place the cursor.

1. File TabNewBlank Workbook 2. Range(Cell(ActiveCell.Row, 1), Cell(ActiveCell.Row, 5)).Select

Student Project F – Copy Range

1. In Excel: File TabOpenConference Menu.xlsx. 2. Write a VB Program to define cell E1 as “Gourmet Menu” and Cell E2 as “Gourmet Items and Prices.” 3. Copy cells A4:C17 to cells E4:G17. 4. Copy Columns E:G using Paste special option xlPasteColumnWidths. 5. Write a VB Command for each line for G5 to G17 and increase the rate by 1.2. Example: G5*1.2.

Possible Solution Only Use As Last Resort

Cell Identification - The following are techniques to accomplish similar results.

Practice Exercise 42 In Excel: File TabOpenFormat2.xlsx. 3.17 Single Select 1 This technique will select a specific cell and allow you to modify its contents.

Example: Range(“A1”).Select Practice Exercise 43

Range(“A1").Select Selection.Font.Underline = True

3.18 Range Select 2 This technique will select multiple cells. Example: Range(“A2:E2”).Select Also, Multiple Ranges can be selected. Range("A2:B2, D2:E2").Select


Select cells A2 to I2 and format as Bold. Range(“A2:I2”).Select Selection.Font.Bold = True

3.19 Range Select 3 This technique can be used to run additional commands on the second cell reference. Range("A2", "I2").Font.Underline = True

Practice Exercise 45 Range("A2", "I2").Select Selection.Font.Color = rgbRed

For Eva

luatio

n Only


Page 19

3.20 Range Select 4 Cells

This technique will identify specific cells (Row1,Column1) and (Row3,Column3). In other words, it will identify cells A1 to C3. Example: Range(Cells(1,1), Cells(3,3)).Select Note: You can also identify a single cell using: Cells(1,1).Select

Practice Exercise 46 This will select cells B3 through I7. Range(Cells(3, 2), Cells(7, 9)).Select Selection.Font.Color = rgbBlue

3.21 Range Select 5

This technique is not commonly used, but will function similar to the above examples. [A1:E3].Select


[A2:A7].Select Selection.Font.Bold = True

Cell Offset, xlDown, CurrentRegion, and Address.

Practice Exercise 48 In Excel: File TabOpenFormatA.xlsx. 3.22 Offset Property

This will offset a number of cells from the active cell. The Offset uses the format of Offset(Row,Column). Row ↨ = 1 or (+1) will move down (1,0) and -1 will move up (-1, 0). Column ↔ = 1 or (+1) will move right (1,0) and -1 will move left (-1, 0). ActiveCell.Offset(-3, -2) – Offset up 3 cells and to the left 2 cells. ActiveCell.Offset(3,2) – Offset down 3 cells and to the right 2 cells.

Practice Exercise 49 Note: If A1 is merged with A1 through I1, the offset (0,1) will actually be cell J1. Range("B1").Offset(0, 1).Value = "Test Offset"

Practice Exercise 50 Read the previous cell and increment the next cell by 1. To start, Select Cell I8. ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1 ActiveCell.Offset(0, 1).Value = "Test" Start location: End Result:

Practice Exercise 51 Move the selection down each time the code runs.

Select cell A1 then run the following code 10 times. Selection.Font.Bold = True ActiveCell.Offset(1, 0).Select

3.23 xlDown This select cells 1 to the end of the column. The End will look for the last cell in the column. Related commands xlUp, xlToRight, and xlToLeft.

Practice Exercise 52 It doesn’t seem to matter where you select the second range name in the same column. Try changing the A3 to A1 or A7. Range("A1", Range("A3").End(xlDown)).Select Selection.Font.Color = rgbPurple

For Eva

luatio

n Only


Page 20

Practice Exercise 53 This will select cell A1, move relative to the end of the list (xlDown), then move relative down one cell (Offset) by offsetting down one cell:. 1. Type 1,2,3, in cells A1, A2, and A3. 2. Type the following code in a subroutine: Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select 3. This will do the exact same thing except in one line of code: Range(“A1”).End(xlDown).Offset(1,0).Select

3.24CurrentRegion Place the cursor in the middle of the region and run the command. This is similar to a Ctrl A. Example: Range("A1").CurrentRegion.Copy

Practice Exercise 54 Range("A1").CurrentRegion.Copy Range("F1").PasteSpecial

Practice Exercise 55 This will select the data area using xlDown and xlToRight. Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select

Practice Exercise 56 Current Region

ActiveCell.CurrentRegion.Select Selection.Font.Bold = True

3.25 Address Property

This property will identify the cell address in absolute format such as $D$5.

Practice Exercise 57 Select cell A9 and run the following code: ActiveCell.Value = ActiveCell.Address This will store the value of $A$9 in the cell A9.

Practice Exercise 58 In this example, we will start in cell A1 then x will be used to remember the original cell address. When you move to cell A10, the x will be used to remember the original address. Range("A1").Select

x = ActiveCell.Address Range("A10").Select Range(x).Select Msgbox x

This will remember the cell address for future reference. Practice Exercise 59 The following statement displays $A$1:$E$5 in the message box:

MsgBox Range(Cells(1,1),Cells(5,5)).Address 3.26 Text Property This will return a string as displayed. Practice Exercise 60 Text Property

Enter the value $23.50 in cell A1 and format it to currency. MsgBox Range("B1").Text MsgBox Range("C1").Value Note: The Text property works with the MsgBox it doesn’t seem to work with a range property. Range(“B1”).Value = “Test” Range(“C1”).Text = “Test”

3.27 Select Sheet Select the entire sheet. Cells.Select

For Eva

luatio

n Only


Page 21

3.28 Range Name This will identify cells that were defined as a range name. You will need to define a range name area, then write the necessary code. Define a Range Name called Area: Select the cells to define an areaIn the Name box (upper left corner next to the formula bar) type in the name and press the Enter Key. Type in the following code and run it: Range("Area").Select An alternative technique follows: [Area].Select

Practice Exercise 61 Define a Range Name and use the name of “Area.” Open the VB Editor and type in the following code: Range("Area").Select Selection.Font.Color = rgbGreen An alternative technique follows: [Area].Select Selection.Font.Color = rgbPurple

Student Project G – Format Sheet

1. In Excel: File TabOpenPivot1.xlsx. 2. Write VB code to format each column a different color. 3. You can use the xlDown or Columns command to find the end of the data.

Possible Solution – Use as a last resort

For Eva

luatio

n Only

Page 22

Chapter 4 - Formatting Techniques This chapter will review the most common formatting techniques to manipulate cell data.

Learning Objective – In this chapter, we will discover different ways to identify format cells using techniques such as font, fill, borders, offsetting cells, as well as concatenate text strings, “With” statements.

Practice Exercise 62 In Excel: File TabOpenFormat3.xlsx. 4.1 Selection Object

This will allow you to select specific parts of a worksheet such as cell, column, or range. It is usually used in the begging of the code to identify the cells. The following example will show how to use the Selection Object: Range (“A1:A7”).Select Selection.Font.Name = “Arial” Selection.Font.Size = 12

4.2 Font

The Font Property is commonly used. Learning some of its capabilities will allow you to quickly make needed changes.

Practice Exercise 63 Range("A1:I2").Font.Color = rgbDarkBlue 4.3 Fill (Interior) This will format the background of the cells. Practice Exercise 64 Format the background of cells A2 to I2 and A3 to A7.

Range("A2:I2").Interior.Color = rgbLightBlue Range("A3:A7").Interior.Color = rgbLightBlue

4.4 Paragraph Paragraph formatting works similar to Font formatting. Practice Exercise 65 Merge the cells in range A1 to I1.

Range("A1:I1").Select Selection.Merge Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter

4.5 Border To format a border, you must format each side as well as the center lines of the selected area.

Practice Exercise 66 Define the borders for cells A2 to I7. Range("A2:I7").Select Selection.Borders(xlEdgeLeft).Weight = xlThin Selection.Borders(xlEdgeTop).Weight = xlThin Selection.Borders(xlEdgeBottom).Weight = xlThin Selection.Borders(xlEdgeRight).Weight = xlThin Selection.Borders(xlInsideVertical).Weight = xlThin Selection.Borders(xlInsideHorizontal).Weight = xlThin

4.6 NumberFormat Property

This will change the format of a cell to a specific format. Example: change 2 to 2.00.

Practice Exercise 67 Enter a number in column G then format the number. Columns("G:G").NumberFormat = "0.00." Note: You can also use Columns(“G”).

4.7 Date format Range(“A9”).Value = #1/1/2000# 4.8 AutoFit Method

This is used in conjunction with the Columns Object will Autofit the columns. If Columns(“B:E”).AutoFit If a single cell is identified the column will Autofit. Range("B1").Select ActiveCell.EntireColumn.AutoFit

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 4 - Formatting Techniques

Page 23

Practice Exercise 68 Variable

Enter your last name and loop 5 times. Sub VariableExample1() Range("B6").Select Dim x As Integer x = 0 Dim MyName As String Do Until x = 5 MyName = InputBox("Enter Last Name") ActiveCell.Value = MyName Selection.Offset(1, 0).Select x = x + 1 Loop End Sub

4.9 Concatenation This will connect or patch text strings together in order to make one longer text string.

Practice Exercise 69 Concatenation

Type the word Jeff in cell A8. Range("A8").Value = Range("A7").Value & " Value"

Practice Exercise 70 Concatenation

Range("A1").Value = Range("A2") & " " & Range("A1")

With Statements

Practice Exercise 71 In Excel: File TabOpenFormat4.xlsx. 4.10 Single Line Format

This will select and format everything on a single line without using a With statement.

Practice Exercise 72 This will identify the cells and format in one line. Range("A2:I2").Interior.Color = rgbLightBlue Range("A3:A7").Interior.Color = rgbLightBlue

4.11 With Selection This will require multiple lines to format the cell. Practice Exercise 73 Range("B3:I7").Select

With Selection.Interior .Color = rgbLightGreen End With


Use the Selection object along with the Font property to format a cell. With Range(“A2”) .Font.Name = “Arial” .Font.Size = 12 End With

4.12 Entire Column This will select an entire column using the xlDown option and format it. Practice Exercise 75 Range("A2", Range("A2").End(xlDown)).Select

With Selection.Font .Bold = True End With

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 4 - Formatting Techniques

Page 24

Practice Exercise 76 Nested With Example

This will select the first sheet and format it. Sub WithExample() Sheets(1).Select Range("A1").Select With ActiveCell (ActiveCells does not work. If A1:B1 is selected .Value = 100 ActiveCell will only select the first cell) .Style = "Currency" .HorizontalAlignment = xlRight .EntireColumn.AutoFit With .Font .Name = "Arial" .Size = 14 End With End With End Sub

Practice Exercise 77 In Excel: File TabOpenFormat5.xlsx Change the following code using a With Statement. Sub FormatCells () Range("A1").Font.Size = 20 Range("A2:A7").Font.Bold = True Range("A2:A7").Interior.Color = rgbLightBlue Range("B2:I2").Font.Bold = True Range("B2:I2").Interior.Color = rgbLightBlue Range("B3:H7").NumberFormat = "$#,##0.00" Range("I3:I7").NumberFormat = "0.00%" End Sub Example Result:

Student Project H – Write a Format Macro.

1. In Excel: File TabOpenFormat5.xlsx. 2. Create a Macro to completely format a Sheet. 3. Run macro on Qtr2, Qtr3, and Qtr4. 4. When finished review the code in the VB Editor.

For Eva

luatio

n Only

Page 25

Chapter 5 - Variables Variables are the basic building blocks in any programing language and will provide a way to store information to be used later in the routine.

Learning Objective – In this chapter, you will learn the variable structure, math operations, Datatypes, and various problems encountered when using variables.

Concept Explanation / Command String in italics. Practice Exercise 78 In Excel: File TabOpenVariables1.xlsx. 5.1 Variables These are temporary storage locations for information to be used at a later time.

The data will remain in the storage location until new data over rides the current information. When a macro stops, the variable is cleared. Example: Dim Value as Integer

Practice Exercise 79 Math Operations

This will subtract a number from a defined cell. Type the number 25 in cell C2.

Range("C2").Value = Range("C2").Value – 1 Or

Dim x As Integer X = Range("C2").Value – 1 Range("C3").Value = x

Test: Continue to run the program. 5.2 Variable Name Rules

Name First Letter - The first character of Variable Name must be a letter not a number. Correct Example: test12345. Incorrect example: 12345test Name Special Characters – Special characters can’t be used in the name. There are no spaces ( ), periods (.), exclamation marks (“), ampersands (&), “at” symbols (@), dollar signs ($) or pound signs (#) allowed. An unacceptable example is #Test. Name Size - The name has a limit, it can’t be longer than 255 characters. Name Reserved - The name can’t be the same as a VBA reserved name. An unacceptable example is: Select. Name Same - Two variables can’t use the same name

5.3 Dim This is a Dimension statement to define a variable. Each variable must be declared defining the datatype allowed. Once the datatype is declared, only that datatype can be added to the value.

Practice Exercise 80 Dim

Open Locals (View TabLocals) to monitor the variables changing. Run the following code.

Dim x As Integer Dim CustName As String Dim CurrentCust As Boolean x = 39 CustName = "Jack Jetson" CurrentCust = True Range("A1").Value = CustName Range("A2").Value = CurrentCust Range("A3").Value = x

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 5 - Variables

Page 26

5.4 Set Object Variable

This allows you to define a string of commands to be used as a variable. It doesn’t actually save the text values in storage, but will identify the location of the data in the worksheet location. Example: Set ObjectVar = Range("B5:B13") ObjectVar.Font.Color = rgbBlue This will define the ObjectVar Range("B5:B13") and use it to format the cells.

Practice Exercise 81 Set ObjectVar

Dim ObjectVar As Range Set ObjectVar = Range("B5:B13") ObjectVar.Font.Color = rgbBlue

5.5 Datatypes The datatypes that can be processed are: Text, Numbers, Date and Boolean – This is a value defining True/False, Yes/No, or in Access this value is stored in the database as -1/0. Uses 2 bytes of computer space. Byte - This is used for small numbers from 0 to 255 and requires 1 byte of computer storage space. Integer - This is used to dimension a variable to a numeric value with no decimal points. The number range is from -32,768 to 32,767 and requires 2 bytes of computer storage space.. Long Integer- This is an integer (no decimal places) and is used to dimension a variable to a numeric value with numbers ranging from -2,147,483,648 to 2,147,483,647 and requires 4 bytes of storage.. Decimal – Very, very big and requires 14 bytes of computer storage space.. String - This is used to dimension a variable to a text string and can store approximately 0 to 2 billion characters and uses 10 bytes plus the number of characters used. Date - This is used to dimension a variable to a date value and uses 8 bytes of computer space. The range is from #1/1/100 00:00:00AM# to #12/31/9999 23:59:59PM# Currency - This is used to dimension a variable to a numeric currency value with numbers ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. It requires 8 bytes of storage.

5.6 Datatype Formats

The datatype can be declared using different styles. The following are different ways to deckair variables.

Dim x As Integer Dim i, x, y As Integer Dim i As Integer, m As Long Dim i As Integer, x As Integer, y As Integer

Also, the case is not required: dim i as integer Practice Exercise 82 No Declaring

Declaring the variable will ensure the value will be inserted with the proper format. It is good practice to dimension (DIM) your variables. However, the variable can also be used without declaring. Create the following and don’t specify a declaration. Range (“A4”).Value = “Text String”

Range (“A5”).Value = 2 Range (“A6”).Value = True

Practice Exercise 83 Declaring Number

This will declare a number variable and declare it as integer. Dim MyNumber As Integer MyNumber = 10 Range(“A7”) = MyNumber

For Eva

luatio

n Only


Page 27

Practice Exercise 84 Declaring Text String

This will declare a string variable and declare it as a string. Dim MyText As String MyText = “The Bakery Place” Range(“A8”) = MyText

Practice Exercise 85 Declaring Dates

This will declare a date variable and assign a date. Dim MyDate As Date MyDate = #1/1/2000# Range(“A9”) = MyDate

5.7 Assignments The Assignments statement allows you to assign a value to a specific variable. Example: Dim x As String x = “Text String”

Practice Exercise 86 Select Range Example (FS)

Select the data area by recording cell A1 and finding the last cell in the area. Range("A1").Select x = ActiveCell.Address Range("B11").Select ActiveCell.End(xlDown).Select ActiveCell.End(xlToRight).Select y = ActiveCell.Address Range(x, y).Select

5.8 Public Variable If you need to make a variable available to all the procedures within a single module, declare the variable on top at the module level using the Public keyword. This work similar if you declared on the top as Dim.

Practice Exercise 87 Public Test As Integer Sub test2() Test = 2 MsgBox Test End Sub

5.9 Private Variable

Private Variable means it is not available to other modules. It is usually declared on the top of the module outside of the subroutine. The Dim declaration works similar to Private Declaration.

5.10 Private Sub This is similar to a Private Variable. However, the subroutine is not available outside of the current module.

5.11 Const Variable

The Constant declaration (Const) can be used instead of DIM if the value is not changing. The compiler will produce an error if you try to redefine the value. Notice that the variable is designed within the declaration statement.

Practice Exercise 88 Define the Constant value in the top of the module. Const Test As Integer = 4 Sub ConstTest() MsgBox Test End Sub (Did it work? Why not?)

Insert the following command before the MsgBox. Test = 2 5.12 Static Variable The Static Variable declaration will remember the stored value for future

executions. Practice Exercise 89 Type in the following code and run the program multiple times to see the results.

Sub MySub() Static Counter As Integer Counter = Counter + 1 MsgBox Counter End Sub

For Eva

luatio

n Only


Page 28

Optional Topics

5.13 Parenthesis Around An Option.

A parenthesis must be placed around an option that is assigned to a variable. Dim ButtonVar as VBMsgBoxResult ButtonVar = MsgBox(“Please Answer Yes or No”, VBYesNo)

5.14 Declare Above Modules

You can declare the variables above the subroutine that are outside of the code. Dim Test As String Sub Newsubroutine() Code End Sub

5.15 Global Scope A global scope includes Public and Private declared in the top of the module.. 5.16 Arrays Arrays are a collection of data, all having the same data type (called the base

type), grouped into a single array variable, with a single array name. Declaring Array Variables: Dim Text1() As String – Declares a default array starting at 0. Dim Test2(1 to 5) As String – Declares an Array starting from 1 to 5. Split Function - Stores a delimitated text string to an Array. Testd=”1;2;3” Text1()=Split(Testd, “;”) Join Function - Extracts values from an Array. Dim Result As String Result = Join(Text1, “,”) Redim - Redefines the Array size to add additional values. If you use the default Dim of Test1() the redim must start at 0 not 1. If you Dim the Array as Dim Testy(1 to 5) then the values will start at 1 not 0. Preserve - Restores the existing values in the array when redim. Redim Preserve Test1(4) Test1(3)=”4” 0=Value 1, 1=Value 2, 2=Value 3, 3=Value 4

For Eva

luatio

n Only


Page 29

Student Project I – Test Declaration Types

Open a new blank workbook, create 2 Modules in the VB Editor and type in the following code Module 1 Module 2 Public TestIt As Integer Option Explicit Option Explicit ------------------------------------------- -------------------------------------------- Sub Test1 () Sub Test3 () TestIt = TestIt +1 TestIt = TestIt +1 Msgbox TestIt Msgbox TestIt End Sub End Sub ------------------------------------------ Sub Test2 () TestIt = TestIt +1 Msgbox TestIt End Sub

Public Test - Run the code in each subroutine (Test1, Test2 and Test3).. Private Test - Change the word Public to the word Private. Run the code in each subroutine. Dim Test - Change the word Private to the word Dim. Run the code in each subroutine. Global Test - Change the word Dim to the word Global. Run the code in each subroutine. Const Test - Change the word Global to “Const testit As Integer = 6” and comment out the line “testit = testit +1.” Run the code in each subroutine. Static Test - Static declaration must be defined in each subroutine.

DIM - Declare the following variable two different ways Name (String), City (String), Number (Integer).

For Eva

luatio

n Only

Page 30

Chapter 6 - “If” Statements “If” Statements are used to define logical true/false operations of a statement requiring several possible outcomes.

Learning Objective – In this chapter, you will learn about the “If” structure, math operators, comparison operators, logical operators, and select case statements.

Concept Explanation / Command String in italics. Practice Exercise 90 In Excel: File TabOpenLogicalIf4.xlsx. 6.1 Operators These are symbols that separate values. They are normally used to perform

arithmetic operations, comparing two numbers, assigning data to a worksheet, or answering logic questions.

6.2 Assignment Operator =

The = is used to assign values to variables.

6.3 Arithmetic Operators

* - Multiplication / - Division + - Addition - - Subtraction ^ - Exponentiation & - String Concatenation

6.4 Comparison Operators

> - Greater Than < - Less Than >= Greater Than or equal to <= Less than or equal to = Equal To <> Not equal to

6.5 Logical Operators

AND OR NOT

6.6 “If” Structure The If structure uses logical operators to evaluate a statement. If xxx = yyy Then

Code End If

Practice Exercise 91 OR Operator

Determine the length of a text string: Dim NameLength As Integer, NameDesc As String

NameLength = Len(Range("B5").Value) If NameLength < 3 Or NameLength > 17 Then LengthDesc = "Out Of Range" Else LengthDesc = "In Range" End If Range("A5").Value = LengthDesc

Test by selecting a different company and test the text length.. Test by changing B5 to B1 to test the “Out of range” Error.. Test: Rewrite the subroutine to extract the selected cell (ActiveCell) and place the LengthDesc in the Column A by using the Offset property. Answer: NameLength = Len(ActiveCell.Value) ActiveCell.Offset(0,-1).Value = LengthDesc

OR Operator

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 6 - If Statements

Page 31

Practice Exercise 92 Determine the amount of a numerical value:

Select Cell A5 and type in the following code:.. If Range("D5").Value > 20000 Then

Range("A5").Value = "Yes" Else Range("A5").Value = "No" End If

Test: Change to a different Qtr1 value. Rewrite the formula using ActiveCell and Offset.

6.7 One Line Another variation of the “IF” statement would be to put the else statement on a single line.


Determine the length of a cell using a One Line else statement: NameLength = Len(Range("B5").Value) If NameLength < 12 Then LengthDesc = "Short" Else: LengthDesc = "Long" End If Range("A5").Value = NameLength & " is " & LengthDesc

Test: Change to a different company name. 6.8 Nested If This is an “IF” within an “IF” Statement. Practice Exercise 94 Test out a nested if statement.

NameLength = Len(Range("B7").Value) If NameLength < 12 Then LengthDesc = "Short" Else If NameLength < 15 Then LengthDesc = "Medium" Else LengthDesc = "Long" End If End If Range("A5").Value = NameLength & " is " & LengthDesc

6.9 End If This is a close at the end of the “IF” statement. 6.10 Goto The Goto command will allow you to jump to a different position in the program.

“Else:” allows it to be on one line.

Nested If

For Eva

luatio

n Only


Page 32

Practice Exercise 95 Use the If along with the Goto command. Note: The command Range(“D5”).Value will not work and you must use Range(“D5”).

If Range("D5") > 15000 Then GoTo Big Else GoTo Small End If Big: Range("A5").Value = "Big" GoTo Out Small: Range("A5").Value = "Small" Out:

Practice Exercise 96 Using ActiveCell and Offset

This will define the Bonus based on the active cell value. Select any status located in cell C5 to C13 and run the following program: If ActiveCell.Value = 1 Then Bonus = ActiveCell.Value * 0.3 Selection.Offset(0, -2).Value = Bonus ElseIf ActiveCell.Value = 2 Then Bonus = ActiveCell.Value * 0.2 Selection.Offset(0, -2).Value = Bonus ElseIf ActiveCell.Value = 3 Then Bonus = ActiveCell.Value * 0.1 Selection.Offset(0, -2).Value = Bonus Else Bonus = ActiveCell.Value * 0.05 Selection.Offset(0, -2).Value = Bonus End If Test: Select a different status and rerun the formula.

Select Case Statements

Practice Exercise 97 In Excel: File TabOpenLogicalIf4.xlsx. 6.11 Select Case This is useful for choosing a logical statement with three (3) or more options and

is an alternative for using “IF” statements and nested “IF” statements. 6.12 Case Variations

Possible variations of the select Case statement are: Case IS < 100 Case 0 to 100 Case 1,2,13

6.13 Case Else This is the default statement at the end of the case if no other option is chosen. Practice Exercise 98 Case IS

Define the size based on the length of cell A6. This uses the “IS < x” technique: Select Case Len(Range("B5").Value)

Case Is < 3 Range("C5").Value = "Small" Case Is < 8 Range("C5").Value = "Short" Case Is < 12 Range("C5").Value = "Long" Case Else Range("C5").Value = "Very Big" End Select Test: Try a different text value.

Note: To access the cell D5 the if statement does not use the format: Range(“D5”).Value

Note the ActiveCell and Offset.

Note: Case Is < 3.

For Eva

luatio

n Only


Page 33

Practice Exercise 99 Case x to y

Define the size based on the length of cell A5. This uses the “0 to 2” technique: Select Case Len(Range("B6").Value) Case 0 To 2 Range("A6").Value = "Small" Case 3 To 7 Range("A6").Value = "Short" Case 8 To 11 Range("A6").Value = "Long" Case Else Range("A6").Value = "Very Big" End Select

Test: Try using a different text value. Practice Exercise 100 Case x, y

Define the size based on the length of cell A5. This uses the “1, 2” technique. Select Case Len(Range("B8").Value) Case 1, 2 Range("A8").Value = "Small" Case 3, 4, 5, 6, 7 Range("A8").Value = "Short" Case 8, 9, 10, 11 Range("A8").Value = "Long" Case Else Range("A8").Value = "Very Big" End Select Range("A8").Value = LengthDesc

Test: Try using a different text value. 6.14 Nested Select Case

Select Case statements can be nested inside another Select Case statement.

Note: Case 0 To 2.

Note: Case 1,2.

For Eva

luatio

n Only


Page 34

Student Project J – Calculate a raise based on the Status.

1. In Excel: File TabOpenEmployment4.xlsx. 2. Use the following criteria based on the status: Status Raise 2 5% Raise 3 7% Raise 4 10% Raise 7 12% Raise


3. Use the If Then Else If statement to calculate the raise. 4. Add the following command at the end of the VB code select the next cell. ActiveCell.Offset(1, 0).Select 5. Run the program multiple times to select the next cell and calculate the next raise.

6. Extra Credit – Rewrite the program using Select Case statements. Possible Solution – Use as a last resort

For Eva

luatio

n Only

Page 35

Chapter 7 - Message and Input Boxes This chapter will cover Popup Boxes that provide messages and other input.

Lesson Objective – In this chapter, you will learn how to build a message box, input box, user form, and application input box.

Concept Explanation / Command String in italics. Practice Exercise 101 In Excel: File TabOpenWorksheets1.xlsx. 7.1 MsgBox Function

The Message Box Function is used to capture user input when a macro is run. A Message Box will consist of a message and at least one button for the user to click. Depending on the response, the routine could jump over specific code. The message must be the 1st value if the type option is not used. Example: MsgBox (“Message”, Option) MsgBox “Message” Option Variable = (“Message”, Option)

Practice Exercise 102 MsgBox “This Is My Message” Practice Exercise 103 Enter a Value in cell B2.

If Range(“B3”).Value > 1500 Then MsgBox(“You Win!”) Else MsgBox(“You Lose”) End If

7.2 Option Name If you use the option method, enter the option name for each option. Example: MsgBox Prompt = "Message to be displayed", Title = "Title on the top of prompt", Buttons = vbInformation

Practice Exercise 104 MsgBox Prompt:=“Hello World”, Buttons:=vbInformation, Title:=“ Message On Top”

7.3 Default Option With the Default Option, there is no need to enter option names. Simply separate each option with commas in the exact order of the option.

Practice Exercise 105 MsgBox “Hello World”, vbInformation, “Message On Top” 7.4 Title This displays additional information on the Title bar.

Example: MsgBox "Message", Title, "Message On title Bar”

Practice Exercise 106 MsgBox "Left Message", Title:= "Message On Title Bar"

7.5 Default Message Box

If no parameters are provided other than the message parameter, the screen capture to the right will be the result. The message parameter (see practice Exercise below) is the only required field.

Practice Exercise 107 Msgbox “Hello World”

7.6 Information Button

This will display the Information message icon in the pop-up box.

Practice Exercise 108 MsgBox “Hello World”, VBInformation, “Message On Top”

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 7 - Message and Input Boxes

Page 36

7.7 Red Circle Button

This will display the Critical message icon in the pop-up box.

Practice Exercise 109 MsgBox “Hello World”, VBCritical, “Message On Top.”

7.8 Question Warning Symbol Button

This will display the Warning Query icon in the pop-up box.

Practice Exercise 110 MsgBox “Hello World”, vbQuestion, “Message On Top.”

7.9 Exclamation Button

This will display the Exclamation icon in the pop-up box.

Practice Exercise 111 MsgBox “Hello World”, vbExclamation, “Message On Top.”

7.10 Add Return Lines

This will concatenate the code VBCRLF or VBNewLine to the MsgBox. It will place the message on two lines. An optional technique would be to use Carriage Return character Chr(13) and the Line Feed character Chr(10).

Practice Exercise 112 MsgBox “Please Enter The” & VBCRLF & “Following Info” Practice Exercise 113

Carriage Return/Line Feed Example Sub vbCRLFExample() x = InputBox("Enter Your First Name") y = InputBox("Enter Your Last Name") Z = InputBox("Enter Your Phone Number")

MsgBox "You listed your First Name as: " & x & vbCrLf & _ "Your Last Name as: " & y & vbCrLf & _ "Your Phone Number is listed as:" & Z End Sub

7.11 Input a Cell This will extract the value from a cell as the message. Practice Exercise 114 Select a cell with a numeric or string value.

MsgBox ActiveCell.Value & “ Was Entered” 7.12 Yes/No This prompts “Yes/No” as the message. Practice Exercise 115 MsgBox “Do you like Pizza”, VBYesNo

The following are alternate methods: MsgBox "Do you like Pizza?", vbQuestion + vbYesNo

7.13 Variable Assignment

When you assign a msgbox or location to a variable, it requires parentheses(SPELLING)?? around the statement.. ButtonVar = MsgBox(“Please Answer Yes or No”, VBYesNo)

Practice Exercise 116 This will store the results in cell A1. Note the ( ) are needed when you assign the message box to a variable. Dim ButtonVar as VBMsgBoxResult ButtonVar = MsgBox(“Please Answer Yes or No”, VBYesNo) If ButtonVar = VBYes Then Range(“A1”).Value = “Yes” Else Range(“A1”).Value = “No” End if

For Eva

luatio

n Only


Page 37

7.14 Stacking Parameters

When using multiple buttons, you can stack them using a + sign. x = MsgBox("Do you wish to BOLD?", vbYesNo + vbDefaultButton1 + vbCritical, "Bold Data?")

Practice Exercise 117 Message Box Example 1

Enter text in a cell and select the cell. Sub MessageBoxExample1() 10: x = MsgBox("Do you wish to BOLD?", vbYesNo, "Bold Data?") If x = vbYes Then ActiveCell.Font.Bold = True End If If x = vbNo Then y = MsgBox("Are you sure that you don't want to BOLD?", vbYesNo, "Are You Sure?") If y = vbYes Then End If y = vbNo Then GoTo 10 End If End Sub

Practice Exercise 118 Message Box Example 2

Enter text in a cell and select the cell. Sub MessageBoxExample2() x = MsgBox("Do you wish to BOLD?", vbYesNo + vbDefaultButton1 + vbCritical, "Bold Data?") If x = vbYes Then ActiveCell.Font.Bold = True End If If x = vbNo Then ActiveCell.Font.Bold = False MsgBox "You can always decide to BOLD later by clicking the Bold button", Button, "" End If End Sub

Optional Topics

7.15 MsgBox Help Go to the Excel VBA reference and search for MsgBox in order to obtain a list of the parameters available, or click on the link below: https://msdn.microsoft.com/en-us/library/139z2azd(v=vs.90).aspx

7.16 Button Type This will display the type of button in the prompt. Only one type can be displayed at a time. The button types are: vbOKOnly, vbOKCancel, vbAbortRetryIgnore, vbYesNoCancel, vbYesNo, and vbRetryCancel.

7.17 Message Icons These will be displayed as icons in the message box. Only one can be displayed at a time. The options are: vbCritical, vbQuestion, vbExclamation, and vbInformation.

7.18 Mobility This determines how the application will react to a parameter. vbApplicationModal will force the user to respond to the message box before continuing and vbSystemModal will suspend all applications until the user responds to the message box.

7.19 Text Position Without this parameter, the text message will be left aligned and the vbMsgBoxRight will be aligned to the right.

Practice Exercise 119 Test the message box by using the above parameters.

https://msdn.microsoft.com/en-us/library/139z2azd(v=vs.90).aspx

For Eva

luatio

n Only


Page 38

Input Box

Practice Exercise 120 In Excel: File TabOpenBlank Workbook. 7.20 Input Box This will provide the ability to enter text and have the code

respond accordingly. The first parameter is the message and the second parameter will appear on the title bar. Example: InputBox "Message", "Title Bar"

Practice Exercise 121 InputBox "Message", "Title Bar" Practice Exercise 122 Range("A1").Value = InputBox("What do you want

to say?")

7.21 3 Parameters The first Parameter is the message, second is the title, and the third is the default

text in the input box. Practice Exercise 123 InputBox “Please Enter Your Name:”, “Personal Details”, “Enter Name Here” 7.22 X Value This is the 4th Parameter of the InputBox and specifies the placement distance at

the left edge of the screen. Without this Parameter, the InputBox will be placed in the center of the screen.

7.23 Y Value This is the 5th Parameter of the InputBox and specifies the placement distance from the top edge of the screen. Without this Parameter, the InputBox will be placed in the center of the screen.

Practice Exercise 124 InputBox "Please Enter Your Name:", "Personal Details", "Enter Name Here", 2000, 2000

7.24 Value Parentheses are needed with input parameters because the equal “=“ sign is used to define a value.

Practice Exercise 125 Range(“A1”).Value = InputBox(“Please enter your name”, “Personal Details”) Practice Exercise 126 Dim TitleBarText, DefaultText, X, MessageText as String

MessageText = “This is the message to be displayed” TitleBarText =“Input Text” DefaultText = “Default Text In Box” X = InputBox(MessageText, TitleBarText, DefaultText)

Practice Exercise 127 Dim MyName As String

MyName = InputBox (“Please enter your name”, “Personal Details”) Range(“A1”).Value = MyName If MyName = ““ Then MsgBox “You didn’t enter your name” Else MsgBox “Name Is “ & MyName End If

For Eva

luatio

n Only


Page 39

7.25 ActiveCell This is used to prompt and place on a spreadsheet in the active cell. Practice Exercise 128 Dim MyName As String

MyName = InputBox("Enter Your Name", "Name Field", "Enter Your Name Here") ActiveCell.Offset(0, 1).Value = MyName

Practice Exercise 129 Input Box Example

To start, select a blank cell. Then run the following code. Sheets("Sheet1").Select Range("B8").Select x = "Enter Your Full Name" y = "Name Entry Input Box" Z = "Please Enter Your Full Name Here" a = InputBox(x, y, Z) Selection.Value = a Selection.Font.Bold = True Selection.Font.ColorIndex = 3

Optional Topics

7.26 Limitation Once the input box is displayed, you can’t select a sheet. 7.27 Constants These are variables whose values are predefined and unchangeable. The name of

the Constant will reflect its purpose. Constants are also often used to define static numeric values.

Practice Exercise 130 Prompt User for variable name. NewName = InputBox(“Type in Name”) ActiveCell.Value = NewName

7.28 Date Date datatypes are not supported as input type parameters. Therefore, be sure to specify the layout of a date so the cell will recognize the value as a date.

Practice Exercise 131 MyDate = InputBox(“Enter the date in DD/MM/YYYY format”)

Application.InputBox Method

The application method works like the Input Box. However, now you will be able to interact with the spreadsheet and select a cell reference as the answer.. Although the pop-up box will look a little different, you can still interact with the worksheet unlike a normal InputBox..

Practice Exercise 132 In Excel: File TabOpenEmployment1.xlxs. 7.29 Application InputBox

When the Application Input Box opens, click on the sheet.

Practice Exercise 133 Dim Lastname as String LastName = Application.InputBox(“Enter Last Name”) Range(“B2”).End(xlDown).Offset(1,0).Value = LastName

7.30 Application InputBox Type

The “Type” parameter returns the datatype of the Inputbox. If you define the Application.InputBox as a number, it has built in validation. You must enter a number or it will return as an error. Type=0 is used for formulas (This will actually place the cell reference) Type=1 is used for Numbers Type=2 is used for Strings Type=4 is used for True/False Type=8 is used for Cell References (This will actually place the value of the reference in the resulting cell. This is the default value if not defined)

For Eva

luatio

n Only


Page 40

Practice Exercise 134 Dim Status As Integer 'The following could be entered as ("Enter Status",,,,,,1) Status = Application.InputBox(Prompt:="Enter Status", Type:=1) Range("F2").End(xlDown).Select Cells(ActiveCell.Row + 1, ActiveCell.Column).Value = Status

Student Project K Create an Application.InputBox for Department. When you receive the prompt, use your mouse to select the department name.

Optional Topics

Practice Exercise 135 Date

In Excel: File TabOpenEmployment1.xlsx. Date data types are not supported, but try using Type=1. If the number looks like a date it will interrupt it as a date. Be sure to specify the Date format in the prompt. Dim MyDate As Integer MyDate = Application.InputBox(Prompt:="Enter Date DD/MM/YY", Type:=1) Range("D5").End(xlDown).Select Cells(ActiveCell.Row + 1, ActiveCell.Column).Select ActiveCell.Value = MyDate Note: 12/20/99 seems to work but 12/20/15 doesn’t.

User Form

Practice Exercise 136 In Excel: File TabNewBlank Workbook 7.31 User Form Right-Click on Sheet1 in the VB EditorInsertUserform.

Create UserForm ‘This will pop up the user form1 Userform1.Show

7.32 Form Storage Once you create a form, you can retrieve it in the forms folder located in the VB Editor.

7.33 Toolbox The Tool Box has the building blocks to create a form.

ViewToolbox.

7.34 Label This will be used to create text boxes in the form area. 7.35 TextBox This is one of the primary tools used to build input forms.

For Eva

luatio

n Only


Page 41

Practice Exercise 137 Right-Click on Sheet1 in the VB EditorInsertUserform. Create the form to the right.

7.36 Properties Properties can be used to name the input

boxes. You will also be using Properties to change the name of the form and the caption View TabProperties. Select the Name box

7.37 Run

This is used to view the actual look and layout of the user form. In order to execute the code, you will need to open the form (located in the tree) and press Run. The code is usually tied to a button on the form. An alternative Run technique is pressing the F4 key or Run menuRun Sub/UserForm.

7.38 Command Button

This is used to execute data entered in the form.

7.39 Show Model In order to select the worksheet while the form is open, Set the form properties

Show Model to False. PropertiesBehaviorShow Model = False.

For Eva

luatio

n Only


Page 42

Student Project L User Input Form Step1 - Create User Input Form

1. Create a user Form with Labels Full Name, Phone, and Address. 2. Use a Text Box and change the Property Name to: FullName, Phone, Address. 3. Add a CommandButton. Property Name: CommandButton1 Text On Button: Send To Worksheet

4. Double Click on “Send To Worksheet” Button to see the code.

5. Enter the following code: Private Sub CommandButton1_Click() Range("A2").Value = FullName.Text Range("B2").Value = Phone.Text Range("C2").Value = Address.Text End Sub 6. Create labels on the worksheet in cells A1, B1, and C1.

Test it out: Open Code and Run Sub/Userform or press F5. Input the data and press Send To Worksheet.

Student Project M User Input Form Step2 – Send Data To Worksheet

You must complete the previous step before continuing. Increase the functionality to move the next blank cell using the xlDown option. Range("A1").Select ActiveCell.End(xlDown).Select Row1 = ActiveCell.Row Cells(Row1 + 1, 1).Value = FullName.Text Cells(Row1 + 1, 2).Value = Phone.Text Cells(Row1 + 1, 3).Value = Address.Text

Student Project N User Input Form Step3 – Get Data

You must complete the previous step before continuing. Create a new button called “Get Data.” Double click on the button to see the code and type the following: Private Sub CommandButton2_Click() FullName.Text = Range("A2").Value Phone.Text = Range("B2").Value Address.Text = Range("C2").Value End Sub

For Eva

luatio

n Only


Page 43

Student Project O User Input Form Step4 – Get Data Last Record

You must complete the previous step before continuing. Modify the Get Data code to get the last record in the list. Range("A1").Select ActiveCell.End(xlDown).Select Row1 = ActiveCell.Row FullName.Text = Cells(Row1, 1).Value Phone.Text = Cells(Row1, 2).Value Address.Text = Cells(Row1, 3).Value

Student Project P User Input Form Step5 – Create Button

This will assign a button on the Excel sheet to open the user form. In VB: Create a new VB ModuleCreate a subroutine to perform the following: Sub GetUserForm () Userform1.Show End Sub In Excel: Insert Ribbon TabShapesRectangleDraw Rectangle. In Excel: Right Click on the RectangleAssign MacroGetUserForm

For Eva

luatio

n Only

Page 44

Chapter 8 - Loop Control Structures Loops will provide the ability to repeat operations in order to perform necessary repetitive tasks.

Lesson Objective - This chapter will review Tasks that will repeat or loop multiple times including For Loop and Do Loop operations.

Concept Explanation / Command String in italics. Practice Exercise 138 In Excel: File TabOpenLooping1.xlsx.

Note: Be sure to monitor the variables in the Locals Window. 8.1 For Next Loop The FOR command is at the beginning of the loop and the NEXT command is

located at the end of the loop. The criteria placed just after the FOR command will indicate how many times it will loop. A counter is usually placed after the FOR command to count the number of loops.

Practice Exercise 139 Add a number for the number of records found. Dim Counter As Integer Range("A5").Select ActiveCell.Value = "Counter" For Counter = 1 To 10 ActiveCell.Offset(Counter, 0).Value = Counter Next Counter Rewrite the formula to stop after the last row of data. Hint: Set the end counter to: Range("B6").End(xlDown).Row - 5

Practice Exercise 140 Count the number people in the “Administration” department. Dim i As Integer Dim count As Integer count = 0 i = 1 For i = 1 To Range("E6").End(xlDown).Row - 5 If Range("E6").Offset(i, 0).Value = "Administration" Then count = count + 1 End If Next MsgBox Count 2. Add a variable to watch (ViewWatch) different parameters: Test1 = Range("E6").End(xlDown).Row Add a variable to watch the column E change to the new position. Test2 = Range("E6").Offset(i, 0).Value Add to the watch window Count or i.

8.2 Loop Counter Names

Common names used for counters are i, x, num, and loopcounter.

8.3 Debug.Print This will display the counter in the Immediate Window. Practice Exercise 141 View TabImmediate Window.

Dim i As Integer For I = 1 to 10 Debug.Print “I = “ & i Next Try using “Next” or “Next i” at the end for clarity.

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 8 - Loop Control Structures

Page 45

8.4 Step by 2 This will count every other number starting with 1. Practice Exercise 142 Continue from the previous exercise.

Dim i As Integer For I = 1 to 10 Step 2 Debug.Print “I = “ & i Next

8.5 Count Backwards

This will count backward starting with 10.

Practice Exercise 143 Continue from the previous exercise. Dim i As Integer For I = 10 to 1 Step -1 Debug.Print “I = “ & i Next

8.6 Looping Over A Range Of Cells

This will loop through a group of cells using the For Loop.


Dim SingleCell As Range Dim ListOfCells As Range Set ListOfCells = Range("F6", Range("F6").End(xlDown)) For Each SingleCell In ListOfCells If SingleCell.Value > 3 Then Debug.Print SingleCell.Offset(0, -4).Value End If Next SingleCell


Enter the For Loop Example For x = 1 To 10 Cells(x + 5, 8).Value = Cells(x + 5, 7).Value * 0.1 Next x Add the ability to continue looping to the end of the list.


Sheets(1).Select Range("B6").Select x = InputBox("Run This Macro How Many Times?") For MyVariable = 1 To x Cells(5 + MyVariable, 1).Value = "Day " & MyVariable Next MyVariable

Practice Exercise 147 This uses a for loop for each salary and bold the salary if greater than 5000. For Each BigSalary In Range("G6:G34") If BigSalary.Value >= 50000 Then BigSalary.Font.Bold = True Else BigSalary.Font.Bold = False End If Next BigSalary

For Eva

luatio

n Only


Page 46

Do Loop

Practice Exercise 148 In Excel: File TabOpenLooping1.xlsx. 8.7 Do Loop Do While Loop

This will repeat a group of VBA commands. The DO command is placed at the beginning of the loop. The LOOP command will mark the end of the group. The WHILE command will evaluate the criteria and is located next to the DO or LOOP commands.

Practice Exercise 149 Count the number of times the list contains the word “Administration.” Dim i As Integer Dim count As Integer count = 0 i = 1 Do Until Range("E6").Offset(i, 0).Value = "" test1 = Range("E6").Offset(i, 0).Value If Range("E6").Offset(i, 0).Value = "Administration" Then count = count + 1 End If i = i + 1 Loop MsgBox (count)

8.8 Do Until 1 This Do method will place the condition at the Do line. Practice Exercise 150 Do Method 1

Select cell B31and add the following code to the Do line: Do Until ActiveCell.Value = ““ ActiveCell.Offset(1,0).Select Loop The result is that it finishes in the blank cell.

8.9 Do Until 2 The Do loop will place the condition at the Loop line. Practice Exercise 151 Do Method 2

Select cell B31and add the following code to the Loop line: Do ActiveCell.Offset(1,0).Select Loop Until ActiveCell.Value = ““ The results are the same compared to the previous example.

Do While

8.10 Do While 1 Continue to loop while the cell contains a value. Practice Exercise 152 Do Method 3

Select cell B31 and add the While conditional statement. Do While ActiveCell.Value <> ““ ActiveCell.Offset(1,0).Select Loop The result is that it finishes in the blank cell.

8.11 Do While 2 Continue to loop while the cell contains a value, then loop at the end of the Do Loop.

Practice Exercise 153 Do Method 4

Select cell B31 and add the While conditional statement. Do ActiveCell.Offset(1,0).Select Loop While ActiveCell.Value <> ““ The results are the same compared to the previous example.

8.12 Do Exit Do Use an “IF” statement within a Do Loop to exit the Do Loop

For Eva

luatio

n Only


Page 47

Practice Exercise 154 Do Method 5

Select cell B31 and add the following code. Do If ActiveCell.Value = "" Then Exit Do Else ActiveCell.Offset(1, 0).Select test1 = Range("D6").Offset(i, 0).Value If Range("D6").Offset(i, 0).Value = "Administration" Then count = count + 1 End If i = i + 1 End If Loop Place the cursor in the starting cell.

Practice Exercise 155 Do Loop Example (FS)

Continue looping until you reach the end of the list. If the Active Cell is greater than 40000, bold the ActiveCell. Range("G6").Select Do If ActiveCell.Value > 40000 Then Selection.Font.Bold = True End If Selection.Offset(1, 0).Select Loop Until ActiveCell.Value = ""

Practice Exercise 156 If Then Else Example2

This example was demonstrated in a previous example. Enter it in and add the looping capability then store the bonus in a separate column. See previous example. Dim Bonus Range("F6").Select Top: If ActiveCell.Value > 5 Then Bonus = ActiveCell.Value * 0.3 Selection.Offset(0, -5).Value = Bonus ActiveCell.Offset(1, 0).Select GoTo Top ElseIf ActiveCell.Value > 3 Then Bonus = ActiveCell.Value * 0.2 Selection.Offset(0, -5).Value = Bonus ActiveCell.Offset(1, 0).Select GoTo Top ElseIf ActiveCell.Value >= 1 Then Bonus = ActiveCell.Value * 0.1 Selection.Offset(0, -5).Value = Bonus ActiveCell.Offset(1, 0).Select GoTo Top ElseIf ActiveCell.Value = "" Then GoTo Out End If Out:

For Eva

luatio

n Only


Page 48

While Wend Loop

8.13 While Wend A While Wend loop structure is similar to a DO Loop. When the condition is TRUE, the macro loops. When the condition is False, the loop stops.

Practice Exercise 157 While Wend Example

Enter the following code in the VB Editor. Sub WhileWend() While X<100

Selection.offset(1,0).select X=x+1

Wend End Sub Assignment: Change the code to place a counter in the selected cell. Hint: use ActiveCell.Value = x

Student Project Q – Loop to remove blank lines in worksheet.

1. In Excel: File TabOpenPivot1 WBlanks.xlsx.

2. Use a loop structure to loop through each row and search for blank rows then delete the blank row. (Hint: There is only one blank row therefore if you see two blank rows you are at the end of the data area. Starting Document with blanks.


For Eva

luatio

n Only

Page 49

Chapter 9 - Worksheet and Workbook Manipulation This will allow the manipulation of data across worksheets and workbooks.

Lesson Objective – In this chapter, you will learn syntax to manipulate worksheets, add additional worksheets, and place data across worksheets. In addition, you will learn how to perform similar functionality over several workbooks.

Concept Explanation / Command String in italics. Practice Exercise 158 In Excel: File TabOpenSheets1.xlsx. 9.1 Worksheet Collection Object

This will identify the worksheet to be used. If a number is used, it will identify the position from the beginning of the list. The name of the worksheet can also be used. Worksheets(2) or Worksheets(“Sheet3”).

9.2 Activate This will select or activate a different worksheet. Practice Exercise 159 Worksheet

Worksheets(“Sheet3”).Activate Or Sheets(“Sheet4”).Activate Or Worksheets(5).Select or Sheet1

9.3 Name Property

This is used in conjunction with the object and will have a period ( . ) between them. Usually it is followed by an = and double quotes around the property name. Example: ActiveSheet.Name = “MySheet1”

9.4 ActiveSheet Object

This is a VBA object that represents the currently active worksheet in the workbook. You can either rename a worksheet or pull the name to be used in the worksheet spreadsheet. Example: ActiveSheet.Name = “Sheet1” Where Sheet1 is the name of the worksheet.

Practice Exercise 160 Rename ActiveSheet

Use the ActiveSheet object with the name property to rename the Worksheet. ActiveSheet.Name = “Test Worksheet”

9.5 Index Number Use the Index Number if you don’t know the worksheet names. This is the physical position of the worksheet.

Practice Exercise 161 Worksheets(2).select Or Sheets(3).select 9.6 Add Default Worksheet

This adds a worksheet before the selected/active worksheet using the next default name.

Practice Exercise 162 Worksheets.Add 9.7 Add Worksheet With Name

This adds a worksheet with a specific name and appears to the left of existing worksheets.

Practice Exercise 163 After

Add worksheet after Worksheet2. Worksheets.Add After:=Worksheets(“Sheet2”)

Practice Exercise 164 Before

Add worksheet Before Worksheet2. Worksheets.Add Before:=Worksheets(“Sheet2”)

Practice Exercise 165 End

This appears always at the end of all worksheets. Worksheets.Add After:=Sheets(Sheets.Count)

Practice Exercise 166 In Excel: File TabNewNew Workbook. Run the following code. Dim x As Integer For x = 1 To 6 Worksheets.Add After:=Worksheets("Sheet" & (x)) Next x

9.8 Delete Worksheet

This will delete a worksheet.

Practice Exercise 167 Worksheets(2).Delete 9.9 Extract Name This will extract a name from the active worksheet and place the name in a cell.

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 9 - Worksheet and Workbook Manipulation

Page 50

Practice Exercise 168 Range("A1").Select ActiveCell.FormulaR1C1 = ActiveSheet.Name & " is the worksheet name" Range("A3").Value = ActiveSheet.Name & " is the worksheet name"

9.10 Rename Worksheet

This renames a worksheet.

Practice Exercise 169 Worksheets(“Sheet1”).Name = “NewName” Practice Exercise 170 For Next Loop Example4

In Excel: File TabOpenSheet1.xlsx. Note: This will not work on hidden sheets) For x = 1 To Worksheets.Count Worksheets(x).Select ActiveSheet.Name = ActiveSheet.Name & " Report" Next x

9.11 Count Property

The Count Property (Worksheets command) will identify the maximum number of worksheets. Example: Worksheets.Count

Practice Exercise 171 Select

Open Excel so you can view each selected worksheet. In Excel: File TabNewBlank workbook and create 5 worksheets. Name1 = Worksheets.count Dim x As Integer For x = 1 To Worksheets.count Worksheets(x).Select Next x

Practice Exercise 172 Rename

Continue from the previous exercise. 1. Change “Worksheets(x).Select” in the previous exercise to “Worksheets(x).Name = x” 2. Provide an actual name: “Worksheets(x).Name = “MySheet” & x

9.12 Move Worksheets

This moves worksheets to a specified location. The name Sheet1 used in the example below is the physical position or Worksheet(1). The use the worksheet name use Worksheet(“Sheet1”).

Practice Exercise 173 In Excel: File TabNewBlank workbook and create 5 worksheets by pressing on the bottom of the worksheet. Sheet1.Move After:=Worksheets(“Sheet3”)

9.13 Copy Worksheets

This copies a worksheet to specific locations.

Practice Exercise 174 Sheet1.Copy After:=Worksheets(“Sheet3”) 9.14 Paste Method This will paste information from the clipboard to an active cell.

Example: ActiveSheet.Paste Practice Exercise 175 In Excel: File TabOpenWorksheets2.xlsx.

Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Copy Worksheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste

For Eva

luatio

n Only


Page 51

Practice Exercise 176 For x = 1 To Worksheets.Count - 1 Worksheets(x).Select Range("A1").Select ActiveCell.CurrentRegion.Select Selection.Copy Worksheets(x + 1).Select ActiveSheet.Paste Next x

Practice Exercise 177 pasteSpecial

In Excel: File TabOpenWorksheets3.xlsx. Worksheets(“Sheet1”).Range("A1").CurrentRegion.Copy Worksheets(“Sheet2”).Range("A1").PasteSpecial Worksheets(“Sheet2”).Range("A1").PasteSpecial (xlPasteColumnWidths)

9.15 Select 2 Worksheets

The false statement after selecting a sheet will keep the previous sheet selected. If you apply Range(“A1”).Value = “Test” directly it doesn’t seem to work.

Practice Exercise 178 Manually select sheet4 then type the following in a new subroutine: Worksheets(“Sheet1”).Select Worksheets(“Sheet2”).Select False Test the following: Range(“A1”).Select Try replacing the last two lines with Selection.Value = “Test” Range(“A1”). Value = “Test”

9.16 Charts The “Charts” Command provides access to charts located on a specific sheet. Practice Exercise 179 Charts

Charts(“Chart1”).Activate Or Sheets(“Chart1”).Activate

9.17 Hide Worksheets

This will Hide a worksheet using the standard Hide command.

Practice Exercise 180 Sheet1.Visible = xlSheethidden 9.18 Unhide A Worksheet

This will Unhide a worksheet using the standard Unhide command.

Practice Exercise 181 Sheet1.Visible = xlSheetvisible 9.19 Very Hidden This is a special hide and you will not be able to unhide the sheet manually. You

must use the VB command “Sheet1.Visible = xlSheetVisible” to make it visible. Practice Exercise 182 Sheet1.Visible = xlSheetVeryHidden

To Unhide the very hidden worksheet use: Sheet1.Visible = xlSheetVisible 9.20 Protect All Sheets

This will protect all sheets. It does not assign a password, but can add the option if desired. To unprotect Review Ribbon TabUnprotect.

Practice Exercise 183 Protect

In Excel: File TabOpenWorksheets3.xlsx. Run the following code. Dim SingleSheet As Worksheet For Each SingleSheet In Worksheets SingleSheet.Protect Next Singlesheet

Practice Exercise 184 Protect

In Excel: File TabOpenWorksheets3.xlsx. Run the following code. Dim i As Integer Dim NumberOfWorksheets As Integer NumberOfWorksheets = Worksheets.Count For I = 1 to NumberOfWorksheets Worksheets(i).Protect Next

For Eva

luatio

n Only


Page 52

9.21 Unprotect All Sheets

This will unprotect all sheets.

Practice Exercise 185 Unprotect

Continue from the previous exercise and enter the following code: Dim SingleSheet As Worksheet For Each SingleSheet In Worksheets SingleSheet.UnProtect Next Singlesheet

Practice Exercise 186 For Next Loop Example3

Application.DisplayAlerts = False For x = 1 To 12 Worksheets.Add after:=Sheets(x) ActiveSheet.Name = Format(DateSerial(1, x, 1), "MMMM") Next x Sheets(1).Delete Sheets(1).Select

9.22 Turn Off/On Popup Message

This turns on and off popup messages when prompted to confirm. It can be used on a temporary basis and turned on at the end of the program. Application.DisplayAlerts = True Application.DisplayAlerts = False

Practice Exercise 187 Display Alerts

Worksheets("Sheet4").Delete Application.DisplayAlerts = False Worksheets("Sheet3").Delete Application.DisplayAlerts = True

9.23 Add Password The protected sheets did not require a password, but a password can be added by using the proper password option. Add the password to the “SingleSheet.Protect” Command. SingleSheet.Protect (123) Or ActiveSheet.Protect(123)

Student Project R – Create a backup of Sheet1, Protect, and Hide it as Very Hidden.

1. In Excel: File TabOpenPivot3.xlsx. Possible Solution – Use as a last resort. 2. Make a backup of Sheet1 and place it at after Sheet5. 3. Rename the backup sheet to Backup1. 3. Protect the backup sheet using a password. 4. Hide the backup sheet as Very Hidden. Possible Solution – Use as a last resort.

Student Project S – Delete the backup when you run backup code twice.

1. Create a for loop to search through each sheet using Sheets(Count). 2. If the sheet name is equal to Backup1 then delete it. 3. Add the backup1 code (above) under this code to create a backup. Possible Solution – Use as a last resort.

For Eva

luatio

n Only


Page 53

Workbook

9.24 Workbook This will manipulate multiple opened workbooks. 9.25 Copy Method This will copy the active sheet and create a new blank workbook.

ActiveSheet.Copy Practice Exercise 188 Copy

In Excel: File TabOpenWorksheets4.xlsx. Run the following code. Workbooks("Worksheets4.xlsx").Activate Worksheets(1).Activate Range("A1").CurrentRegion.Copy Range("A20").CurrentRegion.PasteSpecial

Practice Exercise 189 Continue from previous practice exercise. Workbooks("Worksheets4.xlsx").Worksheets("Sheet1").Range("B4").Value =2000

9.26 List Workbook Names

This will list all opened workbook names.

Practice Exercise 190 This will close all opened Excel workbooks. Open the Immediate window: ViewImmediate Window. In Excel: File TabOpenWorksheets1.xlsx, Worksheets2.xlsx, Worksheets3.xlsx, and Worksheets4.xlsx. Type and run in the following code: Dim SingleBook As Workbook For Each SingleBook In WorkBooks Debug.Print SingleBook.Name Next SingleBook

9.27 Protect Sheets Workbooks

This protects all workbooks.

Practice Exercise 191 Protect All Sheets

In Excel: File TabOpenWorksheets5.xlsx. Dim SingleSheet As Worksheet For Each SingleSheet In Workbooks("Worksheets5.xlsx").Worksheets SingleSheet.Protect Next SingleSheet

9.28 Close All Workbooks

This closes all opened workbooks except the workbook that contains the macro.

Practice Exercise 192 Close All Workbooks

This will close all opened workbooks except the active workbook. Type and run the following code: Dim SingleBook As Workbook For Each SingleBook In WorkBooks If SingleBook.Name <> ThisWorkBook.Name Then Singlebook.Close End If Next SingleBook

Practice Exercise 193 Close All Workbooks

This is another method to close all workbooks: Type and run the following code. Dim i As Integer Dim NumBooks As Integer NumBooks = Workbooks.Count For i = 2 to NumBooks Workbooks(i).Close Next i

For Eva

luatio

n Only


Page 54

9.29 Password Protect Workbook

This will run the code when the workbook is opened. Place the code at the workbook icon level by double clicking on the ThisWorksheet Icon in the Project explorer.

Practice Exercise 194 Password Workbook Example (FW)

To run the subroutine on open, place the code in the ThisWorksheet code window: (ViewProject Explorer WindowRight-Click on This WorkbookView CodePaste the following code). Private Sub Workbook_open() x = InputBox("Enter Your Password") If x <> "12345" Then MsgBox "Wrong Password" Workbooks.Close Else Sheets("sheet1").Select MsgBox "Welcome to the Budget Worksheet!" End If End Sub

Student Project T – Close All Workbooks

1. Write VB code to close all open workbooks. Possible Solution – Use as a last resort

For Eva

luatio

n Only

Page 55

Chapter 10 - Functions Functions will use built-in functions as well as custom functions.

Learning Objective – You will learn how to use Functions such as Dates, Sum, and randomization techniques.

Concept Explanation / Command String in italics. Practice Exercise 195 In Excel: File TabOpenSheets3.xlsx. 10.1 Excel Function A Function is a standard VB code that has the label of Function in the place of

the Sub. It can be executed from an Excel Worksheet. If you want to execute the function from another workbook, you must enter the full path =Sheet1.xlsx!Test2()

Practice Exercise 196 1. In Excel: File Tab OpenSheets1.xlsx. 2. Create the following function. Function Test2() Dim Test As Integer Test = 2 MsgBox Test End Function 3. Go to Excel and type: =Test2()

4. Go to a different Workbook and type: =Sheets1.xlsx!Test2() 5. It is available in the User Defined

category of the Insert Function

Call – Calls another subroutine

10.2 Call This is used to run a macro from another macro. You can organize several macros to perform a specific function and use the Call command to run them in a specific order. The Call will also run a defined Function, and when finished, will return to the originating Call program. Example: Call MacroName

Practice Exercise 197 Call a Function

The subroutine will Call the function and pass the necessary variables (A, B, Answer). The Function will then calculate the numbers and return the answer to the subroutine. In Excel: File TabNewBlank workbook. Sub Test() Sub CallSub1() Dim A, B, Answer As Integer Call PrintLength(5) A = 2 End Sub B = 3 Call Calculate1(A, B, Answer) Sub CallSub2() Range("A1").Value = Answer Call PrintLength(Len(Range(“B5”).Value End Sub End Sub Function Calculate1(A, B, Answer) Sub PrintLength(CellLen Answer = A * A * B ActiveCell.Value = CellLen End Function End Sub

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 10 - Functions

Page 56

Practice Exercise 198 Call Example

In Excel: File TabOpenCallExample.xlsx. Select cell B5 then run the following VB code: Sub MessageBoxExample3() Sheets(1).Select Range("B3").Select Do If ActiveCell.Value > 2000 Then ActiveCell.Font.Bold = True Selection.Offset(1, 0).Select End If If ActiveCell.Value <= 2000 Then ActiveCell.Font.Bold = False Selection.Offset(1, 0).Select End If If ActiveCell.Value = "" Then Call RunAgain End If Loop Until ActiveCell.Value = "" End Sub Sub RunAgain() verify = MsgBox("Run This Macro Again?", vbOKCancel, "Run Again Verification") If verify = vbOK Then Call ClearAllFormats Call MessageBoxExample3 End If If verify = vbCancel Then MsgBox "Audit Complete" End If End Sub Sub ClearAllFormats() Sheets(1).Select Range("B3").Select ActiveCell.CurrentRegion.Select Selection.ClearFormats End Sub

Using Excel Formulas in VB Code (Not all Excel functions are supported).

Practice Exercise 199 In Excel: File TabOpenWorksheetVB2. 10.3 Formula Property

The Formula property allows you to insert a formula into a cell. The formula must be enclosed in double quotes. For example, the following statement enters a SUM formula into cell A10: Range("A10").Formula = "=SUM(A1:A9)"

10.4 HasFormula Property

This property returns True if the single-cell Range object contains a formula. Otherwise, it returns False. Example: Range("B4").HasFormula

Practice Exercise 200 Create a formula and run the following command: MsgBox Range("B4").HasFormula

10.5 Date Function This extracts the date from the system clock Practice Exercise 201 Date

MsgBox “Today’s Date Is: “ & Date

For Eva

luatio

n Only


Page 57

10.6 Len Function This will determine the length of a value. Practice Exercise 202 Len

Type the word TEST in cell A5. Dim NameLength As Integer Dim LengthDesc As String NameLength = Len(Range("A5").Value) If NameLength < 12 Then LengthDesc = "Short" Else LengthDesc = "Long" End If Range("C5").Value = NameLength & " is " & LengthDesc

10.7 Math.Rnd Function

This generates a random number.

Practice Exercise 203 Math.Rnd

Open the Immediate Windows to see the results of this VB Code. Dim i As Integer Dim RandomNumber As Double For i = 1 To 100 RandomNumber = Math.Rnd Debug.Print “Rand= “ & Rand If RandomNumber > 0.7 Then GoTo EEE End If Debug.Print "I = " & i Next EEE:

10.8 Sum Function Sums a range of data. Practice Exercise 204 AutoSum Example

This example will sum the total row for all sheets. Note: To understand the result, add the following in order to see the area defined: myData.Select and myTotal.Select In Excel: File TabOpenWorksheetVB2. Sub AutoSumExample() Dim x As Integer ' Declare the Range objects here: Dim myData As Range Dim myTotal As Range For x = 1 To Worksheets.Count Worksheets(x).Select 'Set the data range object: Range("A7").Select Set myData = ActiveCell.CurrentRegion 'Position the totals range object below the data range: Set myTotal = myData.Offset(myData.Rows.Count).Rows(1) 'Insert the SUM function in the totals range object: myTotal.Formula="=sum(" & myData.Columns(1).Address(False, False) & ")" 'Modify the first three cells of the totals range object: myTotal.Range("A1").Value = "Total:" Next x End Sub

Sum Function

For Eva

luatio

n Only


Page 58

Run Code When Excel Opens

10.9 Run Code On Open

To run code when a workbook opens could be used to verify who opened it, provide information, or do some preliminary setup. Note: The security settings set up by your IT department may prevent you from starting VB code automatically.. Double Click on the Excel ThisWorksheet Icon and enter the desired code. The subroutine must start with the word Private and the subroutine must be called Workbook_Open. Private Sub Workbook_Open () Code End Sub

Practice Exercise 205 1. In Excel: File TabOpenStartUp.xlsm.

2. VBEditor: Project TreeDouble click on the Excel Workbook Icon. 3. Type in the following code: Private Sub Workbook_Open() MsgBox "Welcome to the StartUp Workbook" End Sub 4. Close Vb Editor, save the workbook, and close the workbook. 5. In Excel: File TabOpenStartUp.xlsm. 6. After the security screen appears you should get the Message Box prompt.

Optional Topics

10.10 Library Add-In Files

An easier way to make your custom functions available at all times is to store them in a separate workbook. The workbook can then be saved as an add-in (an XLA file) in your XLStart folder. (The XLStart folder is a subfolder of the folder containing your Excel files. When you begin Excel, the program opens any documents it finds in XLStart.) To save a workbook as an add-in, choose File, Save As (or File, Save). Then choose Microsoft Excel Add-in from the Files Of Type list.

Practice Exercise 206 Create Excel file with Functions: In Excel: File TabSave AsType: Excel Add-In (*.xlamLocation: XLStart\MyFunctions.xla. Add File to Add-Ins: File TabOptionsAdd-InExcel Add-insGoBrowseXLStartMyFunctions.xlam.

For Eva

luatio

n Only

Page 59

Chapter 11 - Custom Programming Techniques

Concept Explanation / Command String in italics. Practice Exercise 207 User Defined Function Example (FS)

Create a function to calculate the bonus based on an input value. Create a Sub that defines the sales amount and call the bonus function. In Excel: File TabOpenWorksheetVB2.xlsx. Dim bonus1 As Long Sub CallSales() Sales = 28000 Call Bonus(Sales) MsgBox bonus1 End Sub Function Bonus(Sales) Select Case Sales Case Is > 100000 bonus1 = Sales * 0.4 Case Is > 50000 bonus1 = Sales * 0.3 Case Is > 25000 bonus1 = Sales * 0.2 Case Else bonus1 = Sales * 0.1 End Select End Function

Practice Exercise 208 Select Row Range Example

This will define range B3:E6. Then select the 3rd row of the range B3:E6 (which happens to be cell B5 to E5.) In Excel: File TabOpenWorksheetVB2.xlsx. Set x = Range("B3:E6") x.Rows(3).Select y = MsgBox("Do you want to bold?", vbYesNo) If y = vbYes Then Selection.Font.Bold = True Else Selection.Font.Bold = False End If

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Chapter 11 – Custom Programming Techniques

Page 60

Practice Exercise 209 Select Col Range Example

This will define range B3:E6. Then select the 3rd column of the range B3:E6 (which happens to be cell D3 to D6.) In Excel: File TabOpenWorksheetVB2.xlsx. Set x = Range("B3:E6") x.Columns(3).Select y = MsgBox("Do you want to bold?", vbYesNo) If y = vbYes Then Selection.Font.Bold = True Else Selection.Font.Bold = False End If

Practice Exercise 210 In Excel: File TabOpenWorksheets6.xlsx Sheets("sheet1").Select Range("a2").Select y = ActiveCell.Address ActiveCell.End(xlDown).Select ActiveCell.End(xlToRight).Select z = ActiveCell.Address Range(y, z).Copy

Practice Exercise 211 Outlook Email Example

The following example takes an opened Excel file and emails it. One enhance that can be made is search for code on the internet to browse for a specific file.. In Excel: File TabOpenWorksheets6.xlsx. Set MyOlApp = CreateObject("Outlook.Application") Set myitem = MyOlApp.CreateItem(olMailItem) Set myAttachments = myitem.attachments myAttachments.Add "C:\Data\Excel2013-5\Format1.xlsx" Set myRecipient = myitem.Recipients With myRecipient .Add ("[email protected]") End With With myitem .Subject = "Sample Item" .Body = "This is a Sample Message." End With myitem.Display myitem.Send

For Eva

luatio

n Only


Page 61

Practice Exercise 212 How To Sort Worksheets In A File:File TabOpen Worksheets6.xlsx.

Reorder the sheets to Sheet5, Sheet2, Sheet3, Sheet1, Sheet4 Application.DisplayAlerts = False Dim x As Worksheet Dim y As String Worksheets.Add Before:=Sheets(1) ActiveSheet.Name = "zzz" Sheets(1).Select Range("a1").Select For Each x In Application.Worksheets ActiveCell.Value = x.Name Selection.Offset(1, 0).Select Next x Range("a1").Select Range("A1:A40").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess Do Until ActiveCell.Value = "" y = ActiveCell.Value Sheets(y).Move after:=Sheets(Sheets.Count) Sheets("zzz").Select Selection.Offset(1, 0).Select Loop Sheets("zzz").Delete

Practice Exercise 213 Rolling Up Data into Summary Sheet Macro Example: In Excel: File TabOpenWorksheets6.xlsx. 'This macro rolls up data from many sheets 'and puts it on the last sheet in the file. For x = 1 To Worksheets.Count - 1 Worksheets(x).Select Range("a1").Select ActiveCell.CurrentRegion.Select Selection.Copy Worksheets(Worksheets.Count).Select Range("a65000").Select ActiveCell.End(xlUp).Select Selection.Offset(3, 0).Select ActiveSheet.Paste Next x

Practice Exercise 214 Error Trapping Macro Example 1: In Excel: File TabOpenWorksheets6.xlsx. On Error GoTo 10 x = Worksheets.Count + 1 Worksheets.Add after:=Worksheets(1) ActiveSheet.Name = Worksheets(1).Name 10: If Err.Number = 1004 Then

MsgBox ("Worksheet Name Cant Be Same As Another Sheet") ActiveSheet.Name = Worksheets(1).Name & x

End If

For Eva

luatio

n Only


Page 62

Practice Exercise 215 Error Trapping Macro Example 2: In Excel: File TabOpenWorksheets6.xlsx. On Error GoTo MyError Range("a2").Select ActiveCell.Offset(-1, 0).Select ActiveCell.Value = 5 ActiveCell.Offset(-1, 0).Select ActiveCell.Value = 5 End MyError: If Err.Number = 1004 Then MsgBox "You can't go above range A1" Else MsgBox "We have an unknown error" End If Exit Sub

Practice Exercise 216 Error Trapping Macro Example 3: In Excel: File TabOpenWorksheets6.xlsx. On Error Resume Next Range("a1").Select Selection.Offset(-1, 0).Select ActiveCell.Value = 2 Selection.Offset(5, 0).Select ActiveCell.Value = 112

Practice Exercise 217 In Excel: File TabOpenWorksheets6.xlsx. Range("b1").Select ActiveCell.EntireColumn.Clear ActiveCell.ColumnWidth = 9

For Eva

luatio

n Only

Page 63

Appendex A – Quick Reference Guide

Excel VBA Macros - Quick Commands Reference Examples Change font on current region. Activecell.currentregion.font.name= “Arial black” Select the current region around an active cell. Activecell.currentregion.select Select range B3:E6. Range(“b3:e6”).select Rename the first worksheet. Worksheets(1).name= “Contact Information” Rename the sheet called “Budget” to “Budget 2008.” Worksheets(“Budget”).name= “Budget 2008” Change the name of the active sheet to “Annual Budget.” Activesheet.name= “Annual Budget” Select the entire sheet. Cells.select Insert a Column. Selection.entirecolumn.insert Insert a Row. Selection.Entirerow.insert Type “Hello” in cell A1. Range(“A1”).value= “Hello” Type the word “Hello” in the selected area. Selection.value= “Hello” Type the name of the active sheet in A1, with the word “Report” at the end of the name. Range(“a1”).value=Activesheet.name & “ “ & “Report” Type the number 99 in A1. Range(“a1”).value = 99 Go to the 2nd sheet in the file. Worksheets(2).select Go to the sheet called “Budget.” Worksheets(“Budget”).select Change the font to Tahoma for all sheets in the file. For x = 1 to worksheets.count Worksheets(x).select Activesheet.font.name = “Tahoma” Next X Move 2 rows UP and 3 columns Left. Activecell.offset(-2,-3).select Move 2 rows Down and 3 columns Right. Activecell.offset(2,3).select Type the name of the current sheet one cell to the right WITHOUT moving there. Activecell.offset(0,1).value=Activesheet.name Copy the entire worksheet and place in a new file. Activesheet.copy Activesheet.paste Copy a selected area and paste it on sheet 4. Selection.copy

For Eva

luatio

n Only

Excel 2010 / 2013 VB Excel Quick Reference Guide

Page 64

Worksheets(4).select Activesheet.paste Autofit Column B through E. Columns(“B:E”).Autofit Put the address of the current cell in the current cell. Activecell.value=activecell.address Run another macro called Macro7 from this macro. Call Macro7 Go to the top of the column until the cursor hits data or a blank cell. Activecell.end(xlup).select Go to the right of the row until the cursor hits data or a blank cell. Activecell.end(xlToRight).select Add a new sheet in front of the 3rd sheet. Worksheets.Add Before:=Worksheets(3) Add a new sheet after the sheet called Budget. Worksheets.Add After:=Worksheets(“Budget”) Delete sheet 2. Worksheets(2).delete Copy sheet 2 and put it in front of sheet 4. Worksheets(2).copy before:=worksheets(4) Copy sheet 2 and put it after sheet 4. Worksheets(2).copy after:=worksheets(4) Move sheet 2 and put it in front of sheet 4. Worksheets(2).move before:=worksheets(4) Move sheet 2 and put it after sheet 4. Worksheets(2).move after:=worksheets(4) Print Preview the 2nd sheet. Worksheets(2).PrintPreview Print Preview all sheets. Worksheets.PrintPreview Print out the sheet called “Sales.” Worksheets(“Sales”).Printout

Note: This Quick reference guide was obtained from Expand Learning of Utah.

For Eva

luatio

n Only

Page 65

Excel 2010 / 2013 VB Excel Index

Active Cell ....................................................... 16 Address Property ........................................... 20 Application.InputBox .................................... 39 Arrays ............................................................. 28 AutoFit ............................................................ 22 Border ............................................................. 22 Call .................................................................. 55 Case Else ......................................................... 32 Cell Select ........................................................ 18 Charts .............................................................. 51 Clear Method .................................................. 17 Column/Row Property ................................... 17 Column/Row Select ........................................ 17 Columns .......................................................... 17 Concatenation ................................................. 23 Constants ........................................................ 39 Copy Method .................................................. 16 CurrentRegion................................................ 20 Date Function ................................................. 56 Debug.Print..................................................... 44 Delete Method................................................. 17 Do Exit ............................................................ 46 Do Loop ........................................................... 46 Do Until ........................................................... 46 Do While ......................................................... 46 Fill (Interior)................................................... 22 Font ................................................................. 22 Formula Programming ..... 3, 11, 16, 22, 30, 35,

44, 49, 55, 59 Formula Property .......................................... 56 FormulaR1C1 ................................................. 16 Function .......................................................... 55 Goto ................................................................. 31 HasFormula Property .................................... 56 If Statements

Arithmatic Operators ................................ 30 Comparison Operators .............................. 30 End If .......................................................... 31 Goto ............................................................. 31 If Structure ................................................. 30 Logical Operators ...................................... 30 Nested If ...................................................... 31 Operators .................................................... 30

Input Box ActiveCell .................................................... 39 Parameters .................................................. 38 Value ........................................................... 38

X Value ....................................................... 38 Y Value ....................................................... 38

Input Box ........................................................ 38 Input Box .................................................... 38

InputBox Date ................................................ 39 Insert A Row .................................................. 17 Integer ............................................................. 26 Len Function .................................................. 57 Library ............................................................ 58 Loop Control

Count Backwards ...................................... 45 Do Loop ...................................................... 46 For Next Loop ............................................ 44 Step by 2 ..................................................... 45 While Wend ................................................ 48

Loop Counter ................................................. 44 Macros

Absolute Macro ............................................ 4 Form Buttons ............................................... 8 Macro Creation ............................................ 3 Relative Macro ............................................. 5 Security Levels ............................................. 3

Macros Developers Tab ............................................ 3

Math.Rnd Function ....................................... 57 MsgBox

Exclamation Button ................................... 36 Information Button .................................... 35 Input a Cell ................................................. 36 Message Box ............................................... 35 MsgBox ....................................................... 35 Option Name .............................................. 35 Question Warning Button ......................... 36 Red Circle Button ...................................... 36 Return Lines ............................................... 36 Store Results ............................................... 36 Yes/No ......................................................... 36

Name Property ............................................... 49 Nested Select ................................................... 33 NumberFormat Property .............................. 22 Object.Property Method ............................... 13 Offset Property .............................................. 19 Paragraph ....................................................... 22 Paste Method ............................................ 16, 50 PasteSpecial .................................................... 17 Private Sub ..................................................... 27 Quick Commands Reference ........................ 63

For Eva

luatio

n Only

Page 66

Quick Reference Guide .................................. 63 Range Name .................................................... 21 Range Object .................................................. 16 Range Select .................................................... 18 Range Select Cells .......................................... 19 REM ................................................................ 13 Row Property ................................................. 17 Run Code On Open ........................................ 58 Run Time Error ............................................. 15 Select Case ...................................................... 32 Select Case Statements .................................. 32 Select Property ............................................... 16 Select Sheet ..................................................... 20 Selection Object .............................................. 22 Set Object Variable ........................................ 26 Shape Buttons Create Macro Tab .................. 8 Shape Buttons Picture Buttons ....................... 8 Shape Buttons Quick Access Toolbar ............ 8 Shape Buttons Shape Buttons ......................... 7 Sub ................................................................... 13 Sum Function ................................................. 57 Text Property ................................................. 20 Unassigned Macro Keys .................................. 8 User Form ....................................................... 40 UserForm

Command Button ....................................... 41 Form Storage .............................................. 40 Label ............................................................ 40 Properties .................................................... 41 Run .............................................................. 41 TextBox ....................................................... 40

UserForm User Form ................................................... 40

UserForm Toolbox ....................................................... 40

Value Property ............................................... 16 Variable Name ................................................ 25 Variables

Assignments ................................................ 27 Const Variable ............................................ 27 Datatype Formats ....................................... 26 Datatypes .................................................... 26 Dim .............................................................. 25 Object Variable .......................................... 26 Private Variable ......................................... 27 Public Variable ........................................... 27 Set Object Variable .................................... 26 Static Variable ............................................ 27 Variables ..................................................... 25

VB Code .......................................................... 13 VB Comments................................................. 13

VB Editor BreakPoint .................................................. 15 Debug Run To Cursor ............................... 15 Debug Step-Into ......................................... 14 Debug Step-Out .......................................... 15 Debug Step-Over ........................................ 15 Debug Toolbar ........................................... 14 Immediate Window ................................... 12 IntelliSense ................................................. 15 IntelliSense Symbols .................................. 15 Locals Window ........................................... 12 Modules....................................................... 12 Modules Folder .......................................... 12 Option Explicit ........................................... 14 Personal Macro Project ............................. 11 Project Explorer ........................................ 11 Properties Window .................................... 12 Rename Module ......................................... 12 Reset ............................................................ 14 Run .............................................................. 14 Switch To Excel .......................................... 11 Turn Off Popup ......................................... 15 VB Module Font ......................................... 15 Watch Window .......................................... 12 Workbook Projects .................................... 11

VB Editor VB Editor.................................................... 11

VB Editor Minimize VB Editor .................................. 11

With Selection ................................................ 23 With Statements ............................................. 23 Workbook

Close All ...................................................... 53 Copy Method .............................................. 53 List Names .................................................. 53 Protect Sheets ............................................. 53 Workbook ................................................... 53

Workbook Manipulation .............................. 49 Worksheet

ActiveSheet Object .................................... 49 Add Password ............................................ 52 Add Worksheet .......................................... 49 Copy Worksheets ....................................... 50 Count Property .......................................... 50 Default Worksheet ..................................... 49 Delete Worksheet ....................................... 49 Hide Worksheets ........................................ 51 Index Number ............................................ 49 Move Worksheet ........................................ 50 Protect All Sheets ....................................... 51 Rename Worksheet .................................... 50

For Eva

luatio

n Only

Page 67

Turn Off/On Popups .................................. 52 Unhide A Worksheet .................................. 51 Unprotect All Sheets .................................. 51 Very Hidden ............................................... 51 Worksheet ................................................... 49

Worksheet Activate ....................................................... 49

Worksheet Manipulation .............................. 49 xlDown ............................................................ 19

excel 2013 - visual basic programming for · pdf fileonly excel 2013 - visual basic...

Documents