Using The Offset Property

He Offset property provides another method for specifying a range of cells. Using the Offset property you can define a range that is a specific offset from another range with the offset being the distance, in rows and columns, between the new range and the existing range selection. You use two different parameters with the Offset property. Although both are optional, you specify at least one of the values or the current selection is returned. Use the RowOffset parameter to indicate the number...

Using The Visual Basic Editor

Instead of copying the macro code to the ThisWorkbook object , you can create a macro in the ThisWorkbook object code module that calls the macro code in the appropriate code module. For example, if you have a subroutine in a module called SetToolbars that you recorded with the Macro Recorder, you can create another subroutine called Workbook_Open() in the ThisWorkbook object that calls the recorded macro, as shown in the following sample code. From the Workbook_Open() subroutine, you can call...

Vba Programming Basics

You can reference a specific cell in a worksheet with the Cells property. With this property, you can reference a specific cell in a worksheet using one of two methods. The first method enables you to specify the row and column number of the appropriate cell. By specifying the row and column number to reference cell A5, you can type Cells(5,1). This is intepreted as the cell in the fifth row and the first column. Using the cell reference method, you can reference rows from 1 to 65,536 and...

Retrieve Current Date And Time

VBA gives you the ability to retrieve the current date and time information from your system using some of its built-in functions. VBA includes several date-related built-in functions that you can add to subroutines and functions that you create. You use these functions to return a system date or time, perform date calculations, set a date, and even time a specific process. If you want to include the current date and time information, you can select from three different functions. The Date...

Defining Ranges

Besides referencing specific blocks of cells to create Range objects, you can specify a Row or Column as a Range object with the Rows and Columns properties. If you specify an entire column as a range selection, Excel creates a Range object containing the specified column. For example, the following code selects Column B and the selected column You can use the Rows property to specify a row you want to use as a Range object. With this property, Excel selects the entire row as the Range object...

Convert A List Into An Array

By converting a list of common values to an array, you can access the individual values quickly using one variable. You can convert a list of values to an array using a variety of different methods. You assign values to arrays by referencing the index values of each element. Arrays use index values to identify each of their elements. For example, if an array has 10 elements with a lower bounds of 1, the third element in the array has an index value of 3. In order to assign a value to an array...

Working With The Excel Object Model

You can capture the results of a button pressed on a displayed dialog box by assigning the Show property statement to a variable. The value of this variable is True if the user clicks OK, and False if the user clicks Cancel in the dialog box. Excel has over 240 different dialog boxes that display throughout its application. You can display any of these dialog boxes using the appropriate constant. The following table lists a few of the most commonly used Excel dialog boxes The Delete dialog box,...

Modules

The Project Explorer lists a node for each module within the project. Modules contain general procedures, either functions or subroutines. Excel creates a new module for a project each time you add a new macro to the corresponding workbook. You can add other modules within the Visual Basic Editor, as outlined later in this chapter. Not all modules contain macros that are visible within Excel. You can create hidden procedures that are called by other functions and subroutines. The Properties...

Excel Macros

Although Microsoft did not originate the concept of macros and spreadsheets, they have definitely built upon it. The combination of the Macro Recorder and VBA makes macro creation a powerful feature of Excel. The Macro Recorder provides a great method for creating a macro without writing VBA code directly. The Macro Recorder holds true to its name. Just like a tape recorder, when you turn it on, it records all the events that occur within Excel. Excel takes the recorded events and creates the...

Working With Cells

Excel remembers the values specified for the LookIn, LookAt, SearchOrder, and MatchByte parameters. If you run a search again without these parameter values, Excel uses the settings from the previous Find or Replace method. These values are also modified if you run a Find or Replace from within Excel. To avoid running searches that have unexpected values set, you should set these values each time you run the method. You can continue a search and find the next match using the FindNext method. In...

Execute A Procedure At A Specific Time

You can create a procedure that executes at a specific time by capturing the OnTime event. For example, you can set a reminder message that pops up at a specific time while editing a workbook. Unlike most other events, the OnTime event is not associated with a specific object. You, therefore, must access this event using the OnTime method that is associated with the Application object. There are four different parameters you use with the OnTime method, with only the first two being required...

Format A Date Expression

You can custom format an expression using a specific date or time with the FormatDateTime function. Doing so returns a Variant data type value with the specified formatting. See Chapter 3 for more information on data types. The FormatDateTime function uses two different arguments, of which only the first argument is required Date, and NamedFormat. The Date argument identifies the date expression that you want to format and accepts cell references, variable references, string expressions, or...

Add Comments To A Cell

You can add comments to any cell in a worksheet using the AddComment method with the Range object. Comments are great methods for adding extra information about the value in a cell, such as how it was calculated, who provided the information, and so on. Using the AddComment method is basically the same as clicking InsertOComment within Excel. The biggest difference is that by default when you create a comment in Excel, the name of the person creating the comment is always added to the top of...

Custom Paste Values In Cells

You can customize how values paste into a worksheet from the Windows Clipboard by using the PasteSpecial method. You can use the PasteSpecial method with values that have been added to the Windows Clipboard using the Cut or Copy methods, or even values placed there directly from Excel. With the PasteSpecial method, you can customize how the cell contents are pasted into the new range by only pasting the cell formats or even by adding the cell values to the contents of the cells from where you...

Run A Procedure Before Losing A Workbook

You can create a procedure that runs automatically before a particular workbook closes in Excel. Because this type of procedure only executes once as the workbook closes, it works well for removing custom menus and toolbars loaded when the workbook opened, closing other workbooks, recalculating, or even automatically saving the workbook. The procedure executes when the workbook closes by catching the BeforeClose event that the closing workbook triggers. To produce a procedure that executes when...

Assign A Digital Signature To A Macro

You can assign a digital signature to any of your macros. You attach signatures to code in a macro, or file, to signify that the code is valid and that no one has modified it since you applied the signature. You can create two types of digital signatures certified digital signatures and personal digital signatures. You acquire certified digital signatures from commercial agencies, such as VeriSign, Inc. The signatures of choice when you distribute your code to other users, commercial agencies...

Working With Worksheets

Keep in mind that you cannot hide every worksheet in a workbook. If you attempt to do so, Excel displays a Run-time error indicating that it is unable to set the Visible property of the Worksheet class. This message essentially means that you cannot hide all worksheets. To avoid receiving that message, make sure you leave one worksheet visible. To ensure that another user does not hide the worksheet that you want to keep visible, consider using the Visible property. This property checks that a...

Using Excel Worksheet Functions

You can add almost all of the Excel worksheet functions to your VBA code. VBA provides a very limited number of built-in functions. By using the various functions available within Excel, you can add functionality that is not available with the existing VBA functions. For example, Excel provides several different financial functions that you can use within your macros. To place an Excel worksheet function in your VBA subroutine or function, you use the WorksheetFunction property along with the...

Automating Procedures With Excel Events

Vba Scripts

Understanding Excel Events Run a Procedure as a Workbook Opens Run a Procedure Before Closing a Run a Procedure Before Saving a Workbook Run a Procedure When Excel Creates a Workbook Monitor a Range of Cells for Changes Execute a Procedure at a Specific Time Execute a Procedure When You Press Keys Run a Procedure When Right-Clicking a VBA and Excel Object Model Quick What's on the Using the E-Version of the Hungry Minds, Inc. End-User License Excel Programming Your visual blueprint for creating...

An Introduction To

Ou use the Visual Basic for Applications (VBA) programming language to create all macros within Excel. Although most people use VBA for macro development, VBA is actually much more powerful than just a macro language. A member of the Visual Basic (VB) family, VBA sits between Visual Basic and VBScript as far as overall functionality. Amazingly enough, VBA actually provides a large portion of the language elements that VB uses, including forms, controls, objects, modules, and dataaccess...

Working With Other Workbooks And Files

Ex m Using the Close method you can specify that you want to close all workbooks you have open in Excel. If the SaveChanges parameter does not have a value specified, Excel checks each workbook to ensure that you have saved it since its last modification. If a workbook contains modifications, Excel prompts you to save the workbook. When you close all workbooks, the workbooks all close but the application, Excel, remains running. If you want the Excel application to close, you can use the Quit...

Excel Programming

Your visual blueprint for creating interactive spreadsheets At maranGraphics, we believe in producing great computer books one book at a time. maranGraphics has been producing high-technology products for over 25 years, which enables us to offer the computer book community a unique communication process. Our computer books use an integrated communication process, which is very different from the approach used in other computer books. Each spread is, in essence, a flow chart the text and screen...

Create A Custom Toolbar

You can design a VBA procedure to create new toolbars within Excel where you can place links to the custom macros you create. You create a new toolbar by adding a new CommandBar object to the CommandBars object collection. Excel comes with approximately 30 different built-in toolbars, to which you can add controls. By creating new toolbars to house your custom toolbar options, you do not affect the layout of the standard toolbars. of True for the MenuBar parameter. If you only want the toolbar...

Add Controls To A

You can add controls to a toolbar that correspond to VBA macro code or any other Excel commands that you want to execute when selecting that toolbar control. You can add controls to any toolbar available within Excel. You can add existing Excel controls to the toolbar, or you can add new controls. When you add a new control to a toolbar, you can specify the icon image to represent the control along with the tool tip text, which displays when you drag the cursor across the control. You add a new...

Open A Workbook

Can create a procedure to open a workbook in Excel using the Open method of the Workbooks collection. Each time you open another workbook, Excel adds that workbook to the Workbooks collection. Similar to using the Open command on the File menu, opening another workbook using the Open method makes the workbook active. Sixteen different parameters determine how Excel opens a workbook. Of these parameters, Excel requires only FileName. In addition, you only need to use the FileName, ReadOnly,...

Working With Commandbars

By using toolbars and shortcut menus, you can provide quick access to commonly used commands. Excel has more than 50 different built-in toolbars and about 60 different shortcut menus, all of which are part of the CommandBars collection. This collection also contains any new custom toolbars and menus you create. Due to their central location, you can easily make modifications to existing menus and toolbars, or add new ones as needed. UNDERSTANDING THE COMMANDBARS COLLECTION The CommandBars...

Copy And Paste Ranges Of Cells

You can copy and paste cell ranges within a worksheet using the Copy method. The Copy method is essentially the same as the Copy and Paste commands within Excel, except that in Excel you are required to use two commands that is, you first copy the desired range and move to the appropriate location, and then select the Paste command. The Copy method associated with the Range object provides the ability to copy the values from a specific range and paste the values either in the Windows Clipboard...

Bz

Excel enables you to add custom menus to house links to VBA macros or other commonly used Excel commands. You can design a VBA procedure to create new menus that display within Excel. Typically you place most menus on the active menu bar. The active menu bar in Excel is the first object in the CommandBars collection. All menus you add become CommandBarControls on the active menu bar. You can create a new Excel menu with the Add method associated with the CommandBarControls collection. Although...

Vba And Excel Object Model Quick Reference

EXCEL OBJECT MODEL CONSTANTS CONTINUED MSDOS comma-separated values format. MS Windows comma-separated values format. Text file based on current operating system. Excel 2.0 format - Far East version. Text file created for a printer .prn . EXCEL OBJECT MODEL CONSTANTS CONTINUED Lotus 3.x and Lotus 1-2-3 for Windows format.

Protect A Worksheet

You can use the Protect method to password protect a worksheet so that other users cannot modify it. You can allow certain types of modifications, such as inserting rows, by specifying the appropriate parameter value for the Protect method. The Protect method has several different optional parameters that enable you to customize the type of protection that you assign to the workbook. Most of these parameters accept only True or False to indicate whether that type of protection is active for the...

Working With Charts

Chart Basics Create a Chart Sheet Embed a Chart within a Worksheet Apply Chart Wizard Settings to a Chart Add a New Data Series to a Chart Format Chart Text Create Charts with Multiple Chart Types Determine Variations in a Series of Data Add a Data Table to the Chart Customize the Chart Axis .256 .258 .260 .262 .264 .266 .268 .270 .272 .274

Run A Procedure Before Saving A Workbook

You can create a procedure that runs automatically before Excel saves a particular workbook. By creating this type of procedure, you can customize the method you use to save the workbook. For example, you may always want to display the Save As dialog box whenever the user selects the Save or SaveAs option in Excel. This procedure executes whenever you select the Save or the SaveAs options within Excel for the corresponding workbook. To create a procedure that executes before saving a workbook,...

Rename A Macro

You can very easily rename a macro that you created either with the Macro Recorder or in the Visual Basic Editor. Doing so, however, does require accessing the actual macro code within the Visual Basic Editor. When you create a macro in Excel using the Macro Recorder, Excel automatically writes the code for the macro in Visual Basic for Applications VBA . When you create a macro using the Macro Recorder, the only way that you can make changes to the macro code, or the macro name, is by...

Add A New Data Series To A Chart

After you create a chart, you can redefine the range of data Excel uses to display values on the chart by adding a new data series. A data series consists of a group of data values, which Excel displays on the chart. For example, if you have a bar chart showing the monthly sales in Dallas for each month the year, you can add another data series which contained the sales in Miami for the year. To define a new data series to add to the existing range of data, you create a new Series object and...

Embed A Chart Within A Worksheet

You can use VBA to embed a new chart to a worksheet in the existing workbook. When you embed a chart, Excel creates a new Chart object, which contains all the options that correspond to the chart. Each Chart object contains several objects that represent the settings for the chart, such as the ChartTitle object, which contains the chart title, its font and border properties, and other associated attributes. See the section Chart Basics for more information about the various child objects for...

Resize A Range

You can change the size of a range using the Resize property. Typically you resize a range because it does not contain the desired number of cells. When you resize a range, you change the number of rows and columns in a range. You can change the size by specifying either more or fewer rows or columns. The Resize property has two optional parameters of which you need to use at least one. If you do not use either parameter, Excel returns the original range. The first parameter, RowSize, indicates...

Close A Workbook

You can close a particular workbook from your macro using the Close method and including a reference to the Workbook object that contains the workbook you want to close. The Workbooks collection contains all of the currently open workbooks as individual workbook objects. The Workbooks collection adds the Workbook objects sequentially in the order you opened them. You reference a workbook with an index value, the name of the workbook, the ActiveWorkbook property, or the ThisWorkbook property....

Save A Workbook

Ou can save the currently selected Excel workbook using either the Save or Save As methods of the Workbook object. Excel has a different workbook object for each workbook you open. You can reference a specific workbook object by name, if you know the name. For example, the code Workbooks Sample, xls .Save saves the Sample.xls workbook. If you do not know the name of the workbook you want to save, you make the workbook the active workbook in Excel, and use the ActiveWorkbook property to save the...

An Introduction To The Excel Object Model

Designed around the ability to access and manipulate objects, VBA has access to an Object Model in each Microsoft Office product, including Excel, that enables you to interact with each application. Using the Object Model, you can access everything from the entire application to an individual cell in a worksheet. Objects represent the individual pieces of each application. Every object has specific properties and methods associated with it. You use properties and methods to capture events and...

Run A Procedure When Excel Creates A Workbook

You can use the NewWorkbook application event to create a procedure that executes whenever Excel opens a new workbook. When you use an application event, you capture the events that the application in this case, Excel creates. The NewWorkbook event triggers whenever Excel creates a new workbook. Because the event comes from the application and not an individual object such as a workbook, or chart, you may find the process a little more complex than capturing other object events. When working...

Capture Input From A Custom Dialog

You typically use dialog boxes in Excel to gather input from the user. The input you capture from a user can be anything from determining which button was pressed to actual values typed by a user. You can capture the user input from the dialog box so you can return the appropriate responses by using the UserForm events. For example, when the user clicks an OK CommandButton control you use a CommandButton_Click subroutine to indicate what steps to perform. Excel considers every user interaction...

Logical Operators

Logical operators evaluate expressions and return a logical value of True or False. For example, you can use a logical operator to compare two comparison expressions. Negates the value of the expression. If the expression is True the operator causes it to be false, or vice versa. Performs a logical conjunction of two expressions. If they are both True, the result is True. If either of the expressions is False the result is False. If either expression is Null the result is Null. Performs a...

Format Chart Text

Excel Vba Chart Formatieren

As with all elements of a chart, you can customize the text that displays on the chart by changing the font attributes. When Excel adds text to a chart either as the chart title, axis labels, or even data labels, it applies default formatting to the specified text. For example, typically the text for a chart title is formatted using the default font, normally Arial, and bold with a font size of 12 point. You can customize the text of the ChartTitle object, and all other objects on the chart...

Jyn

UNDERSTANDING EXCEL EVENTS amp - - An event occurs within Excel whenever you perform any type of action. For example, if you click a particular cell, a Click event occurs. You can use these events to trigger the execution of particular procedures by creating event-handling procedures. Event-handling procedures are exactly what the name describes, procedures that execute when a particular event occurs. You can monitor five different types of events within Excel workbook, worksheet, chart,...

Save Workbook In Format Specified By User

You can request the name, location, and format for saving a workbook file from the user of your macro with the GetSaveAsFilename method. Using this method displays the Save As dialog box into which the user entered information for saving the file. The dialog box does not save the workbook file instead, Excel returns the user specified information to the variable assigned to the statement. To save the file, you use the SaveAs method. See the section Save a Workbook for more information. The...

Call A Custom Dialog Box From A Procedure

You can call and display any custom dialog boxes that are part of the same project as your procedure. You use custom dialog boxes to gather user input. For example, you can use the dialog box to request the values you need from the user to perform the appropriate calculations within a worksheet. To display a custom dialog box, you use the Show method of the UserForm object. The Show method instructs Excel to display the specified UserForm. The Show method has only one optional parameter, as...

Create A Shortcut

Msobarpopup Access 2007 Form

You can create a shortcut menu that displays when a user performs a specific action that contains commands related to VBA macro and Excel commands. A shortcut menu is a pop-up menu that displays when you right-click a particular location within Excel. You can create new shortcut menus or modify existing Excel shortcut menus, and you perform all shortcut menu creation and modification within the Visual Basic Editor. A shortcut menu is similar to a toolbar in that both are actually CommandBar...

Create Custom Userform Controls

Excel 2000 Vba Userform

You can customize the Toolbox window to suit your needs. The Toolbox that displays when you select a UserForm within the Visual Basic Editor contains all of the standard controls you can add to the UserForm. These controls display on a single tabbed page called Controls. You can change the icon that a control uses as well as the tip text that displays when you drag you cursor across the icon. You can also create new controls to add to the Toolbox. add an OK button to all of your UserForms, you...

Userform Basics

You can use dialog boxes to request specific information from users by providing them an interface with your VBA code. Every Microsoft Windows application utilizes dialog boxes to gather information from the user, and Excel is no exception. As an example, you PARTS OF THE VISUAL BASIC EDITOR TOOLBOX frequently interact with the Open dialog box in Excel to select a file to open. VBA provides two standard dialog boxes MsgBox and InputBox. See Chapter 7 for more information on working with these...

Conditionally Call A Subroutine

You can conditionally move out of any location in a subroutine to run another subroutine or function. You can accomplish this by using a conditional VBA statement, such as an If Then statement, and combining it with a procedure call statement. When you combine the Call statement with a conditional statement, the other procedure is only called if the condition is met. When you use this type of structure, the If Then statement checks the specified condition. If the value of the condition is true,...

Open A File Requested By The User

Instead of specifying the file to open in your code, you can retrieve the name of the file by prompting the user with an Open dialog box. To display an Open dialog box from an Excel macro, you use the GetOpenFilename method. When you use this method, the file selected in the Open dialog box does not open when the user clicks OK. The dialog box passes the name of the file selected back to the variable that receives the statement assignment. If you want to open the selected file, you need to use...

Copy A Range To Multiple Sheets

You can copy a range of cells and place it in the same location on multiple sheets with the FillAcrossSheets method. When you use this method, Excel copies the specified cells to each worksheet you specify. You can copy everything in the range of cells, just the values in the cells, or only the formatting. When you use this method, you call it by indicating the range of worksheets where Excel should copy the cells followed by the FillAcrossSheets method. The range of worksheets must exist...

Apply Chart Wizard Settings To A Chart

When you create a new chart within Excel, the Chart Wizard appears to step you through the process and requires that you specify numerous properties such as the chart location and the chart data values. With VBA, you can use the ChartWizard method to quickly format a chart without the need to set each individual property. You use the ChartWizard method with a specific Chart object. This method includes eleven different optional parameters, which you can only use with this method and which...

Remove A Module

You can remove modules from the Visual Basic Editor rather quickly. As you work within the Visual Basic Editor, you may find that you have modules that you want to remove from a selected project. Typically, you delete modules that contain subroutines and functions that you no longer need for your project. When you remove a module that contains code for a macro used within Excel, remember that you can no longer access the macro. Also, if you remove a module that contains code referenced by a...

Macro Workbook

You can delete macros that you no longer use from the Personal Macro Workbook. The Personal Macro Workbook stores macros that you want to make available to all workbooks. Excel creates the Personal Macro Workbook when you store your first macro in it. After Excel creates the Personal Macro Workbook, the workbook opens as a hidden file whenever you run Excel. You can only tell that a hidden file exists by viewing the Macro dialog box, where Excel lists the macros used by the Personal Macro...

Insert A Range

Ou can insert a range of cells into a worksheet using the Insert method. When you insert a range of cells into a worksheet, Excel adjusts the values in the existing cells by moving them either down or to the right to allow you to insert into the specified location. For example, if you insert a new row of cells in row 3, Excel shifts the existing values in row 3 down to row 4. Excel shifts all remaining values within the worksheet down. If you add a new column, Excel shifts all existing values...

Customize The Chart Axis

You customize each axis on your chart with the Axis object properties and methods. Most charts that you create have a default of two different axes, the category axis and the values axis. For example, if you look at a standard column chart, the category axis runs horizontally across the bottom of the chart while the values axis runs vertically on the left side of the chart. When dealing with 3-D charts, there is a third series axis. Each chart axis is a separate Axis object. The Axes collection...

Validate Input From A Dialog

Excel Vba Input Dialog Box

You must validate the values specified for controls on a dialog box before passing the values back to your procedure. When you validate the data values, you do so for two major reasons. First, you ensure that the user specifies a value for a control. If the user forgets to select a control value, you can remind them immediately. Second, and probably most importantly, you ensure that errors do not occur in your code because the wrong type of data passes to a procedure. Then statement, to check...

Convert A Column Of Text Into Multiple Columns

You can break a column of text into multiple columns using the TextToColumns method. For example, if you have a list that contains both the first and last names in one column, you use TextToColumns to break that list into two different columns. You use the TextToColumns with the Range object containing the columns to parse into multiple columns. This method provides several different optional parameters to specify how to separate the text, including Destination, DataType, TextQualifier,...

Run A Procedure When Rightclicking A Chart

Position Chart Cell Vba

You can create a procedure that runs automatically each time a user right-clicks on a particular chart with the mouse. To create this type of procedure, you need to capture the BeforeRightClick event associated with the appropriate Chart object. To create a procedure that executes when a user right-clicks a chart, you create a new procedure and add it to the object code module for the particular chart. In fact, all event-handling procedures that you create for monitoring chart events must...

Display A Builtin Dialog

You can display all of the dialog boxes available in Excel in your macros by using VBA. By displaying a particular dialog box, you can incorporate that Excel functionality directly into your procedure. The Excel Object Model contains a Dialog object for each of the Excel dialog boxes. These objects are part of the Dialogs collection. You can access each of the Excel dialog box objects by specifying the corresponding constant value. The constant value for each dialog box begins with xlDialog...

Understanding Excel Events

Excel associates UserForm events not only with the form but also with each of the controls that exist on the form. You need to place event-handling procedures related to a UserForm in the code module for the UserForm object. The following table lists the available UserForm events. Excel adds a run-time control to the UserForm. The user performs a drag-and-drop operation. The user releases the mouse button to paste the data from the drag-and-drop operation. The user clicks the mouse on a...

Perform Mathematical Calculations

Ou can perform many types of mathematical calcuations within your macros. VBA provides several different operators for performing mathematical calculations within your procedures. Because Excel typically contains numeric values, you frequently use VBA operators to create Excel macros. VBA includes eight different arithmetic operators for performing calculations. These operators include addition , - subtraction , A exponential , multiplication , division , integer division , and MOD Modulo -...

Combine Multiple Ranges

You can use the Union method to create a muliple area range. A multiple area range contains more than one block of cells, which may not be connected. For example, you use the Union method to create a Range object containng the cells A1 through B5 and D1 through E5. Although these two groups of cells are separated within the worksheet, using the Union method you can create one range that references only those cells. When you use the Range property in conjunction with the Union method, you can...

Customizing Dialog Boxes Menus And Toolbars

EXISTING MENUS AND TOOLBARS CONTINUED An msoControlType constant value indicating the type of control to add. The five constant values include msoControlButton for a standard button, msoControlEdit for an edit box, msoControlComboBox for a combo box, msoControlDropdown for a drop-down list, and msoControlPopup for another pop-up list of controls or a menu pop-up. An integer value specifying the built-in control to add to the command bar. If you omit this parameter, or specify a value of 1,...

Compare Two Strings

You can compare two strings to see if they are alike using the built-in StrComp function in VBA. When you compare two strings, the StrComp function returns a value indicating whether the strings are the same. If the strings are different, the function returns a value that shows which string is larger. The StrComp function has three different arguments, of which the first two arguments are required string1, string2, and compare. The string1 and string2 arguments indicate the strings to compare....

Getting Started With Excel Macros

When you create a new macro, you have the option of assigning it to a keyboard shortcut by typing the shortcut in the Keyboard Shortcut box on the Record Macro dialog box. When you do this, the macro runs when you press the Ctrl key and the specified lowercase key simultaneously. If you specify an uppercase letter for the key, you can run the macro by pressing Ctrl Shift the specified key. Unfortunately, Excel does not stop you from creating shortcuts that override other predefined Excel...

Using The Object Browser

Liminating the need to remember required syntax, the Object Browser enables you to quickly search for an object, property, or method that matches a desired keyword. For example, to add a new worksheet when you do not remember the appropriate method, you can use the search option on the Object Browser to find all objects that deal with the Add method. The Object Browser refers to each object as a class and lists them within the Classes list box. The Object Browser lists all properties and...