Selecting The Maximum Value In A Range
This tip describes a simple utility that activates the worksheet cell that contains the maximum value. The VBA routine determines the maximum value in the selected range. If a single cell is selected, it determines the maximum value for the entire worksheet. Next, it use the Find method to locate the value and select the cell.
To use this subroutine:
- Copy the code below to a VBA module. You can also store it in your Personal Macro Workbook, or create an add-in.
- If you want to search for the maximum value in a specific range, select the range. Otherwise, select any single cell.
- Execute the GotoMax subroutine.
The GoToMax Subroutine
Sub GoToMax() ' Activates the cell with the largest value Dim WorkRange as Range ' Exit if a range is not selected If TypeName(Selection) <> "Range" Then Exit Sub ' If one cell is selected, search entire worksheet; ' Otherwise, search the selected range If Selection.Count = 1 Then Set Workrange = Cells Else Set Workrange = Selection End If ' Determine the maximum value MaxVal = Application.Max(Workrange) ' Find it and select it On Error Resume Next Workrange.Find(What:=MaxVal, _ After:=Workrange.Range("A1"), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False _ ).Select If Err <> 0 Then MsgBox "Max value was not found: " & MaxVal End Sub
A companion file is available: Click here to download
The number of permutations of a string is equal to the factorial of the length
of the string. For example, the word DOG has a length of three -- which means
that the letters can be rearranged in six different ways: DOG, DGO, ODG OGD, GDO,
and GOD. The number of permutations quickly gets unwieldy. The table below lists
the number of permutations for strings of various sizes.
This tip describes how to generate all permutations from a string. It uses a recursive subroutine to do the work. The source of this algorithm is not known (I was browsing through some old files on my hard drive and discovered it).
The GetString subroutine prompts the user for a string. If the length of the string is greater than 1 and less than 8, the GetPermutations subroutine is called --which then calls itself. The permutations are stored in column A of the worksheet.
Dim CurrentRow Sub GetString() Dim InString As String InString = InputBox("Enter text to permute:") If Len(InString) < 2 Then Exit Sub If Len(InString) >= 8 Then MsgBox "Too many permutations!" Exit Sub Else ActiveSheet.Columns(1).Clear CurrentRow = 1 Call GetPermutation("", InString) End If End Sub Sub GetPermutation(x As String, y As String) ' The source of this algorithm is unknown Dim i As Integer, j As Integer j = Len(y) If j < 2 Then Cells(CurrentRow, 1) = x & y CurrentRow = CurrentRow + 1 Else For i = 1 To j Call GetPermutation(x + Mid(y, i, 1), _ Left(y, i - 1) + Right(y, j - i)) Next End If End Sub
A companion file is available: Click here to download
This tip describes a useful technique that lets you display a series of help topics in an Excel workbook.
How it works
The technique described here uses a UserForm, and the UserForm has the following controls (see the Figure below):
- A DropDown control (which displays the current help topic title)
- A Label control that displays the help topic text
- A Frame control. The label is inside of the frame to allow scrolling if all of the text isn't visible.
- Three buttons: Previous, Next, and Exit
The help topics are contained on a worksheet, and the VBA code simply reads the text in the worksheet and transfers it to the UserForm. The user can select a help topic from the DropDown, or view the topics sequentially using the Previous and Next buttons.
You can easily adapt this technique to your own project:
- Export the UserForm and Import it to your project
- Set up the worksheet with the help topics and help text
- Create a simple macro to display the UserForm
Pros and Cons
This technique is very easy to set up, and requires no additional software or files. The VBA code is contained in the UserForm, so no additional programming is necessary. The primary disadvantage is that you cannot format the text that is displayed.
Deleting All Empty Rows
The subroutine below is a fast and efficient way to delete all empty rows in the active worksheet.
Sub DeleteEmptyRows() LastRow = ActiveSheet.UsedRange.Row - 1 + _ ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete Next r End Sub
Notice that loop uses a negative Step value, which causes the rows to be deleted from the bottom to the top.
Working With Names In VBA
Most Excel users realize the value of using names in the workbook. This tip describes some VBA techniques that are useful when working with names.
Listing all names
You probably know that you can get a list of all names by using the Insert Name Paste command, and then clicking the Paste List button. The list that gets generated omits hidden names and sheet level names. The subroutine below creates a list of all names and their references, beginning in cell A1.
Sub ListAllNames() Row = 1 For Each n In ActiveWorkbook.Names Cells(Row, 1) = n.Name Cells(Row, 2) = " " & n.RefersTo Row = Row + 1 Next n End Sub
This subroutine works by looping through the Names collection.
Hidden names are names that are not visibile to the end user. Developers often use hidden names to store various types of information. To demonstrate, just save a range in HTML format using the Internet Assistant Wizard add-in (use the File Save as HTML command). After doing so, execute the ListAllnames subroutine and you'll find that your workbook contains more than a dozen new hidden names that contain the parameters you specified in the Internet Assistant Wizard. The Internet Assistant Wizard uses this information as default values the next time it is run.
A hidden name has its visible property set to False. The only way change this propery is with VBA. For example, the statement below makes the name MyRange a hidden name:
ActiveWorkbook.Names("MyRange").Visible = False
Sheet level names
A sheet level name is a name that is valid for a particular worksheet. To create a sheet level name, precede the name with the worksheet name and an exclamation point.
To find out if a particular name is a sheet level name, simply determine if the name's Name property contains an exclamation point. You can use the Like operator to do this. The expression below is True if MyRange is a sheet level name.
ActiveWorkbook.Names("MyRange").Name Like "*!*"
A name can also refer to a different workbook. I call this type of name a linked name. To find out if a particular name is a linked name, simply determine if the name's RefersTo property contains a right bracket. The expression below is True if MyRange is a linked name.
ActiveWorkbook.Names("MyRange").RefersTo Like "*[*"
NOTE: A bracket is a special character when using the Like command, so it must be enclosed in brackets.
It's not uncommon for a name to refer to a range that no longer exist (deleting a sheet can cause this). These types of names are responsible for the "phantom link" phenomenon in which Excel asks you to update links when you open a workbook -- even if no links really exist.
To find out if a particular name is an erroneous name, simply determine if the name's RefersTo property conains "REF!". The expression below is True if MyRange is an erroneous name.
ActiveWorkbook.Names("MyRange").RefersTo Like "*REF!*"
Determining The Number Of Printed Pages
If you need to determine the number of printed pages for a worksheet printout, you can use Excel's print preview feature, and view the page count displayed at the bottom of the screen.
This tip provides two ways to determine the number of printed pages -- one using the the Excel 4 (XLM) Get.Document macro function, the other using VBA.
Using an XLM macro in VBA
You can execute an XLM macro function from VBA, as follows:
PgCnt = ExecuteExcel4Macro("Get.Document(50)")
In the statement above, the number of printed pages in the active sheet is assigned to the PgCnt variable.
The VBA subroutine below loops through all worksheets in the active workbook and displays the total number of printed pages. Note that this may return incorrect results when using a user-specified print range.
Sub ShowPageCount() PageCount = 0 For Each sht In Worksheets sht.Activate Pages = ExecuteExcel4Macro("Get.Document(50)") PageCount = PageCount + Pages Next sht MsgBox "Total Pages = " & PageCount End Sub
Stew Scott provided the VBA procedure below, which does not use XLM.
Sub NumberOfPrintedPages() Worksheets(1).DisplayAutomaticPageBreaks = True HorizBreaks = Worksheets(1).HPageBreaks.Count HPages = HorizBreaks + 1 VertBreaks = Worksheets(1).VPageBreaks.Count VPages = VertBreaks + 1 NumPages = HPages * VPages Worksheets(1).DisplayAutomaticPageBreaks = False MsgBox NumPages End Sub
Using Auto List Members In The VB Editor
One of the most useful features in VBA is an option called Auto List Members. When this option is in effect, VBA displays a list that contains information that would logically complete the statement at the current insertion point.
For example, when you type Application (followed by a dot), you'll get a drop-down list of all of the properties and methods for the Application object. Simply select from the list and be on your way. This feature is useful because it:
- Reduces typing
- Prompts you with properties and methods you may not even know about
- Eliminates spelling errors
If you're working in a code module for a UserForm, you may need to specify an object located on the UserForm (such as a ListBox) -- but you can't remember the object's name. The solution? Type Me followed by a dot. You'll get a list of all of the properties, methods, and objects for the UserForm. Then you can simply select the object's name from the list.
Similarly, you can type VBA followed by a dot and you'll get a list of VBA statements, functions, and constants. The figure below demonstrates.
NOTE: if Auto List Members isn't working for you, select Tools Options to view the Options dialog box. Click the Edit tab and make sure you have the Auto List Members option checked.
Automatically Resetting The Last Cell
It's commonly known that Excel sometimes has a problem in keeping track of the "last cell" in a worksheet. The last cell is lower right cell that contains data.
Any VBA reference to the UsedRange.Rows.Count property will force Excel to recognize the updated last cell on a worksheet. Therefore, if you're using Excel 97 you can insert the following subroutine into the code module for the ThisWorkbook object:
Private Sub Workbook_SheetSelectionChange _ (ByVal Sh As Object, ByVal Target As Excel.Range) x = Sh.UsedRange.Rows.Count End Sub
This subroutine is executed whenever the selection is changed on a worksheet. It quickly accesses the Count property -- and the last cell is reset. Pressing End-Home will always take you to the real last cell in the worksheet.
CAUTION: This technique has one potentially serious side-effect. Executing this procedure wipes out Excel's undo stack. In other words, if you use this technique you won't be able to use Undo. Therefore, a better technique might be to reset the used range when the workbook is saved (saving a workbook also zaps the undo stack). Here's an example:
Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) For Each Sh In ThisWorkbook.Worksheets x = Sh.UsedRange.Rows.Count Next Sh End Sub
Synchronizing Sheets In A Workbook
If you use multisheet workbook, you probably know that Excel cannot "synchronize" the sheets in a workbook. In other words, there is no automatic way to force all sheets to have the same selected range and upper left cell.
The VBA macro listed below uses the active worksheet as a base, and then performs the following on all other worksheets in the workbook:
- Selects the same range as the active sheet
- Makes the upper left cell the same as the active sheet
Note: Hidden worksheet are ignored.
The SynchSheets Subroutine
Following is the listing for the subroutine:
Sub SynchSheets() ' Duplicates the active sheet's active cell upperleft cell ' Across all worksheets If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub Dim UserSheet As Worksheet, sht As Worksheet Dim TopRow As Long, LeftCol As Integer Dim UserSel As String Application.ScreenUpdating = False ' Remember the current sheet Set UserSheet = ActiveSheet ' Store info from the active sheet TopRow = ActiveWindow.ScrollRow LeftCol = ActiveWindow.ScrollColumn UserSel = ActiveWindow.RangeSelection.Address ' Loop through the worksheets For Each sht In ActiveWorkbook.Worksheets If sht.Visible Then 'skip hidden sheets sht.Activate Range(UserSel).Select ActiveWindow.ScrollRow = TopRow ActiveWindow.ScrollColumn = LeftCol End If Next sht ' Restore the original position UserSheet.Activate Application.ScreenUpdating = True End Sub
Using the Subroutine
To use this subroutine, copy it to a VBA module (your Personal Macro Workbook
is a good choice). Then, activate a worksheet and execute the SynchSheets
subroutine. All of the worksheets will then have the same range selection and
and upper left cell as the active sheet.
Handling The Workbook Beforeclose Event
The BeforeClose event occurs before a workbook is closed. This event is often used in conjunction with a Workbook_Open event handler. For example, you can use the Workbook_Open procedure to initialize things, and use the Workbook_BeforeClose procedure to "clean up" things before the workbook is closed.
If you attempt to close a workbook that hasn't been saved, Excel displays a prompt asking if you want to save the workbook before closing. A potential problem can arise because by the time the users sees this message, the BeforeClose event has already occurred -- which means that your Workbook_BeforeClose subroutine has already executed.
Consider this scenario: You need to display a custom menu when a particular workbook is open. Therefore, your workbook uses a Workbook_Open procedure to create the menu when the workbook is opened, and it uses a Workbook_BeforeClose procedure to remove the menu when the workbook is closed. These two event-handler procedures (which must be in the code module for the ThisWorkbook object) are listed below.Private Sub Workbook_Open() Call CreateMenu End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteMenu End Sub
But, as I noted above, Excel's "save workbook before closing" prompt occurs after the Workbook_BeforeClose event-handler runs. So if the user clicks Cancel, the workbook remains open -- but the custom menu item has already been deleted!
One solution to this problem is to by-pass Excel's prompt and write your own code in the Workbook_BeforeClose procedure to ask the user to save the workbook. The code below demonstrates. This procedure must be in the code module for the ThisWorkbook object.Private Sub Workbook_BeforeClose(Cancel As Boolean) If Not Me.Saved Then Msg = "Do you want to save the changes you made to " Msg = Msg & Me.Name & "?" Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel) Select Case Ans Case vbYes Me.Save Case vbNo Me.Saved = True Case vbCancel Cancel = True Exit Sub End Select End If Call DeleteMenu 'Change this to your own subroutine End Sub
This procedure first determines if the workbook has been saved. If so, no problem -- the DeleteMenu subroutine is executed and the workbook is closed. But if the workbook has not been saved, the procedure displays a message box that duplicates the one Excel would normally show.
If the user clicks Yes the workbook is saved, the menu is deleted, and the workbook is closed. If the user click No, the code sets the Saved property of the Workbook object to True (but doesn't actually save the file), and deletes the menu. If the user clicks Cancel, the BeforeClose event is canceled and the subroutine ends without deleting the menu.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks