Erlandsen Data ConsultingThis example macro shows how to print a selected document to another printer then the default printer. This is done by changing the property Application.ActivePrinter: Sub PrintToAnotherPrinter() Dim strCurrentPrinter As String strCurrentPrinter = Application.ActivePrinter ‘ store the current active printer On Error Resume Next ‘ ignore printing errors Application.ActivePrinter = “microsoft fax on fax:” ‘ [...]
If you turn off the screen updating, and your macros takes some time to finish, the user may think that the computer has stopped to respond. Because of this it’s a good programming rule to inform the user of the macro progress by displaying a message on the statusbar at the bottom of the screen. [...]
This workbook contains a class module “template” for an event-enabled application class. Useful if you want to attach macros to an Application-object event. Click here to download this file. Updated: 2000-11-02 Requires: XL97 File size: 11 kB
The macros below will display a list of all installed fonts. Note! If you have many fonts installed, the macro may stop responding because of lack of available memory. Sub ShowInstalledFonts() Dim FontNamesCtrl As CommandBarControl, FontCmdBar As CommandBar, tFormula As String Dim fontName As String, i As Long, fontCount As Long, fontSize As Integer Dim [...]
The macro below will display a list of all installed fonts. Note! If you have many fonts installed, the macro may stop responding because of lack of available memory. Sub ShowInstalledFonts() Const StartRow As Integer = 4 Dim FontNamesCtrl As CommandBarControl, FontCmdBar As CommandBar, tFormula As String Dim fontName As String, i As Long, fontCount [...]
Sometimes the application displays a messagebox asking if the user wants to continue, if he/she wants to save a file before closing, or want some other verification from the user. These messages can be turned off by using this command in a macro: Application.DisplayAlerts = False The warningmessages will remain turned off until you enable [...]
The function below can be used to determine if a workbook or another file exists: Function FileExists(FullFileName As String) As Boolean ‘ returns TRUE if the file exists FileExists = Len(Dir(FullFileName)) > 0 End Function Example: If Not FileExists("C:\FolderName\SubFolder\FileName.xls") Then MsgBox "The file doesn’t exist!" Else Workbooks.Open "C:\FolderName\SubFolder\FileName.xls" End If
The function below can be used to determine if a workbook is open or not: Function WorkbookOpen(WorkBookName As String) As Boolean ‘ returns TRUE if the workbook is open WorkbookOpen = False On Error GoTo WorkBookNotOpen If Len(Application.WorkBooks(WorkBookName).Name) > 0 Then WorkbookOpen = True Exit Function End If WorkBookNotOpen: End Function Example: If Not WorkbookOpen("MyWorkbookName.xls") [...]
Events for the Application object occurs when a workbook is created, opened or when a sheet in any workbook is changed. If you want to write event procedures for the Application-object, you will have to create a new object with the keyword WithEvents in a class module. After the new class module is created you [...]
The function below will return the computer name for the machine running the code, works in both Windows NT and Windows95/98. Private Declare Function GetComputerName Lib "kernel32" _ Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long Function ReturnComputerName() As String Dim rString As String * 255, sLen As Long, tString As String [...]