VBA Code

Event handler procedures must always be located in the correct module, otherwise they will not run. Never put any event-handlers in a standard code module.
When using event codes it is essential to include Application.EnableEvents = False. This will prevent an endless loop


Replace the main Excel caption

Application.Caption = "Your text" 

Replacing the File Name with the File Name and Path

Windows(1).Caption = ActiveWorkbook.FullName 


Performing an action when a particular cell is active

Private Sub Workbook_SheetChange(ByVal As Object, ByVal Target As Range) 
   If Target.Address = "$A$3" Then
   End If
End Sub

Inserting the date the file was last saved.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
   Range("A3").Value = Now()
End Sub

Finding the version of Excel you are using

If Val(Application.Version) = 10 Then Call MsgBox("You are using Excel 2002") 

Application

Application.Quit 
Application.Run "Personal.xls!Macro2"
Application.AskToUpdateLinks = False 'Remove the prompt to update the links
Application.ScreenUpdating = False
Application.Caption = ""
Application.StatusBar = "please wait .."
Application.RecentFiles = 9
Application.StatusBar = False
Application.Cursor = xlWait | xlDefault
Application.ReferenceStyle = "A1"
Application.FindFormat ??
Application.CellFormat
Application.ReplaceFormat ??
Application.InchesToPoints(1.5)
Application.CutCopyMode = False
Application.OnTime TimeValue("2:30 PM"), "C:\temp\Temp.xls!WkbName.macro_name"
Application.SheetsInNewWorkbook
Application.Visible = False
Application.Volatile ??

This does not appear in the object browser but can be used
This is only available in Excel, not in Word or PowerPoint.

Application.Pi() 


ActiveWorkbook

ActiveWorkbook.FullName 
ActiveWorkbook.Name
ActiveWorkbook.Path

ActiveWorkbook.VBProject.VBComponents.Remove _ 
   ActiveWorkbook.VBProject.VBComponents("Module2")

Runs a Microsoft Excel 4.0 macro function and then returns the result of the function. The return type depends on the function.

Application.ExecuteExcel4Macro 


Application.RecordRelative 

Allows modification of the macro attributes such as the name, description, shortcut key, category and associated help file
Equivalent to the macro options dialog box.

Application.MacroOptions 


Records code if the macro recorder is on.

Application.RecordMacro 
Application.RecordMacro BasicCode:="Application.Run ""MySub"" "

BasicCode Optional Variant. A string that specifies the Visual Basic code that will be recorded if the macro recorder is recording into a Visual Basic module. The string will be recorded on one line. If the string contains a carriage return (ASCII character 10, or Chr$(10) in code), it will be recorded on more than one line.
XlmCode Optional Variant. This argument is ignored.
The RecordMacro method cannot record into the active module (the module in which the RecordMacro method exists).
If BasicCode is omitted and the application is recording into Visual Basic, Microsoft Excel will record a suitable Application.Run statement.
To prevent recording (for example, if the user cancels your dialog box), call this function with two empty strings.




This hierarchy is known as the Object model. Each Microsoft Office application (Excel, Word, Access, PowerPoint etc) has a different object model. These object models can be viewed using the Object Browser.
When you are looking at code it is often easier to think in terms of objects. An object being something that you can manipulate. There are hundreds if not thousands of objects in Excel. The most obvious ones are a Workbook and a Worksheet. There is a clearly defined hierarchy within the objects. Some objects are contained within other objects. There is an object that can represent a selection of cells. This object is contained within the corresponding worksheet object which is consequently contained within the corresponding workbook object.



Do not use the Range objects as arguments to procedures or function. Use the address instead.
Try to use Range("") with column letter references instead of Cells() with numerical references. If you have to use Cells() the always use a column enumeration so it is very quick to identify individual columns.
Use worksheet code names, rather than referring to the Sheets("---") descriptive names that can be easily changed by the user.
Never use ActiveWorkbook as you can never guarantee what workbook will be the active workbook.
Always use .Cells() instead of .Range(col_letter) when using enumerations
Conditional Formatting - no option on paste special but selecting "formats" will paste conditional formats
User defined functions - you can't use the same name for a module and a user defined function - otherwise the UDF is not recognised.
How to definitely update workbook/just one worksheet (do not use Application.Calculate) - do you have to go through each worksheet ?
Undo functionality is session specific, not workbook specific.



© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext