Microsoft Office Tutorials and References
In Depth Information
VBA Techniques
I used a Select Case construct to base the greeting displayed in the message box’s title bar on
the time of day. VBA time values work just as they do in Excel. If the time is less than .5 (noon), it’s
morning. If it’s greater than .7083 (5 p.m.), it’s evening. Otherwise, it’s afternoon. I took the easy
way out and used VBA’s TimeValue function, which returns a time value from a string.
The next series of statements determines the user’s first name, as recorded in the General tab in
Excel’s Options dialog box. I used VBA’s InStr function to locate the first space in the user’s
name. When I first wrote this procedure, I didn’t consider a username that has no space. So when
I ran this procedure on a machine with a username of Nobody, the code failed — which goes to
show you that I can’t think of everything, and even the simplest procedures can run aground. (By
the way, if the user’s name is left blank, Excel always substitutes the name User. ) The solution to
this problem was to use the length of the full name for the SpaceInName variable so that the
Left function extracts the full name.
The MsgBox function concatenates the date and time but uses the built-in vbCrLf constant to
insert a line break between them. vbOKOnly is a predefined constant that returns 0 , causing the
message box to appear with only an OK button. The final argument is the Greeting ,
constructed earlier in the procedure.
The DateAndTime procedure is available on the companion CD-ROM in a file named
date and time.x l sm .
Getting a list of fonts
If you need to get a list of all installed fonts, you’ll find that Excel doesn’t provide a direct way to
retrieve that information. The technique described here takes advantage of the fact that (for
compatibility purposes) Excel 2010 still supports the old CommandBar properties and methods. These
properties and methods were used in pre-Excel 2007 versions to work with toolbars and menus.
The ShowInstalledFonts macro displays a list of the installed fonts in column A of the active
worksheet. It creates a temporary toolbar (a CommandBar object), adds the Font control, and
reads the font names from that control. The temporary toolbar is then deleted.
Sub ShowInstalledFonts()
Dim FontList As CommandBarControl
Dim TempBar As CommandBar
Dim i As Long
‘ Create temporary CommandBar
Set TempBar = Application.CommandBars.Add
Set FontList = TempBar.Controls.Add(ID:=1728)
‘ Put the fonts into column A
Range(“A:A”).ClearContents
For i = 0 To FontList.ListCount - 1
Cells(i + 1, 1) = FontList.List(i + 1)
 
Search JabSto ::




Custom Search