Return on Investment and Return on Equity Business Model
Workbook model of key inputs to the ROI and ROE measures including profit margin, asset turnover and equity multiplier. Du Pont style analysis of performance using goal seek and multiple scenarios.Highlighting and Exporting Cell Notes
You can use VBA to highlight cells with notes before printing -- and also to copy cell notes into a worksheet for viewing, printing or exporting.Learn How to Add Running Totals to a Worksheet
Excel provides several methods for creating running totals.You can use a worksheet function like =SUM($A$1:A1) where the values to total are in column A and the formula is copied down column B. You can also use a visual basic macro to store a running total in the note field of a cell. When a new value is entered to the cell, the value in the note field is added to the value in the cell.Custom Dialog and List Box
You can use a simple Visual Basic for Applications procedure to display a custom dialog and list box. If you use worksheet ranges to control the list box, it is easy to return the selected value to a location on your worksheet. With only 12 lines of Visual Basic for Applications code, you can build a useful lookup feature.List Box Lookup
You can look up entries in a list box by entering the first few letters of the entry in an edit box. With a Visual Basic for Applications procedure, the matching entry in the list box will be highlighted. When you change the text in the edit box, the list box will scroll and change its selection value.Change Cell Formatting
You can modify recorded macros with Do Loop and With, End With to create Visual Basic for Applications procedures that perform an operation on a block of cells. Use the macro recorder to record the action of changing one cell, then modify the macro to loop through a selected range.With these Visual Basic for Applications Tools, you will be able to add power to your recorded Macros.Dialog Box to Filter Data
You can create an AdvancedFilter with a criteria range on a hidden worksheet. If you control the values in the hidden criteria range with a simple Visual Basic for Applications procedure, you can click on a button, enter criteria values with the InputBox method and change the filter. This technique if useful when you need to protect the criteria range and make the process of selection simple for the worksheet user.Custom Dialog Example
You can create a custom dialog that updates a database with a Visual Basic for Applications procedure. The dialog can add new records, and edit or delete existing records. If you want to find a record, you can use a scroll bar to page through the database. The Module page in this example includes comments designed for novice users of Visual Basic for Applications.Select Custom Header
You can have Excel store frequently used custom headers and choose them from a menu option in a worksheet. A Visual Basic for Applications procedure and custom dialog can be used to store header information, select a header using options buttons and copy the header information to the three header areas of the PageSetup object.Data Validation
A Visual Basic for Applications procedure and a custom dialog box can be used to prompt user to correct input errors. If you assign a Visual Basic for Applications procedure to the frame of the dialog box, you can set the focus of the dialog to highlight the first field with errors.Highlight Exception Values
You can use the OnEntry property to run an exception check. The procedure can change the font color of the cell and can perform an operation on the ActiveCell if you want to validate or change color cell where data is entered. There is a "hole" in the logic of OnEntry: when the user copies a cell and pastes the value on the worksheet the OnEntry property does not run.Force Uppercase Entry
You can use an OnEntry procedure to run a Visual Basic for Applications procedure each time that data is entered into the worksheet. The procedure can convert lowercase text to uppercase is the active cell is in a specified range. The example converts entries in the second (B) column to uppercase.Pop Up List box
You can create a Visual Basic for Applications procedures that creates a new list box "on the fly" when a command button is clicked. The list box can be positioned near the active cell and the value selected copied to the activecell. When the copy is complete, the list box can be deleted from the worksheet.Dialing a Phone Number
You can use the Shell command in VBA to start the Dialer Windows95 application and dial a phone number with your computer's modem. The combination of the Shell and Sendkeys commands can be used to automate many functions in Windows. Shell starts another Windows application and SendKeys sends keystrokes to that application. With Windows 3.11, you can use a similar macro to start the CardFile program and send keystrokes to activate it's AutoDialer feature.Input Validation
Excel's OnEntry property provides a way to validate data entered in a worksheet cell. You can set a worksheet's OnEntry property in an Auto_Open macro. When data is entered in a cell on the worksheet, the macro specified in the OnEntry property will run. If the data is not valid, the OnEntry procedure will display a message box and set an error message next to the invalid data.Displaying a Sequence of Colors in a Picture
In Visual Basic for Applications, you can set a Picture equal to the value of a cell. The picture will then show the contents of the cell .... or the portion of another picture that overlays the cell.
![[HRule Image]](../images/div.gif){kind=small}