EXCEL POWER PROGRAMMING WITH VBA MACROS (BINN, CARTY) (Z-Library)

EXCEL POWER PROGRAMMING WITH VBA & MACROS CARTY BINN

Copyright © 2022 CARTY BINN All Rights Reserved This book or parts thereof may not be reproduced in any form, stored in any retrieval system, or transmitted in any form by any means—electronic, mechanical, photocopy, recording, or otherwise—without prior written permission of the publisher, except as provided by United States of America copyright law and fair use. Disclaimer and Terms of Use The author and publisher of this book and the accompanying materials have used their best efforts in preparing this book. The author and publisher make no representation or warranties with respect to the accuracy, applicability, fitness, or completeness of the contents of this book. The information contained in this book is strictly for informational purposes. Therefore, if you wish to apply the ideas contained in this book, you are taking full responsibility for your actions. Printed in the United States of America

CONTENTS CONTENTS PART ONE STARTING EXCEL VBA PROGRAMMING CHAPTER ONE GETTING TO KNOW VBA WHAT IS VBA? KNOWING WHAT VBA CAN DO Inserting A Bunch of Text Automating a task, you perform frequently Automating repetitive operations Making your own command Creating a custom button Developing new worksheet functions Creating custom add-ins for Excel Creating complete, macro-driven applications ADVANTAGES OF VBA Understanding what VBA excels at Understanding the disadvantages of using VBA CHAPTER TWO BUILDING SIMPLE MACROS DISPLAYING THE DEVELOPERS TAB CREATING A MACRO Preparing the Environment Recording the Macro Running the Macro Viewing a Macro in the Visual Basic Editor Modifying the Macro Saving Workbooks that Contain Macros Understanding Macro Security Revealing More about the NameAndTime Macro PART TWO EMPLOYING VBA WITH EXCEL CHAPTER THREE WORKING IN THE VISUAL BASIC EDITOR Getting to Know the Visual Basic Editor Activating the VBE Exploring VBE components The menu bars Toolbar Project window Code window Immediate window IN THE VISUAL BASIC EDITOR, WHAT'S NEW? Working with the project explorer Adding a new VBA Module Removing a VBA module Exporting and importing objects Working with a Code Pane Window minimization and maximization Looking at the parts of a module Getting VBA code into a module Directly entering a code Using the Macro recorder

Copying VBA Code Customizing the VBE Using the Editor tab Auto Syntax Check option Require Variable Declaration option Explicit Option Auto List Members option Auto Quick Info option Auto Data Tips option Setting the Auto Indent Text Editing using Drag-and-Drop Default to Full Module View option Procedure Separator option Using the Editor Format tab Code Colors option Font option Size setting Margin Indicator Bar option Using the General Tab Using the Docking tab CHAPTER FOUR INTRODUCING THE EXCEL OBJECT MODEL IS EXCEL AN OBJECT? Understanding the Hierarchy of Objects Wrapping Your Mind around Collections Referring to Objects Getting around the hierarchy Setting object references Diving into Object Properties and Methods Setting objects properties Taking action with object methods Triggering actions with objects events Getting additional information from VBA resources Using the Help System in VBA Using the Object Browser Automatically listing properties and methods CHAPTER FIVE VBA SUB AND FUNCTIONS PROCEDURES UNDERSTANDING SUBS VERSUS FUNCTIONS Examining Sub-Procedures Looking at Function procedures Naming Subs and Functions Executing Sub procedures Directly executing the Sub procedure Executing the procedure from the Macro dialog box Executing a macro using a shortcut key Executing the procedure from a button or shape Executing the procedure from another procedure Executing Function procedures Calling the function from a Sub procedure Calling a function from a worksheet formula CHAPTER SIX USING THE EXCEL MACRO RECORDER RECORDING BASICS Getting Ready to Record Choosing Between Absolute and Relative Modes Absolute recording mode

Recording in relative mode What Gets Recorded? Specifying recording options for your Macro Macro name Shortcut key Store Macro In option Description Streamlining Macro Recorder-Generated Code PART THREE PROGRAMMING CONCEPT CHAPTER SEVEN ESSENTIAL VBA LANGUAGE ELEMENTS Making Use of Comments in VBA Code Using Variables, Constants, and Data Types What are VBA’s data types? Declaring and scoping variables Procedure-only variables Module-only variables Public variables Static variables Life of variables Working with constants Using Premade Constants Working with strings Working with dates Using Assignment Statements Examples of assignment statements About that equal sign Smooth operators WORKING WITH ARRAYS Array declarations Multidimensional arrays Dynamic arrays Making Use of Labels CHAPTER EIGHT WORKING WITH RANGE OBJECTS REFERRING TO RANGE OBJECTS Referring to a Range Using Properties The Cells property The Offset property Working with Range Properties The Value property The Text property The Font property The Interior property The Formula property The Number Format property USING RANGE OBJECT METHODS TO TAKE ACTION The Select method The Copy and Paste methods The Clear method The Delete method CHAPTER NINE USING VBA AND WORKSHEET FUNCTIONS Understanding Functions Using VBA's Built-In Functions

Examples of VBA functions VBA functions that do more than return a value Using Worksheet Functions in VBA Worksheet Functions Examples Determining the highest value in a range Entering worksheet functions More about using worksheet functions Using Custom Functions CHAPTER TEN CONTROLLING PROGRAM FLOW AND MAKING DECISIONS Going with the Flow, Dude The GoTo Statement WHAT IS STRUCTURED PROGRAMMING? DOES IT MATTER? Decisions, Decisions An If-Then-Else example Using ElseIf Another If-Then example THE SELECT CASE STRUCTURE An example of a Select Case A nested Select Case example Knocking Your Code for a Loop CHAPTER ELEVEN AUTOMATIC PROCEDURES AND EVENTS PREPARING FOR THE BIG EVENT Are events useful? Programming event-handler procedures Where Does the VBA Code Go? Writing an Event-Handler Procedure Triggering Workbook Events The Open event for a workbook The BeforeClose event for a workbook The BeforeSave event for a workbook Using Activation Events Activate and Deactivate events in a sheet Activating and deactivating events in a workbook Workbook activation events Other Worksheet-Related Events Why not using data validation? Preventing data validation from being destroyed Events Not Associated with Objects The OnTime event Keypress events CHAPTER TWELVE ERROR HANDLING TECHNIQUES TYPES OF ERRORS An Erroneous Macro Example Not quite perfect macros Perfecting the Macro Giving up on perfection Handling Errors Another Way Error handling in a nutshell Knowing when to ignore errors Identifying specific errors An Intentional Error CHAPTER THIRTEEN BUG EXTERMINATION TECHNIQUES

Species of Bugs Detecting Bugs Debugging Techniques Reviewing your code Using the MsgBox function. Using the VBA debugger Using the debugger’s tools Setting breakpoints in your code Stop Stepping through your code Using the Watch window Using the Locals window Tips for Bug Reduction CHAPTER FOURTEEN VBA PROGRAMMING EXAMPLES Working with Ranges Copying a range Copying a variable-size range Selecting the end of a row or column Selecting a row or column Moving a range Looping through a range efficiently On Error Resume Next Looping through a range efficiently (Part II) Prompting for a cell value Identifying multiple selections Changing Excel Preferences Changing Boolean settings Changing non-Boolean settings Working with Charts AddChart versus AddChart2 Modifying the chart type Looping through the Chart Objects collection Modifying chart properties Applying chart formatting VBA Quick Tips Turning off screen updating Disabling automatic calculation Eliminating those pesky alert messages Simplifying object references Declaring variable types PART FOUR COMMUNICATING WITH YOUR USERS CHAPTER FIFTEEN SIMPLE DIALOG BOXES Interacting with User In VBA The MsgBox Function Displaying Messages with the MsgBox Function Customizing message boxes Getting Data with an Input Box syntax Using the Input Box function Constructing a GetOpenFilename Statement The syntax for the GetOpenFilename method An example of GetOpenFilename Displaying Excel’s Built-in Dialog Boxes CHAPTER SIXTEEN

USERFORM BASICS Knowing When to Use a UserForm Working with UserForms Inserting a new UserForm Adding controls to a UserForm Changing properties for a UserForm control Viewing the UserForm Code window Displaying a UserForm Using information from a UserForm A UserForm Example Adding the CommandButtons Adding the Option Buttons Adding event-handler procedures Creating a macro to display the dialog box Making the macro available Testing the macro CHAPTER SEVENTEEN USING USERFORM CONTROLS GETTING STARTED WITH DIALOG BOX CONTROLS Adding controls Introducing control properties Dialog Box Controls: The Details CheckBox control ComboBox control CommandButton control Frame control Image control ListBox control MultiPage control OptionButton control ScrollBar control SpinButton control TabStrip control TextBox control ToggleButton control Working with Dialog Box Controls Moving and resizing controls Aligning and spacing controls Accommodating keyboard users Changing the tab order Setting hotkeys Aesthetics of Dialog Boxes CHAPTER EIGHTEEN USERFORM TECHNIQUES AND TRICKS Using Dialog Boxes A UserForm Example Creating the dialog box Making the macro available Trying out your dialog box Adding event-handler procedures Validating the data Now the dialog box works More UserForm Examples A ListBox example Filling a ListBox Determining the selected item Determining multiple selections

Choosing a range Using a TextBox and a SpinButton How this example works Creating a modeless tabbed dialog box Displaying a chart in a UserForm A Dialog Box Checklist CHAPTER NINETEEN ACCESSING YOUR MACROS THROUGH THE USER INTERFACE CUSTOMIZING THE RIBBON MANUALLY CUSTOMIZING THE RIBBON Tabs Groups Adding a macro to the Ribbon Using XML to customize the Ribbon Adding a new item to the Cell shortcut menu PART FIVE PUTTING IT ALL TOGETHER CHAPTER TWENTY CREATING WORKSHEET FUNCTIONS Make Custom Functions to Make Your Work Easier What Custom Worksheet Functions Can't Do? WORKING WITH FUNCTION EXAMPLES A function with one argument A function with two arguments A function with a range argument A function with an optional argument Introducing Wrapper Functions The ExtractElement function The SayIt function The IsLike function Working with Functions That Return an Array Returning a sorted list Using the Insert Function Dialog Box Displaying the function’s description Adding argument descriptions CHAPTER TWENTY-ONE CREATING EXCEL ADD-INS Add-Ins Defined Reasons to Create Add-Ins Working with Add-Ins Understanding the Basics of Add-Ins Looking at an Add-In Example Setting up the workbook Testing the workbook Protecting the VBA code Creating the add-in Opening the add-in Distributing the add-in Modifying the add-in PART SIX OTHER EXCEL PROGRAMMING ETHICS WORTH KNOWING CHAPTER TWENTY-TWO TEN HANDY VISUAL BASIC EDITORS TIPS Applying Block Comments

Copying Multiple Lines of Code at Once Jumping between Modules and Procedures Teleporting to Your Functions Staying in the Right Procedure Stepping Through Your Code Stepping to a Specific Line in Your Code Stopping Your Code at a Predefined Point Seeing the Beginning and End of Variable Values Turning Off Auto Syntax Check CHAPTER TWENTY-THREE RESOURCES FOR VBA HELP Allowing Excel to Generate Code for You Pilfering Code from the Internet Leveraging User Forums Visiting Expert Blogs Mining YouTube for Video Training Attending Live and Online Training Classes Learning from the Microsoft Office Dev Center Dissecting the Other Excel Files in Your Organization Asking Your Local Excel Guru CHAPTER TWENTY-FOUR TEN VBA DO’S AND DON’TS Declaring All Variables Don’t Confuse Passwords with Security Do Clean Up Your Code Don’t Put Everything in One Procedure Do Consider Other Software Don’t Assume That Everyone Enables Macros Do Get in the Habit of Experimenting Don’t Assume That Your Code Will Work with Other Excel Versions Do Keep Your Users in Mind Don’t Forget About Backups INDEX PART ONE STARTING EXCEL VBA PROGRAMMING

CHAPTER ONE GETTING TO KNOW VBA Grasp your horses if you're keen to get into VBA programming. There isn't a single piece of practical training material in this chapter. It does, nevertheless, offer some important supplemental materials that will help you learn how to program in Excel. In essence, this chapter sets the tone for the rest of the book and provides you with a sense of how Excel programming fits into the bigger picture. What is VBA? VBA stands for Visual Basic for Applications. Visual Basic for Applications (VBA) is a programming language created by Microsoft, the business that attempts to get you to purchase a new version of Windows every few years. Excel, like the rest of Microsoft Office, comes with the VBA programming language. In a nutshell, VBA is the tool that individuals like you and me use to create Excel-controlling applications. Consider a sophisticated robot who is well-versed in Excel. This robot can understand instructions and use Excel with great speed and accuracy. In Excel, you create a set of robot commands using specific codes whenever you want the robot to perform anything. Then you sit back and have a glass of water as the robot follows your orders. That's essentially what VBA is – a coding language for robots. Excel, on the other hand, does not include a robot or lemonade. The vocabulary used in Excel programming may be a little befuddling. VBA, for example, is a programming language that can also be used to create macros. What do you name something that was written in VBA and ran in Excel? Is this a program or a macro? VBA processes are often referred to as macros in Excel's Help system, therefore I use that nomenclature. But I also refer to this as a program. All through this book, I use the word automate. This word refers to the automated completion of a set of steps. You've automated those three stages if you develop a macro that applies color to certain cells, outputs the worksheet, and then eliminates the color. Macro doesn't quite stand for Messy and Confusing Repeated Operation, by the way. Alternatively, it is derived from the Greek makros, which means huge, and which also characterizes your salary after you have mastered macro programming. Knowing What VBA Can Do You're undoubtedly conscious that Excel is used for a wide range of activities. Here are a few such examples: Scientific data analysis Forecasting and budgeting Receipts and other forms are created. Creating graphs from data Making lists of client names, grade point averages, and Christmas present ideas The list can go on forever, but I'm sure you get the picture. My statement is basically that Excel is utilized for a broad range of jobs, and everybody viewing this book has distinct Excel

requirements and assumptions. The urge to automate some component of Excel is something that almost every user has in common. That is what VBA is all about, my reader. Build a VBA application to input certain statistics, then prepare and publish your month-end sales report, for instance. After you've finished writing and evaluating the program, you may run it with a unified program, which will have Excel do several moment tasks for you. Instead of struggling through a long list of instructions, you can just press a button and then go to Facebook to pass time while your macro works. In the subsequent sections, I'll go through some of the most popular applications for VBA macros. One or two of these may set off your alarm. Inserting A Bunch of Text You may develop a macro to type your firm name, address, and phone number into your spreadsheets if you need to do so often. You may take this idea as far as you want. For example, you might create a macro that automatically writes a list of all of your company's salesmen. Automating a task, you perform frequently Assume you're a sales manager who has to write a month-end sales report in order to please your employer. If the work is simple, you may write a VBA program to do it for you. Your manager will be pleased by the continuously high quality of your reports, and you will be promoted to a new position for which you are grossly underqualified. Automating repetitive operations If you need to conduct the same action on 12 distinct Excel workbooks, you may record a macro while doing the activity on the first workbook and then have the macro repeat the action on the others. Excel never complains about being bored, which is a wonderful feature. The macro recorder in Excel works similarly to a video recorder when capturing live activity. It does not, however, need the use of a camera, and the battery never has to be charged. Making your own command Do you have a habit of using the same Excel menu commands again and over? If that's the case, create a macro that combines these instructions into a single custom command that you can run with just a single keystroke or button click. You won't save much time, but you'll almost certainly be more accurate. And the person in the cubicle next to you will be blown away. Creating a custom button You may add your own buttons to your Quick Access toolbar that run the macros you create. Buttons that do magic are often quite impressive to office employees. You may even add additional buttons to the Ribbon if you truly want to amaze your coworkers. Developing new worksheet functions Despite the fact that Excel comes with hundreds of built-in functions (such as SUM and AVERAGE), you may develop custom worksheet functions to make your formulae easier. I promise you'll be astonished at how simple it is. (In Chapter 20, I teach you how to achieve this.) Even better, your own functions are displayed in the Insert Function dialog box, making them look built-in. It's all quite fancy. Creating custom add-ins for Excel

Several of the add-ins that come with Excel are presumably recognizable to you. The Analysis Tool Pak, for example, is a popular add-in. You may create your own special-purpose add-ins using VBA. People from all around the globe pay me actual money to utilize my Power Utility Pak add-in, which I created using just VBA. Creating complete, macro-driven applications You can utilize VBA to construct large-scale programs with a custom Ribbon tab, dialog boxes, onscreen assistance, and a variety of other features if you're prepared to put in the effort. This book doesn't go nearly that far, but I'm just mentioning it to show you how strong VBA can be. Advantages of VBA Excel allows you to automate practically any task. You accomplish this by writing instructions for Excel to follow. Using VBA to automate a job has numerous advantages: Excel always completes the assignment in the same manner. (Consistency is often a desirable trait.) Excel can do the work considerably quicker than you can (unless you're Clark Kent, of course). Excel always accomplishes the operation without mistakes if you're a skilled macro coder (which definitely can't be stated for you or me). If you put things up correctly, even someone who isn't familiar with Excel can execute the macro and do the work. Excel allows you to perform things that would otherwise be impossible, making you a popular person at work. You wouldn't have to sit beside your computer and grow bored with lengthy, time-consuming chores. While you're at the water cooler, Excel performs the job. Understanding what VBA excels at I've produced a quick-and-dirty description of what VBA is all about just to let you know what you're in for. Of course, I go into semi-excruciating detail about all of this later in the book. VBA allows you to conduct tasks by writing (or recording) code in a VBA module. The Visual Basic Editor is used to examine and modify VBA modules (VBE). Sub procedures make up a VBA module. Sub procedures have nothing to do with submarines or delectable sandwiches. Rather, it's a piece of computer code that does something with or on things (discussed in a moment). The AddEmUp Sub method is shown in the following example. When this fantastic application is run, the result of 1 + 1 is displayed:

A subpar process is one that does not work as expected. Function procedures may be included in a VBA module. A single value is returned by a Function procedure. You may use it as a function in a spreadsheet formula or call it from another VBA operation. The following is an example of a Function procedure (called Add Two). This function takes two integers as input (arguments) and returns the total of those numbers. A dysfunctional function process is one that does not operate properly. VBA is a program that manipulates things. You can alter thousands upon dozens of items in Excel. A workbook, a worksheet, a cell range, a chart, and a shape are all examples of objects. You have access to a lot more objects, which you can alter using VBA code. Objects are organized in a hierarchical order. Things may be used to encapsulate other objects. Excel is at the top of the object hierarchy. Excel is a kind of object known as an application. Other items, such as Workbook objects and Add-In objects, are contained inside the Application object. Other objects, such as Worksheet objects and Chart objects, may be contained inside the Workbook object. Range and PivotTable objects may be included inside a Worksheet object. The organization of these things is referred to as an object model. (Object-model aficionados may learn more in Chapter 4.) A collection is made up of objects of the same kind. The Worksheets collection, for example, contains all of the worksheets in a certain workbook. All Chart objects in a workbook are part of the Charts collection. Collections are things in and of themselves. A dot (sometimes known as a period) is used as a separator to relate to an item's location in the object hierarchy. For example, the workbook Book1.xlsx might be referred as Application. Workbooks("Book1.xlsx") This pertains to the Workbooks collection's workbook Book1.xlsx. The Application object contains the Workbooks collection (that is, Excel). To take it a step further, you might refer to Sheet1 in Book1.xlsx as Application. Workbooks("Book1.xlsx").

You may go this even farther by referring to a particular cell (in this instance, cell A1), as seen in the following example: Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1") Excel utilizes the active objects if you don't provide particular references. If Book1.xlsx is the active workbook, the above reference may be simplified as follows: Worksheets("Sheet1"). Range("A1") You may reduce the reference even further if you know that Sheet1 is the active sheet: Range("A1") Objects have attributes. A property may be thought of as an object's setting. Value and Address are two attributes of a Range object, for example. Has Title and Type are two characteristics of a Chart object. VBA may be used to determine and update the characteristics of objects. By combining the object name with the property name, separated by a dot, you may refer to a property of an object. For example, the Value attribute in cell A1 on Sheet1 may be referred to as follows: Worksheets("Sheet1").Range("A1").Value Variables may have values assigned to them. A variable is a kind of named element that keeps data. Variables may be used in VBA code to store things like values, text, and property settings. Use the following VBA statement to allocate the value in cell A1 on Sheet1 to a variable named Interest: Worksheets + Interest = ("Sheet1").Range("A1").Value Methods exist in objects. An action Excel takes with an object is referred to as a method. ClearContents, for example, is one of the Range object's methods. This technique clears the contents of the range, as its name suggests. A method is specified by combining the object with the method and separating them with a dot. The following sentence, for example, clears the contents of cell A1: Worksheets ("Sheet1"). Range("A1"). ClearContents VBA has all of the features found in current programming languages, including variables, arrays, and loops. To put it another way, if you're prepared to put in some effort to understand the ropes, you can build code that does amazing things. Understanding the disadvantages of using VBA You must be familiar with VBA programming (but that's why you purchased this book, right?). Thankfully, it isn't as complicated as you would think. Others who will be using your VBA applications will require their own versions of Excel. It would be wonderful if there was a button that turned your Excel/VBA software into a standalone program, but this isn't achievable (and probably never will be). Things go awry from time to time. To put it another way, you can't expect that your VBA application will always run properly in all situations. Welcome to the world of debugging and technical assistance, if others are utilizing your macros. VBA is an ever-changing target. Microsoft, as you may be aware, is constantly updating Excel.

Even while Microsoft makes a concerted effort to ensure that versions are compatible, you may find that the VBA code you've created is incompatible with previous versions or future versions of Excel.

CHAPTER TWO BUILDING SIMPLE MACROS By the conclusion of this chapter, you'll probably feel a lot better about this Excel programming thing, and you'll be pleased you took the risk. This chapter walks you through the process of creating a basic yet useful VBA macro. Displaying the Developers Tab You must go through the initiation ceremonies before you can call yourself an Excel coder. That means you'll need to make a little adjustment to get Excel to show a new Developer tab at the top of the screen. It's simple to get Excel to show the Developer tab (and you only have to do it one time). Simply follow the instructions below: Customize the Ribbon may be accessed by right-clicking any portion of the Ribbon and selecting Customize the Ribbon from the shortcut menu. Locate Developer in the second column of the Customize Ribbon tab of the Excel Options dialog box. Next to Developer, put a check mark. Click the OK button. You're back in Excel, but this time there's a new tab called Developer. When you select the Developer tab, the Ribbon shows information that programmers (that's you!) will find useful. The picture below displays the Ribbon in Excel 2022 when the Developer tab is chosen. Creating a Macro I'll show you how to make your first macro in this part. This is what the macro you're going to make does: Fills up a cell with your name. In the box below, enter the current date and time. Both cells are formatted to be bold. The font size in each cell is increased to 16 points. The Annual VBA Programmer's Competition will not award this macro any awards, but everyone has to start somewhere. All of these processes are completed by the macro in a single operation. You begin by documenting your behaviors as you move through these processes, as I explain in the following sections. The macro is then tested to determine whether it works.

Finally, you add some final touches to the macro. Ready? Preparing the Environment The procedures you perform before recording the macro are described in this section. To put it another way, you'll need to do a few preparations before you can start having fun: If Excel isn't already open, open it. Create a new, empty worksheet if required. Ctrl + N is my preferred method of doing this. Take a look at the Use Relative References button in the Code group on the Developer page. You're in excellent condition if the color of that button differs from the other ones. To activate this option, click the Use Relative References button if it is the same color as the other buttons. In Chapter 6, I go through the Use Relative References option in more detail. For the time being, simply make sure the option is switched on. The Use Relative References button will have a different color when it is switched on. Recording the Macro Carefully follow the following instructions: Choose a cell. Any cell will suffice. Select Developer Code Record Macro from the menu bar, or click the macro recording button on the status bar. The dialog window for recording macros appears. Give your macro a name. Excel comes with a default name (something like Macro1), but it's best to give it something more meaningful. This macro's name should be Name and Time (without spaces). Enter Shift + N (for an uppercase N) as the shortcut key in the Shortcut Key box. It is not necessary to provide a shortcut key. You may run the macro by using a key combination — in this example, Ctrl +Shift + N — if you define one. Ensure that the Store Macro In option is set to This Workbook. If you like, you can add some text to the Description box. This is an optional step. Some individuals like describing the macro's function (or is supposed to do). Click the OK button. Excel's macro recorder is switched on once the Record Macro dialog box closes. Excel now keeps track of everything you do and transforms it into VBA code: In the active cell, type your name. To input this formula, move the cell cursor to the cell below and type: =NOW() The current date and time are shown in the formula. To copy the formula cell to the Clipboard, select it and hit Ctrl + C.

Select Home > Clipboard > Paste Values from the drop-down menu (V). The formula is converted to its value using this command. Press the Shift key + up arrow when selecting the date cell to pick it and the one above it (which contains your name). Set the font size to 16 points and change the formatting to Bold using the options in the home Font group. Stop recording by selecting Developer Code. The macro recorder has been disabled. Running the Macro You may now test this macro to check whether it works correctly. To test your macro, use Ctrl+Shift+N in an empty cell. Excel performs the macro in a flash. In huge, strong characters, your name and the current date and time are shown. Another approach to running the macro is to open the Macros dialog box by selecting Developer Code Macros (or using Alt+F8). Click Run after selecting the macro from the list (in this example, NameAndTime). Before running the macro, make sure the cell that will store your name is selected. Viewing a Macro in the Visual Basic Editor You've created a macro and run it through its paces. If you're the inquisitive sort, you're undoubtedly puzzled about the appearance of this macro. You could even be curious as to where it's kept. Do you recall when you first began recording the macro? You specified that the macro is saved in This Workbook in Excel. The macro is saved in the worksheet, but you must first open it with the Visual Basic Editor (VBE). To view the macro, follow these steps: Select Developer Code Visual Basic (or press Alt+F11) from the drop-down menu. The Visual Basic Editor application window displays. Because this window can be customized so much, your VBE window may appear a little different. The VBE program window is particularly scary since it includes multiple other windows. Don't worry; you'll get accustomed to it in no time. Look for the Project window in the VBE window. The Project window (also known as the Project Explorer window) displays a list of all currently active workbooks and add-ins. Each project is organized as a tree that may be enlarged (to display more information) or contracted (to display less information) (to show less information). The VBE employs a variety of windows, each of which may be opened or closed. If a window isn't instantly visible in the VBE, you may show it by selecting an option from the View menu. If the Project window isn't visible, for example, choose View Project Explorer (or press Ctrl + R) to bring it up. You may do the same thing with any other VBE window. In Chapter 3, I go through the components of the VBE in further detail. Choose the project that corresponds to the workbook where the macro was recorded.