Advanced Techniques
Saving
ActiveWorkbook.SaveAs FileName:="C:\Temp\Wbk1.xls"
ActiveWorkbook.SaveCopyAs "C:\temp\Temp.xls"
Workbooks("Book2.xls").SaveCopyAs ("C:\temp\Backup.xls")
Application Caption
Application.Caption = "message"
Status Bar
It is possible to add your own messages to the status bar.
This can be used as a way to keep the user informed of the current action when a macro takes quite a long time to run.
Application.StatusBar = "Processing .."
Application.StatusBar = False
It is important to reset the Statusbar property to False when your macro has finished.
Otherwise your last message will remain in the statusbar until Excel is closed.
Whenever referencing workbooks or worksheets always enclose them in single speech marks "WshName" as they could contain spaces and/or unusual characters.
When you open a workbook using VBA even if you have the Application.DisplayAlerts = False you will be prompted whether to update links. To prevent being prompted change the "updatelinks" property in the Open method.
You can write an event procedure that will work for any workbook that is open but you need to use a class module
If you want something to happen to all the workbooks in a workbook use the "SheetActivate" event for "ThisWorkbook".
In previous versions of Excel the Auto_Open event was used to execute when a workbook is opened. It has since been supplemented / replaced with the Workbook-Open event which is stored in the ThisWorkbook module. Workbook_Open is executed before the Auto_Open event.
Event sequences are not always in an obvious order. The SheetActiveWorkbook event actually occurs before the WorkbookNewSheet. Application event when a new worksheet is added to a workbook.
Workbook event procedures must be in the Code Module for the "ThisWorkbook" Object. If they are anywhere else they will be ignored.
If you want to obtain the number of workbooks that are open you must remember if you use workbooks.count this will include any hidden workbooks (including your Personal.xls) You must check for visibility as well.
If you are referencing a value in another workbook use Workbook(---.Sheets(---).Range().value instead of "Worksheets".
Application.WindowState = xlMinimised
ActiveWorkbook.Windows.Arrange ArrangeStyle := xlArrangeStyleTiled, SyncHorizontal := True
ActiveWorkbook.Visible = False
ThisWorkbook.CustomViews.Add "my view name"
This returns the file name
ThisWorkbook.Name
This returns the folder path
ThisWorkbook.Path
This returns the folderpath and filename
ThisWorkbook.FullName
To make sure that code works on both Windows and Macintosh use: Since the windows uses "\" and macs use ":".
Application.PathSeparator
Application.Workbooks.Count
sFolderPathAndFileName = ThisWorkbook.FullName
Workbooks(Range("A2").Worksheet.Parent).Name
?? = Workbooks.Count
?? = Workbooks.Item(3)
ThisWorkbook.ChangeFileAccess xlReadOnly
Workbooks("Book2.xls").RefreshAll
Set wbktemp = Workbooks.Filename :="temp.xls"
Speech (new 2002)
Excel 2002 offers a new Speech object that allows the control of automatic speech output and the output of text via the sound card'
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrev