VBA Tips and Tricks

RSS Feed Homepage

Articles: 1, 2, 3

Disallow user interaction - Excel VBA
2008-03-03 03:44:00
Allow / Disallow user interaction in Excel VBASub Hold_User _Interaction ()Application.Intera ctive = False' Do necessary calculations / processingApplication.Interactive = TrueEnd SubApplication.Interactive is True if Microsoft Excel is in interactive mode; this property is usually True. If you set the this property to False, Microsoft Excel will block all input from the keyboard and mouse (except input to dialog boxes that are displayed by your code). Blocking user input will prevent the user from interfering with the macro as it moves or activates Microsoft Excel objects. Read/write Boolean. Remarks This property is useful if you're using DDE or OLE Automation to communicate with Microsoft Excel from another application. If you set this property to False, don't forget to set it back to True. Microsoft Excel won't automatically set this property back to True when your macro stops running.

Voice Messages in VBA
2008-03-03 03:39:00
Speech in VBA or Spell out messages and instructions in VBAIf you are developing applications for one and all, it would be great if you broadcast the messages in voice format. Here is the way you can achieve it in Excel VBA 2007Sub Speak_Out()Application.Speech.Speak "Speaking out to you..."' Synchronous MethodFor i = 1 To 100 i = i + 1Next iApplication.Speech.Speak "Synchronous Speak"Application.Speech.Speak "asynchronous Speak - the following code will be executed, when this statment is executed", TrueMsgBox "Wait..."For i = 1 To 100 i = i + 1Next iEnd SubThe synchronous message allows the message to be executed and holds subsequent code processing. In asynchronous Speak the code after the Speak statements are executed while the message is spelt out.
More About: Voice , Messages

VBA Response from Message Boxes
2008-03-03 03:37:00
Message Boxes in VBA (Action on user response)Sub Get_Response _From_MessageBoxes()Dim ResponseResponse = MsgBox("With to Continue?", vbYesNo, "Yes or No")If Response = vbYes Then MsgBox "Reponse was yes!"Else MsgBox "Reponse was no"End IfResponse = MsgBox("Error while processing", vbAbortRetryIgnore, "Abort Retry ignore")If Response = vbAbort Then Exit SubElseIf Response = vbRetry Then GoTo StartAgainElseIf Response = vbIgnore Then '... continue ...End IfEnd Sub
More About: Message

Convert Dates to Arrays using Array Function
2008-03-03 03:33:00
Convert Dates to Array s using VBAHere is the way to convert dates to array. Replace the normal quotes used for string to hash (#).Function Convert_Date_Into_Array()Dim arDatesarDates = Array(#1/1/2008#, #2/1/2008#, #3/1/2008#)For i = 1 To UBound(arDates) MsgBox arDates(i)Next iEnd Function The Array Function returns a Variant containing an array. Syntax Array(arglist) The required arglist argument is a comma-delimited list of values that are assigned to the elements of the array contained within the Variant. If no arguments are specified, an array of zero length is created.
More About: Convert

Exclude Holidays in Net Working Days (Excel VBA)
2008-03-03 03:29:00
No of Working days in a Year / quarter using VBA (Excluding Holidays )Many times we are confronted with a situation to estimate the days left in a quarter or year. The catch is the holidays, exclude Christmas, Thanksgiving, Martin Luthers day or Diwali from the working day. Here is a where Excel 2007 has simplified that for usReturns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.Here the holidays are excluded from the predefined range.Function Get_Net_Working_Days _Excluding_Holidays() Dim WrkDays As IntegerDim StartDate As DateDim EndDate As DateStartDate = NowEndDate = #12/12/2008#WrkDays = WorksheetFunction.NetworkDays(StartDate, EndDate, Range("b2:b15"))MsgBox "No of Working Days Left := " & WrkDaysEnd FunctionThe function is exclusive in Excel 2007. There is no equivalent fun...

Get Net Working Days in a Year / Quarter using VBA
2008-03-03 03:24:00
No of Working days in a Year / quarter using VBAMany times we are confronted with a situation to estimate the days left in a quarter or year. Here is a where Excel 2007 has simplified that for usReturns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.Function Get_Net_Working_Days ()Dim WrkDays As IntegerDim StartDate As DateDim EndDate As DateStartDate = NowEndDate = #12/31/2008#WrkDays = WorksheetFunction.NetworkDays(StartDate, EndDate)Publish PostMsgBox "No of Working Days Left := " & WrkDaysEnd FunctionThe function is exclusive in Excel 2007. There is no equivalent function in Excel 2003
More About: Quarter

Sleep Function in Excel VBA
2008-03-03 03:20:00
Application.Wait as Sleep in VBAYou can use Application.Wait instead of sleep function to hold the process for a specified period of time.Here is the way to achieve that:Sub Setting_Sleep_Without_Sleep_Function ()Deb ug.Print NowApplication.Wait DateAdd("s", 10, Now)Debug.Print NowEnd SubThe code will give the following output02-03-2008 19:12:4702-03-2008 19:12:57If you still require the Sleep Method here is it for you:Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)Here is a classical example of the use of Sleep function in a splash screenPrivate Sub Form_Activate() frmSplash.ShowDoEventsSleep 1000Unload MefrmProfiles.ShowEnd Sub
More About: Excel

Calculate Working days (Excluding Holdiays) using Excel Function / VBA
2008-03-03 03:15:00
Calculate the End date (Excluding Holidays) based on No. of Days using Excel Function / VBAMost of the time you want to exclude weekends and holidays in the calculation for workdays, here is the simple way to do that.This uses WORKDAY WorksheetFunction, which returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.Function Calculate_Workday_With_Holidays_direct Value()Dim WrkDays As IntegerDim StartDate As DateDim EndDate As DateDim arHolidays() As Date'arHolidays() = Array(#1/1/2008#)StartDate = NowEndDate = WorksheetFunction.WorkDay(StartDate, 12, #2/23/2008#)End FunctionThe following excludes the holiday dates from the range (Range("b2:b15") here)Function Calculate_Workday_With_Holidays_As_Range( ...
More About: Calculate

Calculate Workdays - Excel VBA
2008-03-03 03:10:00
Calculate the End date based on No. of Days using Excel Function / VBAMost of the time you want to exclude weekends in the calculation for workdays, here is the simple way to do that.This uses WORKDAY WorksheetFunction, which returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.Function Calculate_Workday()Dim WrkDays As IntegerDim StartDate As DateDim EndDate As DateStartDate = NowEndDate = WorksheetFunction.WorkDay(StartDate, 12)End FunctionThe above excludes weekends and calculates the end date of the task based on the no. of daysCalculate the End date programmatically, Code Calculate Workdays - Excel VBA,
More About: Calculate

Delete Comments from Excel Workbook using VBA
2008-03-03 03:05:00
Remove Comments Programmatically using Visual Basic Applications (VBA)Most of the times comments are used for internal purpose. This need not go with the workbbok, here is the way to remove itThe following code uses RemoveDocumentInformation. It removes all information of the specified type from the workbook. It is compatible with Excel 2007Sub Remove_Comments_From_WKBK()'' Remove Comments from Excel 2007 Workbook'' ActiveWorkbook.RemoveDocumentInformation (xlRDIComments)End SubIf you want the same for Excel 2003 and before here is the codeSub Remove_Comments_From_WKBK_2003()'' Remove Comments from Excel 2003 Workbook'' Dim wks As Worksheet Dim cmnt As Comment For Each wks In ActiveWorkbook.Sheets For Each cmnt In wks.Comments cmnt.Delete Next cmnt NextEnd Sub

Opening Dynamic Text file in Excel
2007-12-05 04:04:00
Query Table for Text / CSV File sIf you update some Excel frequently, you can keep it as shared and then ask your fellow colleagues to check if often (refresh)One of the good option is to have them as CSV file and use query table to update it regularlySub TXT_QueryTable()Dim ConnString As StringDim qt As QueryTableConnString = "TEXT;C:Temp.txt"Set qt = Worksheets(1).QueryTables.Add(Connection: =ConnString, _ Destination:=Range("B1")) qt.Refresh End SubThe Refresh method causes Microsoft Excel to connect to the query table’s data source, execute the SQL query, and return data to the query table destination range. Until this method is called, the query table doesn’t communicate with the data source.
More About: Opening , Dynamic

Query Table with Excel as Data Source
2007-12-05 03:56:00
Query tables can be of great help if you need to extract particular data from a data sourceIt represents a worksheet table built from data returned from an external data source, such as an SQL server or a Microsoft Access database. The QueryTable object is a member of the QueryTables collectionHowever, it need to be SQL server or a Microsoft Access database always. You can use CSV file or our fellow Microsoft Excel spreadsheet as a data source for QueryTableHere is one such example, which extracts data from MS Excel sheetSub Excel_QueryTable()Dim oCn As ADODB.ConnectionDim oRS As ADODB.RecordsetDim ConnString As StringDim SQL As StringDim qt As QueryTableConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =c:SubFile.xls;Extended Properties=Excel 8.0;Persist Security Info=False"Set oCn = New ADODB.ConnectionoCn.ConnectionString = ConnStringoCn.OpenSQL = "Select * from [Sheet1$]"Set oRS = New ADODB.RecordsetoRS.Source = SQLoRS.ActiveConnection = oCnoRS.OpenSet qt = Worksheets...
More About: Query

Combining Text Files using VBA
2007-12-05 03:33:00
Visual Basic Application to Merge Text Files Multiple utilities are available to split & merge text files. However, here is a simple one my friend uses to merge around 30 ascii files into oneIt uses File System Object and you need to add a reference of Microsoft Scripting RuntimeSub Append_Text_Files()Dim oFS As FileSystemObjectDim oFS1 As FileSystemObjectDim oTS As TextStreamDim oTS1 As TextStreamDim vTempSet oFS = New FileSystemObjectSet oFS1 = New FileSystemObjectFor i1 = 1 To 30 Set oTS = oFS.OpenTextFile("c:Sheet" & i1 & ".txt", ForReading) vTemp = oTS.ReadAll Set oTS1 = oFS.OpenTextFile("c:CombinedTemp.txt", ForAppending, True) oTS1.Write (vTemp)Next i1End SubThe code is simple.. it searches for files from Sheet1.txt ...Sheet30.txt and copies the content into one variable. Then it appends the content to CombinedTemp.txt

Open XML File in Excel
2007-12-05 03:28:00
Here are the primitive commands to open an XML file in Microsoft Excel .Sub Open _XML_File ()Dim oWX As WorkbookSet oWX = Workbooks.OpenXML("c:sample.xml")End SubSub Open_XML_File_As_List()Dim oWX As WorkbookSet oWX = Workbooks.OpenXML(Filename:="c:sample.xml ", LoadOption:=XlXmlLoadOption.xlXmlLoadImpo rtToList)End SubThis option will work for Excel 2003 and above

Visual Basic - Special Folders (Temp Folder , System Folder)
2007-12-04 03:27:00
Here is a simple routine to get special folders like temporary folder etc:Sub Get_Special _Folders()' Uses File System Object' Need to have reference to Microsoft Scripting RuntimeOn Error GoTo Show_ErrDim oFS As FileSystemObjectDim sSystemFolder As StringDim sTempFolder As StringDim sWindowsFolder As StringSet oFS = New FileSystemObject' System Folder - WindowsSystem32sSystemFolder = oFS.GetSpecialFolder(SystemFolder)' Temporary Folder PathsTempFolder = oFS.GetSpecialFolder(TemporaryFolder)' Windows Folder PathsWindowsFolder = oFS.GetSpecialFolder(WindowsFolder)Dim aa = oFS.GetFolder("m:9.3 BulkLoadBLT1_Base15.6Reports8-Nov-2007Ou tput")If Not oFS Is Nothing Then Set oFS = NothingShow_Err:If Err 0 Then MsgBox Err.Number & " - " & Err.Description Err.ClearEnd IfEnd SubYou need to have reference to Microsoft Scripting Runtime to execute the above code
More About: Visual Basic , Visual , Basic

Array Dimensioning in Visual Basic
2007-12-04 03:18:00
Dimensioning Array s in Visual Basic The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array. However, you can't declare an array of one data type and later use ReDim to change the array to another data type, unless the array is contained in a Variant. If the array is contained in a Variant, the type of the elements can be changed using an As type clause, unless you’re using the Preserve keyword, in which case, no changes of data type are permitted. If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has ...

Comparing two Word Documents using Word VBA
2007-12-04 03:12:00
Compare Word Documents using VBAHere is a simple routine, which will compare two Microsoft Word documents and return the status.Sub IsDocument_Equal()Dim oDoc1 As Word.DocumentDim oResDoc As Word.Document' Delete the tables from both the document' Delete the images from both the document' Replace Paragraphs etcSet oDoc1 = ActiveDocument' comparing Document 1 with New 1.docoDoc1.Compare Name:="C:New 1.doc", CompareTarget:=wdCompareTargetNew, DetectFormatChanges:=True'This will be the result documentSet oResDoc = ActiveDocumentIf oResDoc.Revisions.Count 0 Then 'Some changes are done MsgBox "There are Changes "Else MsgBox "No Changes"End IfEnd Sub

Convert URLs to Hyperlinks using VBA
2007-12-04 03:07:00
Automatically create Hyperlinks for all URLs in a documentMicrosoft Word has in-built intelligence to convert the URLs or Web Addresses to Hyperlinks automatically. This functionality is executed when you type some website/email address in word document.For some reason, if you want to be done on the Word document at a later stage you can do the following:Sub Make_URLs_as_HyperLinks()Options.AutoForm atReplaceHyperlinks = TrueActiveDocument.SelectSelection.Range. AutoFormatSelection.CollapseOptions.AutoF ormatReplaceSymbolsEnd SubWarning: I have set only AutoFormatReplaceHyperlinks = True and not set/reset others. You need to check all options as autocorrect/autoformat can cause undesirable changes that might go unnoticed
More About: Convert

Run a Automatic Macro in Word Document
2007-12-04 03:00:00
Execute Word Macro on File OpenThere are numerous instances where one stores the word document format as a Microsoft Word template. When the user opens the document (using the template), some macro needs to be executed. This can be achieved by RunAutoMacro Method of Word VBASub Run_Macro_In_WordDocument ()Dim oWD As Word.DocumentSet oWD = Documents.Add("c:dcomdemosample.dot")oWD. RunAutoMacro wdAutoOpenEnd SubHere a new document is open based on the Sample.dot template and once the document is open the AutoOpen macro is firedRunAutoMacro Method can be used to execute an auto macro that's stored in the specified document. If the specified auto macro doesn't exist, nothing happensOn the other hand, if a normal macro (not auto open etc) needs to be executed, Run method can be usedApplication.Run "Normal.FormatBorders"
More About: Automatic

Create Database with ADO / ADO Create Database
2007-11-20 02:54:00
Create Microsoft Access Database programmatically using ADOProgrammers who have used DAO will definitely miss the CreateDatabase in ADO. Here is one method to do the same using ADO.For doing this you need to include the ADO Extensions library in the project reference.This library consists of classes and methods to handle the Schema related activities like creation of database, table etcThe following code uses the Catalog object of the ADOX to create a new Microsoft Access databaseSub Create_DB_and_Table_Using_ADOX()Dim oDB As ADOX.CatalogDim sDBPAth As StringDim sConStr As StringDim oCn As ADODB.ConnectionDim oCM As ADODB.Command' ------------------------' Set the Path and Connection String' ------------------------sDBPAth = "c:TempMyAccounts.mdb"sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPAth & ";"' ------------------------' Create New ADOX Object' ------------------------Set oDB = New ADOX.CatalogoDB.Create sConStrSet oCn = New ADODB.Connectiono...
More About: Create

Excel VBA - FindAll Method
2007-10-07 04:07:00
One out of two code module in Excel VBA will have cells.Find or Findnext method. Here is a generic function - FindAll that can be used to retrieve information of all matching cells.Function FindAll(ByVal sText As String, ByRef oSht As Worksheet, ByRef sRange As String, ByRef arMatches() As String) As Boolean' ----------------------------------------- ----------------------------------------- ----------------------------' FindAll - To find all instances of the1 given string and return the row numbers.' If there are not any matches the function will return false' ----------------------------------------- ----------------------------------------- ---------------------------- On Error GoTo Err_TrapDim rFnd As Range ' Range ObjectDim iArr As Integer ' Counter for ArrayDim rFirstAddress ' Address of the First Find' -----------------' Clear the Array' -----------------Erase arMatchesSet rFnd = oSht.Range(sRang...
More About: Method

Disabling Macros in a Workbook (Excel VBA)
2007-10-07 04:00:00
Many workbooks will have macros embedded with it. When you open the Workbook in Excel , you would have noticed the Dialog with options Enable Macros / Disable MacrosThe following code snippet (Excel 2003) uses Application.FileDialog method to open a Workbook and disables the macros in it:Sub Open_File_With_Macros_Disabled()Dim i1 As IntegerDim secAutomation As MsoAutomationSecurity secAutomation = Application.AutomationSecurity Application.AutomationSecurity = msoAutomationSecurityForceDisable With Application.FileDialog(msoFileDialogOpen) .Show For i1 = 1 To .SelectedItems.Count MsgBox .SelectedItems(i1) Workbooks.Open .SelectedItems(i1) Next i1 End With Application.AutomationSecurity = secAutomation End Sub
More About: Acro

Automate Lotus Notes eMail using Visual Basic
2007-10-05 03:18:00
How to send Lotus Notes mail messages with Microsoft Visual Basic The following Visual Basic code will send a Notes e-mail message. The code includes examples of code to include an attachment and to save the sent message, which are both optional and can be removed if desired.Dim Maildb As ObjectDim MailDoc As ObjectDim Body As ObjectDim Session As Object'Start a session to notesSet Session = CreateObject("Lotus.NotesSession")'This line prompts for password of current ID noted in Notes.INICall Session.Initialize 'or use below to supply password of the current ID'Call Session.Initialize("")'Open the mail database in notesSet Maildb = Session.GETDATABASE("", "c: otesdatamailmymail.nsf")If Not Maildb.IsOpen = True ThenCall Maildb.OpenEnd If'Create the mail documentSet MailDoc = Maildb.CREATEDOCUMENTCall MailDoc.ReplaceItemValue("Form", "Memo")'Set the recipientCall MailDoc.ReplaceItemValue("SendTo", "John Doe")'Set subjectCall MailDoc.ReplaceItemValue("Subject", "Subject Text...
More About: Email

Reducing Size of Microsoft Access Database (Compact Database)
2007-09-27 03:10:00
Compact MS Access Database using VBA (ADO Code)If the DB Size is huge the compact DB utility [Compact & Repair Database (Tools-->Database utilities-->Compact and Repair Database) should reduce the DB Size.Here is the code for doing the same using VB/VBA (ADO)Public Sub CompactDB() 'Microsoft Jet and Replication objects Dim objJE As New JRO.JetEngine, strSource As String, strTarget As String DoEvents Busy True strSource = " " strTarget = " " objJE.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSource & ";", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strTarget & ";Jet OLEDB:Engine Type=4;" Busy False 'Engine type: 'Access 97 = 4 'Access 2000 = 5End Sub
More About: Compact

Excel VBA - Change Font Color for Part of Text
2007-09-16 04:56:00
Formatting Text (Cell Contents) Part ially using Excel VBASometimes, you need to differentiate some parts of text in an Excel cell. This can be done using formatting those characters.Here is the way that can be done using VBA using the Characters propertySub Color _Part_of_Cell()'Print ActiveCell.Characters.Count With ActiveCell.Characters(2).Font .Color = RGB(36, 182, 36) End With With ActiveCell.Characters(2, 2).Font .Color = RGB(36, 182, 36) End With With ActiveCell.Characters(, 2).Font .Color = RGB(36, 182, 36) End With End SubThe output of the above isSyntax expression.Characters(Start, Length) expression Required. An expression that returns an object in the Applies To list. Start Optional Variant. The first character to be returned. If this argument is either 1 or omitted, this property returns a range of characters starting with the first character. Length Optional Variant. The number of characters to be returned. If this argument is o...
More About: Change

Orientation of Cell Through Excel VBA
2007-09-16 04:44:00
Almost most of the Excel VBA programmers would have the requirement to change the orientation of the cell.Here is an exampleSub Orientation s()ActiveCell .Orientation = xlHorizontalActiveCell.Orientation = xlVerticalActiveCell.Orientation = xlUpwardActiveCell.Orientation = xlDownwardActiveCell.Orientation = 45ActiveCell.Orientation = -45End SubThe orientation of the cell in Excel would be as follows:

ShutDown Windows using VBA
2007-08-21 16:45:00
VBA Function to Logoff /VBA Function to Restart Windows Option Explicit' Win API DeclarationsConst MF_BYPOSITION = &H400&Private Const EWX_LOGOFF = 0Private Const EWX_SHUTDOWN = 1Private Const EWX_REBOOT = 2Private Const EWX_FORCE = 4Private Declare Function ExitWindowsEx Lib "user32.dll" ( _ ByVal uFlags As Long, _ ByVal dwReserved As Long) As LongUse the function with atmost caution, you will not be warned by Windows for Shutdown / Restart. Save all your work before trying this example:)Function Common_ShutDown_Logoff()'Shutdown WindowsCall ExitWindowsEx(EWX_SHUTDOWN, 0)'Restart WindowsCall ExitWindowsEx(EWX_REBOOT, 0)'logoff WindowsCall ExitWindowsEx(EWX_LOGOFF, 0)End Function

Disable Close Button in UserForm (VBA)
2007-08-21 16:39:00
Disable Close Button in Userform (Visual Basic)Option Explicit'API Declarations Const MF_BYPOSITION = &H400&Private Declare Function GetSystemMenu Lib "user32" _ (ByVal hwnd As Long, _ ByVal bRevert As Long) As LongPrivate Declare Function RemoveMenu Lib "user32" _ (ByVal hMenu As Long, _ ByVal nPosition As Long, _ ByVal wFlags As Long) As LongPublic Sub DisableCloseWindowButton(frm As Form) Dim hSysMenu As Long 'Get the handle of the Window hSysMenu = GetSystemMenu(frm.hwnd, 0) 'Disable the close button of the Form RemoveMenu hSysMenu, 6, MF_BYPOSITION 'Remove the seperator bar RemoveMenu hSysMenu, 5, MF_BYPOSITIONEnd Sub

Detecting duplicate values (Excel VBA)
2007-08-13 04:36:00
Check presence of values in a column/range using Excel VBAMost often a programmer would be given a job of Insert/Update scenarion in EXcel. That is, Insert a new row if a specific value does not exist; if it does then update some. So the process is to check for existence of a specific valueHere is a generic function ;Sub CheckForExistence()myVal = "Sample"myRange = "A:A"If Check_Val_Existence(myVal, myRange) = True Then MsgBox "Value exists"End IfEnd SubThis used the Find Method. This method finds specific information in a range, and returns a Range object that represents the first cell where that information is found. Returns Nothing if no match is found.Function Check_Val_Existence(ByVal sText, ByVal sRange) As BooleanDim rFnd As RangeDim sText As StringSet rFnd = ActiveSheet.Range(sRange).Find(What:=sTex t, LookAt:=xlPart)If Not rFnd Is Nothing Then Check_Val_Existence = TrueElse Check_Val_Existence = FalseEnd IfEnd Function
More About: Values

Automatically Event Repeat in Excel VBA (OnTime Method)
2007-08-13 04:27:00
VBA Code that can run at a fixed time can be done using the OnTime Method .OnTime Method schedules a procedure to be run at a specified time in the future (either at a specific time of day or after a specific amount of time has passed).expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule) expression Required. An expression that returns an Application object. EarliestTime Required Variant. The time when you want this procedure to be run. Procedure Required String. The name of the procedure to be run. LatestTime Optional Variant. The latest time at which the procedure can be run. For example, if LatestTime is set to EarliestTime + 30 and Microsoft Excel is not in Ready, Copy, Cut, or Find mode at EarliestTime because another procedure is running, Microsoft Excel will wait 30 seconds for the first procedure to complete. If Microsoft Excel is not in Ready mode within 30 seconds, the procedure won’t be run. If this argument is omitted, Microsoft Excel will...
More About: Event