Excel VBA Application Object, Properties & Methods
Related Links:
Working with Objects in Excel VBA
Excel VBA Workbook Object, working with Workbooks in Excel
Microsoft Excel VBA – Worksheets
Excel VBA Range Object, Referencing Cells and Ranges
Excel VBA Custom Classes and Objects
———————————————————————————————————–
Contents:
Application.GetOpenFilename Method
———————————————————————————————————–
The Application Object refers to the host application of Excel, and the entire Excel application is represented by it. If the host application is the Microsoft Word application, the Application Object refers to and represents the Word application.
All applications like Excel, Access, Word or PowerPoint, which use VBA, have their own object model. While writing vba code in Microsoft Office Excel, you will be using the objects provided by the Excel object model. The object model is a large hierarchy of all the objects used in VBA. The Object Model of the Application (Excel) refers to and contains its programming objects which are related to each other in a hierarchy. The entire Excel application is represented by the Application Object which is at the top of the Excel object hierarchy and moving down you can access the objects from Application to Workbook to Worksheet to Range (Cells) and further on, by connecting the objects with a period (dot). Excel objects are accessed through ‘parent’ objects – Worksheet is the parent of the Range Object, and the Workbook is the parent of the Worksheet object, and the Application object is the parent of the Workbook object.
Example – the following code line inserts the text “Hello” in the cell A1 of the worksheet named Sheet1 of the workbook named ExcelVBA.xlsm:
Application.Workbooks(“ExcelVBA.xlsm”).
Worksheets(“Sheet1”).Range(“A1”) = “Hello”
The Application object is the Default Object, Excel assumes it even when it is not specified. The Application qualifier is mostly not required to be used in vba code, because the default application is Excel itself, unless you want to refer to other outside applications (like Microsoft Word or Access) in your code or you want to refer to Excel from another application like Miscrosoft Word. In your VBA code, both the expressions Application.ActiveWorkbook.Name and ActiveWorkbook.Name will have the same effect of returning the Active Workbook’s name. However, there are some instances when the Application qualifier is required to be used, viz. generally when using properties & methods which relate to the Excel window’s appearance, or which relate to how the excel application behaves as a whole.
We elaborate on some often used properties & methods below.
Instances where using the Application qualifier is required to be used for Properties or Methods of the Application object:
Height, Width and WindowState Properties. Refer below Example of using these properties:
Sub ApplicationObject_WinSt_Ht_Wd()
‘The WindowState property sets the state of the application window. Setting options are xlNormal, xlMaximized (sets the active window to the maximum available size provided it is not already maximized) & xlMinimized:
Application.WindowState = xlNormal
‘Height & Width Properties, set the height and width of the application window, in points.
‘Note that these properties cannot be set if the window is maximized, and the properties are read-only if the window is minimized.
Application.Height = 350
Application.Width = 450
End Sub
Application.DisplayFullScreen Property. Boolean Value – with the DisplayFullScreen Property set to True, the application window is maximized filling the entire screen and the application title bar gets hid:
Application.DisplayFullScreen = True
Application.DisplayFormulaBar Property. Boolean Value – displays or hides the Formula Bar when set to True or False respectively:
Application.DisplayFormulaBar = False
Application.Calculation Property returns or sets the calculation mode. There are 3 settings: xlCalculationAutomatic – (Default) Automatic recalculation by Excel as data is entered in cells; xlCalculationSemiautomatic – Automatic recalculation by Excel except for Data Tables; xlCalculationManual – Calculation is done only when requested by user on clicking “Calculate Now” or pressing F9.
Application.Calculation = xlCalculationManual
Application.EditDirectlyInCell Property. Boolean Value – allows or disallows editing directly in cells when set to True or False respectively:
Application.EditDirectlyInCell = False
Application.ScreenUpdating Property. Boolean Value. When your vba code (macros) executes, your screen view gets refreshed or updated with its actions. If the ScreenUpdating Property is set to False, screen updating will be turned off, and you will not be able to view what your code does but it executes faster. It is common to turn off screen updating in vba procedures to make codes run faster (and set the property back to True after the code execution ends).
Application.ScreenUpdating = False
Application.DisplayAlerts Property. Boolean Value. While executing a macro code, certain alerts are displayed by Excel to confirm an action viz. excel asks for a confirmation while deleting a worksheet. Setting this property to False will not display any prompts or alerts and in this case a default response will be chosen by Excel. This property is restored to its default value of True after the procedure ends.
Application.DisplayAlerts = False
Application.DefaultFilePath Property. This property sets (or returns) the default folder or path used by Excel while opening files.
Application.DefaultFilePath = “C:\My Documents\Excel”
Application.Quit Method. Use this method to quit the Excel Application. Note that after closing the workbook, the excel window remains open. To exit Excel, use the Quit method as shown below.
‘closes ThisWorkbook after saving it, but does not quit Excel:
ThisWorkbook.Close SaveChanges:=True
‘quits the Excel Application and then closes ThisWorkbook after saving it (the Quit method does not terminate Excel):
Application.Quit
ThisWorkbook.Close SaveChanges:=True
‘using the following closes ThisWorkbook, but does not quit the Excel Application because the macro also gets closed with ThisWorkbook without reading the Application.Quit line:
ThisWorkbook.Close SaveChanges:=True
Application.Quit
Application.OnTime Method. This method is used in VBA to automatically run a procedure at periodic intervals or at a specific time of day. In the following example, RunTime is a Public variable of type Date, which sets the time interval [RunTime = Now + TimeValue(“00:00:03”)] and the macro named MacroAutoRun will run automatically, at the scheduled time interval of 3 seconds, with the OnTime Method.
Application.OnTime RunTime, “MacroAutoRun”
The Application.OnTime Method has been illustrated in detail in the section “Application.OnTime VBA, Schedule Excel to Run Macros at Periodic Intervals or a Specified Time.“.
Example of using the Application.OnTime Method:
Public RunTime As Date
Dim count As Integer
_______________________
Sub MacroAutoRun()
‘This procedure uses the OnTime Method to auto increment cell value at specific time intervals, and Stops the procedure after it runs for a specific number of times.
‘set the time interval at 3 seconds, at which interval the procedure will run:
RunTime = Now + TimeValue(“00:00:03”)
‘procedure named MacroAutoRun will autmatically run, at the sheduled time interval, with the OnTime Method.
Application.OnTime RunTime, “MacroAutoRun”, , True
‘increment the value in cell A1 (in Active Worksheet) by 5, for each time the Macro is repeated:
Cells(1, 1).Value = Cells(1, 1).Value + 5
count = count + 1
‘stop the procedure after it runs for 5 times:
If count = 5 Then
Application.OnTime RunTime, “MacroAutoRun”, , False
count = 0
End If
End Sub
Application.ActivateMicrosoftApp Method. This method activates an already running Microsoft application or else it creates a new instance of the application in case the application is not already running. Below codes start and activate Word, Access & Power Point respectively:
Application.ActivateMicrosoftApp xlMicrosoftWord
Application.ActivateMicrosoftApp xlMicrosoftAccess
Application.ActivateMicrosoftApp xlMicrosoftPowerPoint
Application.GetOpenFilename Method
The GetOpenFilename method gets the file name (to open) from the user by displaying the standard Open dialog box, but the file is not actually opened by the method. The method returns the full path and name of the selected file(s). Syntax: ApplicationObject.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect). All arguments are optional to specify, but it is necessary to specify the ApplicationObject. The FileFilter argument is a string value which specifies the filter criteria for the type of files which will be displayed in the directory from which the user gets the file name. See below how to specifies the filter criteria in this argument. If there are 4 filters specified in FileFilter, you can specify any one of them as the default criteria by using the index numbers of 1 to 4 (for 3 filters, use index numbers 1 to 3) in the FilterIndex argument. Use the Title argument to specify the dialog box’s title – default title is “Open” if not specified. ButtonText argument is applicable only for computers running Excel for the Macintosh (Mac). Set the MultiSelect argument to True to allow selecting multiple file names, its default setting being False.
Using FileFilter for .xlsm Excel files (Note how the string specifies in pairs and uses the wildcard character):
“Excel Files (*.xlsm), *.xlsm”
Using FileFilter for .xls, .xlsx & .xlsm Excel files (Note how the string specifies in pairs, uses the semi-colon and the wildcard characters):
“Excel Files(*.xls;*.xlsx;*.xlsm), *.xls;*.xlsx;*.xlsm”
Using FileFilter for .xlsm Excel files & .txt Text files (Excel files & Text files will each be listed separately in the drop-down list of File types):
“Excel Files (*.xlsm), *.xlsm,Text Files (*.txt),*.txt”
If the FileFilter argument is omitted, it will default to All Files:
“All Files (*.*),*.*”
Example 1: Select and Open a single file (and enter text, save and close the workbook), using Application.GetOpenFilename & Workbooks.Open methods.
Sub GetOpenFilename1()
‘select and open a single file, using Application.GetOpenFilename & Workbooks.Open methods.
‘in this example we open an excel .xlsm workbook from a chosen location/directory, select a worksheet and enter text, save and close the workbook.
Dim fileName As Variant
Dim wkbk As Workbook
Dim strSetFolder As String
Dim strCurFolder As String
‘CurDir returns the current directory or path. This is being stored to revert to this initial directory after running the procedure.
strCurFolder = CurDir
‘set a folder or location from where to select files in the Open dialog box:
strSetFolder = “C:\My Documents\Excel”
‘set current drive:
ChDrive strSetFolder
‘set current directory or folder:
ChDir strSetFolder
‘The GetOpenFilename method gets a file name from the user by displaying the standard Open dialog box, but the method does not actually open a file.
‘the GetOpenFilename method returns the full path and name of the selected file(s).
fileName = Application.GetOpenFilename(filefilter:=“Excel Files (*.xlsm), *.xlsm”, title:=“Select a file”)
‘if the user clicks the Cancel button, fileName returns False (ie. file name is returned as False). Note that an alternate first code line could be – If fileName = “False” Then – in that case you can also Dim fileName as String.
If fileName = False Then
MsgBox “Please select a file to continue”
Exit Sub
Else
‘Workbooks.Open method opens a workbook:
Workbooks.Open (fileName)
‘set the workbook variable to the opened workbook:
Set wkbk = ActiveWorkbook
‘select worksheet named Sheet1 in the opened workbook:
Sheets(“Sheet1”).Select
‘enter text in Range A1 in Sheet1 of the workbook:
wkbk.ActiveSheet.Range(“A1”) = “Hello”
‘save workbook:
wkbk.Save
‘close workbook:
wkbk.Close
End If
‘change and revert to initial drive:
ChDrive strCurFolder
‘change and revert to initial directory or folder:
ChDir strCurFolder
End Sub
Example 2: Select and Open Multiple files, using Application.GetOpenFilename & Workbooks.Open methods.
Sub GetOpenFilename2()
‘select and open multiple files, using Application.GetOpenFilename & Workbooks.Open methods
Dim FileNames As Variant
Dim i As Integer
Dim iFiles As Integer
‘The GetOpenFilename method gets a file name from the user by displaying the standard Open dialog box, but the method does not actually open a file.
‘the GetOpenFilename method returns the full path and name of the selected file(s).
‘set MultiSelect to True to allow selecting multiple file names, its default setting being False.
FileNames = Application.GetOpenFilename(filefilter:=“Excel Files(*.xls;*.xlsx;*.xlsm), *.xls;*.xlsx;*.xlsm”, title:=“Select file(s)”, MultiSelect:=True)
‘check if not an array ie. if the user clicks the Cancel button:
If IsArray(FileNames) = False Then
MsgBox “Please select file(s) to continue”
Exit Sub
Else
‘determine number of files to open:
iFiles = UBound(FileNames) – LBound(FileNames) + 1
For i = 1 To iFiles
‘Workbooks.Open method opens a workbook:
Workbooks.Open FileNames(i)
Next i
End If
End Sub
Example 3: Open an existing excel .xlsm workbook from a chosen directory, add a new workbook in the same location and save it as an .xlsm file, then copy a worksheet from the existing workbook to the newly added workbook, and then close both the workbooks.
Sub GetOpenFilename3()
‘using Application.GetOpenFilename, Workbooks.Open, Workbooks.Add, Workbook.SaveAs, Workbook.Save & Workbook.Close methods
‘in this example we open an existing excel .xlsm workbook from a chosen directory, then add a new workbook in the same location and save it as an .xlsm file, and copy a worksheet from the existing workbook to the newly added workbook, and then close both the workbooks.
Dim fileName As Variant
Dim strSetFolder As String
Dim wbTarget As Workbook
Dim wbSource As Workbook
‘set a folder or location from where to select files in the Open dialog box:
strSetFolder = “C:\My Documents\Excel”
‘set current drive:
ChDrive strSetFolder
‘set current directory or folder:
ChDir strSetFolder
‘The GetOpenFilename method gets a file name from the user by displaying the standard Open dialog box, but the method does not actually open a file.
‘the GetOpenFilename method returns the full path and name of the selected file(s).
fileName = Application.GetOpenFilename(filefilter:=“Excel Files (*.xlsm), *.xlsm”, title:=“Select a file”)
‘if the user clicks the Cancel button, fileName returns False (ie. file name is returned as False).
If fileName = False Then
MsgBox “Please select a file to continue”
Exit Sub
Else
‘Workbooks.Open method opens a workbook:
Set wbSource = Workbooks.Open(fileName)
End If
‘add a new workbook, which is the target workbook to which a worksheet will be copied:
Workbooks.Add
‘save the target workbook with a new name and as .xlsm file type.
‘Remember that in Excel 2007-2010 while using SaveAs, it is necessary to specify the FileFormat parameter to save a FileName with a .xlsm extension if the ActiveWorkbook is not a .xlsm file.
‘.xlsm FileFormat representing a macros enabled file: xlOpenXMLWorkbookMacroEnabled = 52. You can use either FileFormat:=xlOpenXMLWorkbookMacroEnabled or FileFormat:=52. For a non .xlsm file, you can use SaveAs without specifying the FileFormat viz. ActiveWorkbook.SaveAs FileName:=“newWorkbook.xlsx”.
ActiveWorkbook.SaveAs fileName:=“newWorkbook.xlsm”, FileFormat:=52
Set wbTarget = ActiveWorkbook
‘copy worksheet named Sheet2 from source workbook to the new target workbook as the last sheet:
wbSource.Worksheets(“Sheet2”).Copy After:=wbTarget.Sheets(Sheets.Count)
‘close both the workbooks, after saving the newly created target workbook:
wbSource.Close
wbTarget.Close SaveChanges:=True
End Sub
Properties & Methods of the Application Object – when it is not required to specify the Application qualifier.
Those properties and methods whose use does not require specifying the Application object qualifier are considered “global”. You can view these global properties & methods in the Object Browser in VBE (View -> Object Browser; or press F2), by choosing Excel from the Project/Libraries drop down list in the top pane, and then clicking <globals> which appears at the top in the Classes box. Instances where using the Application qualifier is not required to be used include:
ActiveCell Property. The ActiveCell Property, applied to an Application object, returns the active cell (Range object) in the worksheet displayed in the active window. You can also apply this property to a Window object by specifying the window to look for the active cell. Note that in the absence of a worksheet being displayed in the window, the property will fail.
Any of the following codes can be used alternatively (note that Value is the default property of a Range object):
MsgBox “Active Cell’s value is: ” & Application.ActiveCell
MsgBox “Active Cell’s value is: ” & ActiveCell
MsgBox “Active Cell’s value is: ” & ActiveWindow.ActiveCell
MsgBox “Active Cell’s value is: ” & ActiveCell.Value
ActiveWindow Property. This property returns the active window. The active window is the currently selected window / the window on top / the window with the current focus.
Code to display ActiveWindow’s name which appears in the title bar:
MsgBox “Active Window is: ” & Application.ActiveWindow.Caption
MsgBox “Active Window is: ” & ActiveWindow.Caption
ActiveWorkbook Property. This property returns the active workbook (ie. the workbook in the active window).
MsgBox “Active Workbook’s name is ” & Application.ActiveWorkbook.Name
MsgBox “Active Workbook’s name is ” & ActiveWorkbook.Name
ThisWorkbook Property. This property is used only from within the Excel Application and returns the workbook in which the code is running currently.
MsgBox “This Workbook’s name is ” & Application.ThisWorkbook.Name
MsgBox “This Workbook’s name is ” & ThisWorkbook.Name
Note that though most times the ActiveWorkbook is the same as ThisWorkbook, but it might not always be so. The active workbook can be different than the workbook in which the code is running, as shown by the following code example. The Active Object has been illustrated in detail in the section “Working with Objects in Excel VBA“.
Sub ActiveWorkbook_ThisWorkbook()
‘Open two Excel workbook files (“Book1.xlsm” and “Book2.xlsm”) in a single instance (this will enable all workbooks to access macros).
‘Enter this code in the workbook “Book1.xlsm”, which is also the active/selected workbook.
‘returns “Book1.xlsm”:
MsgBox “Active Workbook’s name is ” & ActiveWorkbook.Name
‘returns “Book1.xlsm”:
MsgBox “This Workbook’s name is ” & ThisWorkbook.Name
‘activate “Book2.xlsm”:
Workbooks(“Book2.xlsm”).Activate
‘returns “Book2.xlsm”, while ThisWorkbook remains “Book1.xlsm”:
MsgBox “Active Workbook’s name is ” & ActiveWorkbook.Name
‘returns “Book1.xlsm”:
MsgBox “This Workbook’s name is ” & ThisWorkbook.Name
‘activate “Book1.xlsm”:
Workbooks(“Book1.xlsm”).Activate
‘returns “Book1.xlsm”:
MsgBox “Active Workbook’s name is ” & ActiveWorkbook.Name
‘returns “Book1.xlsm”:
MsgBox “This Workbook’s name is ” & ThisWorkbook.Name
End Sub
ActiveSheet Property. The ActiveSheet Property, applied to an Application object, returns the active sheet in the active workbook. You can also apply this property to a Workbook or Window object by specifying the workbook or window to look for the active sheet.
Following codes return the active sheet in the active workbook:
Msgbox “Active Sheet’s name is ” & Application.ActiveSheet.Name
MsgBox “Active Sheet’s name is ” & Application.ActiveWorkbook.ActiveSheet.Name
Msgbox “Active Sheet’s name is ” & ActiveSheet.Name
MsgBox “Active Sheet’s name is ” & ActiveWorkbook.ActiveSheet.Name
Following code returns the active sheet in the specified workbook (named “ExcelVBA.xlsm”):
MsgBox “Active Sheet’s name is ” & Application.Workbooks(“ExcelVBA.xlsm”).
ActiveSheet.Name
ActiveChart Property. The ActiveChart Property, applied to an Application object, returns the active chart in the active workbook. In your workbook you can have separate chart sheets which sets charts as worksheets, or embedded charts which includes the chart as an object within a worksheet. A chart sheet or an embedded chart is active if it has been selected it or if it has been activated with the Activate method. You can also apply the ActiveChart property to a Workbook or Window object by specifying the workbook or window to look for the active chart.
Following codes return the active chart in the active workbook:
MsgBox “Active Chart name is: ” & Application.ActiveChart.Name
MsgBox “Active Chart name is: ” & Application.ActiveWorkbook.ActiveChart.Name
MsgBox “Active Chart name is: ” & ActiveChart.Name
MsgBox “Active Chart name is: ” & ActiveWorkbook.ActiveChart.Name
Following code returns the active chart in the specified workbook (named “ExcelVBA.xlsm”):
MsgBox “Active Chart name is: ” & Application.Workbooks(“ExcelVBA.xlsm”).
ActiveChart.Name
Application.ActivePrinter Property. This property sets (or returns) the active printer’s name.
Set a local HP LaserJet 1022 printer on LPT1 as the active printer:
Application.ActivePrinter = “HP LaserJet 1022 on Ne02:”
ActivePrinter = “HP LaserJet 1022 on Ne02:”
Set a local Adobe PDF printer on Ne04 as the active printer:
Application.ActivePrinter = “Adobe PDF on Ne04:”
ActivePrinter = “Adobe PDF on Ne04:”
Selection Property. The Selection Property when applied to an Application object, returns the object which is selected in the active window. The selected object could be a Range object (single cell or range of cells) in the active worksheet, a ChartArea object in the active worksheet, and so on. You can also apply the Selection property to a Window object wherein the property will return the selected object in the specified window.
Presuming the current selection to be a Range object in the active worksheet, to clear the contents of the cells currently selected in the active worksheet, use any of the 2 code lines (with or without using the Application object qualifier):
Application.Selection.Clear
Selection.Clear
Example: Select a Range of cells, get the object type of the selection, use the Selection to change font color & font size.
Sub SelectionProperty()
‘Select a Range of cells, get the object type of the selection, use the Selection to change font color & font size.
‘select a range of cells in the active worksheet:
Range(“A1:C3”).Select
‘get the object type of the selection – returns “Range”:
MsgBox TypeName(Selection)
‘change the font color of the selected range to Red:
Selection.Font.Color = vbRed
‘change the font size of the selected range to 11:
Selection.Font.Size = 14
End Sub
Sheets Property. A Sheets Collection object refers to all sheets contained in a workbook, which includes chart sheets and worksheets. The Sheets Property, applied to an Application object, returns a Sheets collection in the Active Workbook. You can also apply this property to a Workbook object by specifying the workbook which will return a Sheets collection in the specified Workbook.
Below example shows how to count the number of sheets in the active workbook and return the name of each sheet.
Sub SheetsCollection()
Dim n As Integer
‘count number of sheets in the active workbook:
MsgBox “Number of Sheets in the Active Workbook are: ” & Application.Sheets.Count
MsgBox “Number of Sheets in the Active Workbook are: ” & Sheets.Count
‘Sheet Name based on the index value:
For n = 1 To Sheets.Count
MsgBox Sheets(n).Name
Next n
‘Sheet Name by looping through all sheets in the active workbook:
For Each Sheet In Sheets
MsgBox Sheet.Name
Next
End Sub
Range Property. The Range Property returns a Range object (single cell or range of cells). You can use the syntax: Range(cell1). This can only be an A1-style reference, and you can use a range operator (ie. colon) or the union operator (ie. comma), or it can be a named range. You can also use the syntax: Range(cell1,cell2) – where cell1 and cell2 are Range objects that represent the range of contiguous cells specifying the start and end cells. To apply the Range property to an Application object, you can either omit the object qualifier viz. Range(cell1) or use Application.Range(cell1), both of which will return the Range object in the ActiveSheet. To apply the Range Property to a Workbook object or Range object, specify the respective object qualifier as shown below.
Range Property as applicable to the Application Object
Any of the following codes will insert a value of 10 in the cell A1 of the active sheet (with or without using the Application object qualifier or typing ActiveSheet):
Application.ActiveSheet.Range(“A1”).Value = 10
ActiveSheet.Range(“A1”).Value = 10
Application.Range(“A1”).Value = 10
Range(“A1”).Value = 10
The following will insert a value of 10 in the cells A1, A2, A3 & A4 (wherein the cells refer to the upper-left corner & lower-right corner of the range) of the active sheet. The second expression uses the syntax: Range(cell1,cell2):
Range(“A1:A4”).Value = 10
Range(“A1”, “A4”).Value = 10
The following will insert a value of 10 in the cells A1, B2 & C4 of the active sheet (note that specifying “Value” after Range is not necessary because this assumed being the default property of the Range object):
Range(“A1,B2,C4”) = 10
The following will insert a value of 10 in the Named Range “Score” of the active sheet, viz. you can name the range A1 as “Score” and use the following code:
Range(“Score”) = 10
Range Property as applicable to the Worksheet Object
The following will insert the text “Steve” in the cell A1 of the worksheet named “Sheet1”:
Worksheets(“Sheet1”).Range(“A1”) = “Steve”
Range Property as applicable to the Range Object
When Range Property is applied to a Range object, then the property becomes relative to the Range object (reference starting from upper-left corner of the Range object) as shown below:
Sub RangeProperty()
‘Range Property as applicable to the Range Object
‘select a range in the active sheet:
Range(“C5:E8”).Select
‘inserts a value of 10 in Range C5:
Selection.Range(“A1”) = 10
‘inserts a value of 11 in Range C6:
Selection.Range(“A2”) = 11
‘inserts a value of 20 in Range D5:
Selection.Range(“B1”) = 20
‘inserts a value of 20 in Range D6:
Selection.Range(“B2”) = 21
End Sub
The Calculate Method, when applied to an Application object, calculates all workbooks that are open. You can also apply this method to a Worksheet object by specifying the worksheet in a workbook; or you can apply this method to a Range Collection by specifying a cell or a range of cells in a worksheet.
Calculate Method as applicable to the Application Object
Use any of the following code lines to calculate all workbooks that are open:
Application.Calculate
Calculate
Calculate Method as applicable to the Worksheet Object
Use any of the following code lines to calculate a specified worksheet (named “Sheet1”):
Application.Worksheets(“Sheet1”).Calculate
Worksheets(“Sheet1”).Calculate
Calculate Method as applicable to the Range of Cells
Use any of the following code lines to calculate the specified range (cells A5, A6 & A7) in a worksheet:
Application.Worksheets(“Sheet1”).
Range(“A5:A7”).Calculate
Worksheets(“Sheet1”).Range(“A5:A7”).Calculate
The following code line calculates the entire column (column A) in a worksheet:
Worksheets(“Sheet1”).Columns(1).Calculate
The following code line calculates the specific cells A5, A6, B7 & B20 in a worksheet:
Worksheets(“Sheet1”).
Range(“A5,A6,B7,B20”).Calculate
Use the following code line to calculate the specified range (cells A5, A6 & A7) in the active worksheet:
Range(“A5:A7”).Calculate
Speed up your VBA Code by Turning off Screen Updates and Automatic Calculations
In Excel, the default calculation mode is Automatic Mode (Application.Calculation = xlCalculationAutomatic), wherein Excel automatically calculates each cell as you enter it. When Excel is in Manual Mode (Application.Calculation = xlCalculationManual), calculation is done only when requested by the user viz. on clicking “Calculate Now” or pressing F9 or changing the calculation mode to Automatic. While in Automatic Mode, for each new value entered by a macro, Excel will recalculate all cells referred to or impacted by the new value thereby slowing the execution of the vba code. This can slow down the working of the vba code considerably, especially in the case of large macros wherein calculations are significant. To speed up a macro and make the execution faster & efficient, it is typical of developers to turn off automatic calculation right at the start of the macro, recalculate the specific worksheet or range using the Calculate method within the macro, and then turn on the automatic calculation back at the end of the code.
The following Example turns off Screen Updates and Automatic Calculations & use the Calculate Method, while running their vba codes (macros):
Sub CalculateMethod()
‘turn off Screen Updates and Automatic Calculations & use the Calculate Method, while running code.
‘turn off Screen Updates and Automatic Calculations:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Enter Your Code Here
‘use the Calculate method to calculate all open workbooks:
Application.Calculate
‘turn on Screen Updates and Automatic Calculations:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub