How to use the At and Ln indicators in Word's status bar
Thursday, August 13, 2009 12:07 PM
At and Ln are used in automating special format requirements. Here's what you need to know.
Microsoft Word
How to use the At and Ln indicators in Word's status bar
You might have noticed a couple of indicators on Word's status bar that you don't understand: At and Ln.
Ln is easy enough to guess: It displays the current line number, including blank lines. The At indicator is a bit more mysterious. It displays the distance between the top of the page to the current line.
The truth is, you probably don't use them much, but if you do, there's a chance you're automating special format requirements. In this case, you might need VBA.
It's almost as easy to get VBA to return these values, if you know the right properties. To return the Ln and At values, use the following statements, respectively:
Debug.Print Selection.Information(wdFirstCharacterLineNumber)
Debug.Print Selection.Information(wdVerticalPositionRelativeToPage) / 72
These two statements work as long as the document's in Print or Normal view, so take that into account when using VBA.
Microsoft Access
Automatically close all the open forms and reports in an Access database
You might expect your users to clean up after themselves by closing all the open forms and reports before closing the database. The truth is, they probably won't bother--and frankly, it really isn't their job.
Instead of relying on users, add the appropriate code to the database's exit routine. Your users won't know the difference.
The For...Each statement doesn't work
At first, you might think that VBA's For...Each statement is the most efficient way to close all open forms or reports. For instance, the following code should loop through the collection of open forms and close each until all the forms are closed--or so you might think:
Function CloseForms()
'Close all open forms
Dim frm As Form
For Each frm In Forms
DoCmd.Close acForm, frm.Name
Next
End Function
However, this function always leaves one form open. (The same is true if you loop through the Reports collection.) That's because after closing a form, the remaining forms slip down a notch in the collection.
It's easy to see with a simple illustration. Suppose you open the following forms in order: Employees, Products, and Orders. Furthermore, the collection's index values for these three forms are as follows:
Employees 0
Products 1
Orders 2
The For loop goes through the collection in the same order the forms were open. During the first loop, the code deletes the form at the 0 index position, Employees. Consequently, Products and Orders both move down a notch: Products is now 0 and Orders is 1. The next time through, the loop is looking for 1, so it deletes Products. Now, Orders slips down to 0, but the loop is looking for the index value 2, which it doesn't find. The loop finishes without closing the Orders form.
A simple loop will do it
After eliminating For...Each, you might consider a For loop based on the number of forms, but there's a simpler way. The Do loop in Listing A keeps running until there are no forms left in the collection. It's simple and efficient. It still relies on the index value, but that value is always 0, and there will always be a form or report in that position until all the forms or reports are deleted and the respective collection is empty.
Listing A
Function CloseFormsReports()
'Close all open forms
On Error GoTo errHandler
Do While Forms.Count > 0
DoCmd.Close acForm, Forms(0).Name
Loop
Do While Reports.Count > 0
DoCmd.Close acReport, Reports(0).Name
Loop
Exit Function
errHandler:
MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
End Function
How to execute it
The form and report closing code is simple; deciding how to execute it might prove more difficult. You could include a button or menu and let the users decide, but that's not a great idea. If you want to clean up before closing, let the user interface execute it.
For instance, the Main Switchboard form in Northwind (the sample database that comes with Access) has an exit button. You could easily call the above function from that button's Click event. Or you could simply add the code to the event as follows:
- Open the Main Switchboard form in Design view and then click the Code button to launch the form's module.
- From the Object drop-down list (in the Module window), choose ExitMicrosoftAccess (that's the name of the form's exit button).
- From the Procedure drop-down list, choose Close. The Visual Basic Editor (VBE) will insert a stub for the form's Close event.
- Insert the two Do loops just above the DoCmd.Quit statement, as shown in Figure A.
- Click the Save button on the VBE's Standard menu.
Figure A
You can add the form and report closing code to the existing user interface routine that closes the database.
To try it out, return to Access and open a few forms and reports. Now, click the Exit Microsoft Access button on the Main Switchboard form. It'll happen quickly and you won't actually see Access close all the open forms and reports, but it will.
This is just one simple example of how to execute the function. Keep in mind that closing the form or report will execute that object's Close event, if any. Be sure to test the code thoroughly and add the appropriate error-handling code. While a bit of code in a form's or report's Close event shouldn't be troublesome, it's something to consider during the development stage. If the Close event performs some complex tasks, you might want to move that functionality to another area.
Worth noting
The Northwind database doesn't force users to use the Main Switchboard form to close the database. That means they could bypass any cleanup routine executed by the Exit button. If you rely on the user interface to execute cleanup or maintenance code, you must make sure users actually use that routine to exit the database.
So you must inhibit the Close button on the title bar and the Close and Exit commands on the File menu. Don't forget about the Close button on the Database window's title bar. Clicking it will close the current database if users have access to the Database window.
Quitting time!
Before closing a database, it's a good idea to close all the open objects. To close all open forms and reports, you can execute a simple Do loop from the user interface before closing the database.
Microsoft Excel
Avoid this referencing gotcha when using Excel's range names
Assigning a name to a range of cells is a great way to work more efficiently because you can refer to the range name instead of literal cell references in functions and formulas. However, there's an interesting behavior connected to range names that can be a bit limiting: Range names use absolute references.
The spreadsheet below has four named ranges: North, South, East, and West. To sum the regional totals, you might enter the formula =SUM(North) in cell F5 and then copy that function to cells F6:F8, but it won't work.
Because range names are absolute references, Excel will not use a relative referencing scheme and enter the formulas, =SUM(South), =SUM(East), and =SUM(West), as you might expect.
When this happens, you might try dragging across the appropriate cells, but Excel will automatically usurp the cell reference and enter the range name.
There's a way around this, according to Excel guru Bob Umlas. When you want to include the cell reference of a named range in a function or formula, you must enter it via the keyboard. Then, when you copy it, Excel's relative cell referencing behavior will kick right in.
In a simple spreadsheet, range names are easy enough to avoid. In this particular example, the range names add nothing--it's just as easy to enter the cell references as it is to enter the range name. In other words, often, this limitation is symptomatic of poor design rather than Excel's seemingly limiting behavior.
However, if you run into it, you'll still want to know how to get around it if deleting the range name isn't practical (and it seldom is in a working spreadsheet). Keep this limitation in mind when designing future spreadsheets.
There are currently no comments for this post.