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.



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext