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.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.ReplaceFormat ??
Application.CutCopyMode = False
Application.OnTime TimeValue("2:30 PM"), "C:\temp\Temp.xls!WkbName.macro_name"
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.




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.



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.


Records code if the macro recorder is on.

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.

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