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...
Set Display Options For The Code Window
You can modify the display settings for the text that displays on the Code window in the Visual Basic Editor. You can change the text color, font type, and font size for the text that displays in the Code window. You can not only specify the text color but also the background color. Just like any basic editor, the Code window has predefined formatting for the type of text that displays in the window. For example, there is a definition for comments and a different definition for normal text. You...
Declare An Array
You can declare an array a group of the same type of data values. You declare an array in the same fashion as you declare any other variable. Just like other variables, you declare arrays as either local or global variable arrays. You specify the scope of an array with either the Dim, Private, or Public statements. See the section Using Global Variables for more information about setting the scope of a variable. You declare arrays to store a group of related data. The array stores data with the...
Vba Programming Basics Ohj
A procedure is simply a block of code that performs specific actions. Typically, when using VBA with Excel macros, you perform the actions with or on Excel objects. VBA provides essentially two types of VBA procedures Functions and Subroutines. The only real difference between the two types of procedures is that a function returns a value. If, up to this point, you have created all of your VBA code with the Macro Recorder, you have probably seen only subrountines. Because you cannot call...
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,...
Return A Portion Of A String
Instead of an entire string, you can use the built-in functions available in VBA to return only a portion of a string. These functions work well when you only want a smaller portion of a string. You can use three different functions to return a portion of a string. The Left function returns the specified number of characters starting at the left side, or beginning, of the string. The Right function returns the specified number of characters starting at the right side, or end of the string. Each...
Working With Other Workbooks And Files Ilp
' 0 Type Workbooks.OpenText Filename UserFile to open the file selected in the Open dialog box. Note See Open a Text File as a Workbook for more information on using the OpenText method. Switch to Excel and run the macro. The Text Files dialog box requests the workbook to open.
Defining Ranges Wsd
You can create named ranges within Excel by highlighting the selected range and clicking InsertONameODefine to display the Define Name dialog box. Type the appropriate range name in the Names in workbook field and click Add. The list box on the dialog box contains a list of all named ranges within the current workbook. You can see which cells are part of a specific named range by highlighting the range within the list box and viewing the corresponding range displayed in the Refers To field....
Working With The Excel Object Model 1
fclta Besides maintaining a list of all objects with their corresponding properties and methods, the Object Browser keeps track of the constant values assigned to object properties. It also keeps track of parameter values for various object methods and VBA functions. For example, the ChartType property enables you to specify the type for a chart. You need to use one of the XlChartType constant values as the value for this property. For example, ThisWorkbook. Chart.Type xlPie creates a pie...
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 Ktt
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
Delete A Macro From A Workbook
ou can remove macros from any workbook to reduce your list of available macros. Similar to cleaning a closet, you want to eliminate the stuff you no longer need. When you delete a macro, Excel removes the actual macro without affecting any changes previously applied to the workbook with that macro. For example, if you use the macro to sum a series of cells, the sum remains the same when you delete the macro. Excel immediately applies the macro changes to the worksheet and then no longer relies...
Move A Sheet
You can rearrange sheets within a workbook using the Move method with the Sheets object. When you move a sheet, you indicate the new location by specifying the name of the sheet that you want to place before or after the current sheet. The Move method has two optional parameters, Before and After. Although both parameters are optional, you can only use one of them. Use the Before parameter to specify the sheet in front of which you want to move the current sheet. Use the After parameter to...
Working With Other Workbooks And Files
Specifies how to handle links within the workbook. Type 0 for no updates, 1 to update external references, and 2 to update remote references. A value of 3 updates external and remote references. Indicates the delimiter character in the text file to separate data into cells in a worksheet. 3 Spaces 6 Delimiter parameter value Indicates original platform of text files and has three constant values. xlMacintosh, xlWindows, and xlMSDOS. If omitted, Excel uses the current operating system. Specifies...
Set Macro Security
Due to the increasing problem with computer viruses, specifically macro viruses, by default, Excel disables all macros in worksheets that you open, except those with a signature from a trusted source. You can have Excel open all macros regardless of source, or prompt you before opening unsigned macros, by modifying the macro security level. Digital signatures, which a creator uses to verify a macro's safety, remain attached to a macro or other file so long as no one modifies the macro or file....
Adding Control Statements Rta
You can specify a different value to increment the Counter variable. By default, the Counter variable for the For Next loop increments by one each time the loop executes. If you want to increment or decrement the Counter variable by a different numeric value, you can use the Step statement and specify the increment value. If you specify a positive value, the Counter variable increments by that value each time the loop cycles. If you specify a negative value, the Counter variable decrements by...
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...
Create A Userform Template
If you find that you are consistently creating the same type UserForm for displaying custom dialog boxes with your macros, you can create a UserForm template file to save time and effort. When you create UserForms the Visual Basic Editor attaches them to the project where you create them. Each time you create a new project you need to re-create the UserForm or copy it from another project using the Project window. See Chapter 2 for more information on working with the Project window. When you...
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...
Working With Worksheets Kvo
The Copy method produces the same results when you use it with the Chart object, Charts collection object, Worksheet object, and Worksheets collection object instead of the Sheets object. You can use these other objects when you only want to work with a specific type of sheet. For example, to place a copy of a worksheet at the beginning of the workbook, you type Worksheet 3 .Copy Before Worksheets 1 . This code places a copy of the third worksheet in front of the first worksheet. If the first...
Delete A File
VBA provides the ability to delete a workbook, or any other file using the Kill statement. You can use this statement to delete any file, as long as the user has permission to delete it. The following code illustrates the use of the Kill statement Kill pathname . The Kill statement requires one argument, the pathname. The pathname argument is a string referencing the files that you want to delete. To assure that Excel locates the files, the pathname argument must include not only the filename...
Working With Cells Sms
You can fill a range of cells in a specific direction within a worksheet using one of the Fill methods. For example, you may want to fill across a worksheet with the first value in the left corner of the range. VBA offers four Range object methods for filling in a specific direction FillUp, FillDown, FillRight, and FillLeft. You can use the FillUp method to fill a range of cells with the value specified in the last cell of the range. For example, if you have the range A1 A10 and apply the...
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...
Working With Cells Rjc
When you specify a value of True for the SearchFormat parameter and the ReplaceFormat parameter, Excel looks for the search and replacement format settings. If you want to use formatting as part of the search criteria, you need to specify the format settings for the FindFormat property of the Application object. Whereas, with the ReplaceFormat parameter need to specify the replacement format settings using the ReplaceFormat property. Typically these settings are specified at the top of the...
Defining Ranges Sug
If you protect a worksheet, you cannot remove or add cells. To eliminate any errors that may occur from trying to remove a range from a protected worksheet, you can use the AllowEdit property to determine if you can modify the range. The AllowEdit property returns a Boolean value of True if you can modify the specified range. In the example code, you can use the AllowEdit property to check the range to make sure you can modify a range before you call the Delete method. RangeDelete.Delete Shift...
Working With Worksheets Exc
Instead of setting the range, you can set a print area for a worksheet with the PrintArea property. You use this property with the PageSetup object, a child object of the Worksheets collection object. You assign a range of cells as the print area, for example ActiveSheet.PageSetup. PrintArea A 2 G 8. This code sets the range of cells in the print area to A2-G8. Even if cells outside that range contain data, Excel does not print them. The dollar signs in front of the row and column references...
Capture Input From A Custom Dialog Box
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...
Run A Procedure When Excel Creates A Workbook 1
You use the NewWorkbook event to determine when Excel has created a new workbook. The NewWorkbook event has one parameter value that passes into the subroutine. The Wb parameter contains the new created workbook. You can access any of the methods and properties of the new workbook to customize the created workbook. For example, you can use the Name property to return the name of the new workbook. See Chapter 9 for more information on working with the Workbook object. Creating the NewWorkbook...
Defining Ranges Gmv
Excel provides the Parse method that you can use to separate data values in one column into multiple columns. The method works well when you have string data that is all the same length, such as phone numbers. With the Parse method, you specify how the strings in each cell should break and Excel applies that format to each cell. There are two optional parameters for the Parse method. The first parameter, ParseLine, is a string containing left and right brackets indicating where the cells should...
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
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...
Add A Data Table To The Chart
You can add data tables to any chart you create. You use data tables to provide a list of the values you see on the corresponding chart. Because the data values that Excel plots on a chart can come from different ranges of data, data tables work well for showing the actual data values from the chart in a concise table. Excel stores the data table associated with a chart in the DataTable object. The HasDataTable property, associated with the Chart object, specifies whether a data table actually...
J 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,...
Working With Cells
When you paste values into cells, the cells are not always properly sized to hold the new values. If the values that you paste in the new cells are numeric and the cells are not wide enough for the entire number, Excel displays number signs, , indicating the cell is not properly sized. Excel provides some formatting options you can use with the Range object to resize cells so that values fit appropriately. To ensure that the values pasted in the cells display properly, you can use the...
Working With Other Workbooks And Files Lgw
The SaveAs method has several optional parameters that determine how the file saves. Remember to use the named parameter option to specify parameter values for the method. The SaveAs method has several optional parameters that determine how the file saves. Remember to use the named parameter option to specify parameter values for the method. Indicates the name and location to save the file. Contains an XlFileFormat constant that indicates the format to save file. See Appendix A for the...
Adding Control Statements
Because the body of the loop typically contains at least one statement that affects the results of the loop, you can use incremental statements, such as counter variables, within the body of a loop to change the condition of the loop. A counter variable has a specific constant added to it each time the loop executes. Typically, you declare a counter variable as an integer data type and initialize it with a start value outside the loop. Within the loop, you increment the variable by a constant...
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
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...
Working With Cells Vxe
You use the XlLineStyle constant values, outlined in the table, to specify the type of line to draw as the border for the range of cells. You use the XlBorderWeight constant values, xlHairline, xlMedium, xlThick, and xlThin, to specify the width of the line used to draw the border for the range of cells. The type of line drawn is based upon the XlLineStyle parameter value. Default value. Draws a continuous line around the range of cells. Draws a dashed line around the range of cells. Draws a...