Calculate End Date of the Project using Excel VBA

Calculate End Date using Excel VBA

EDATE returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.

Function Get_The_EndDate()


Dim WrkMonths As Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = WorksheetFunction.EDate(StartDate, 3)

MsgBox "End Date of the Project is := " & EndDate

End Function


The above will work in Excel 2007 only

Disallow user interaction - Excel VBA

Allow / Disallow user interaction in Excel VBA


Sub Hold_User_Interaction()


Application.Interactive = False

' Do necessary calculations / processing


Application.Interactive = True


End Sub

Application.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

Speech in VBA or Spell out messages and instructions in VBA

If 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 2007

Sub Speak_Out()

Application.Speech.Speak "Speaking out to you..."

' Synchronous Method
For i = 1 To 100
i = i + 1
Next i

Application.Speech.Speak "Synchronous Speak"

Application.Speech.Speak "asynchronous Speak - the following code will be executed, when this statment is executed", True

MsgBox "Wait..."

For i = 1 To 100
i = i + 1
Next i

End Sub


The 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.

VBA Response from Message Boxes

Message Boxes in VBA (Action on user response)

Sub Get_Response_From_MessageBoxes()

Dim Response

Response = MsgBox("With to Continue?", vbYesNo, "Yes or No")
If Response = vbYes Then
MsgBox "Reponse was yes!"
Else
MsgBox "Reponse was no"
End If

Response = MsgBox("Error while processing", vbAbortRetryIgnore, "Abort Retry ignore")
If Response = vbAbort Then
Exit Sub
ElseIf Response = vbRetry Then
GoTo StartAgain
ElseIf Response = vbIgnore Then
'... continue ...
End If
End Sub

Convert Dates to Arrays using Array Function

Convert Dates to Arrays using VBA

Here is the way to convert dates to array. Replace the normal quotes used for string to hash (#).

Function Convert_Date_Into_Array()

Dim arDates

arDates = Array(#1/1/2008#, #2/1/2008#, #3/1/2008#)

For i = 1 To UBound(arDates)
MsgBox arDates(i)
Next i


End 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.

Exclude Holidays in Net Working Days (Excel VBA)

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 us

Returns 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 Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = #12/12/2008#
WrkDays = WorksheetFunction.NetworkDays(StartDate, EndDate, Range("b2:b15"))

MsgBox "No of Working Days Left := " & WrkDays

End Function

The function is exclusive in Excel 2007. There is no equivalent function in Excel 2003

Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text

Get Net Working Days in a Year / Quarter using VBA

No of Working days in a Year / quarter using VBA

Many 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 us

Returns 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 Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = #12/31/2008#
WrkDays = WorksheetFunction.NetworkDays(StartDate, EndDate)
Publish Post
MsgBox "No of Working Days Left := " & WrkDays

End Function

The function is exclusive in Excel 2007. There is no equivalent function in Excel 2003

Sleep Function in Excel VBA

Application.Wait as Sleep in VBA

You 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()

Debug.Print Now

Application.Wait DateAdd("s", 10, Now)

Debug.Print Now



End Sub

The code will give the following output

02-03-2008 19:12:47
02-03-2008 19:12:57

If 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 screen

Private Sub Form_Activate()


frmSplash.Show
DoEvents
Sleep 1000
Unload Me
frmProfiles.Show

End Sub

Calculate Working days (Excluding Holdiays) using Excel Function / VBA

Calculate the End date (Excluding Holidays) based on No. of Days using Excel Function / VBA

Most 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 Integer
Dim StartDate As Date
Dim EndDate As Date
Dim arHolidays() As Date

'arHolidays() = Array(#1/1/2008#)

StartDate = Now
EndDate = WorksheetFunction.WorkDay(StartDate, 12, #2/23/2008#)


End Function


The following excludes the holiday dates from the range (Range("b2:b15") here)

Function Calculate_Workday_With_Holidays_As_Range()




Dim WrkDays As Integer
Dim StartDate As Date
Dim EndDate As Date
Dim arHolidays() As Date

'arHolidays() = Array(#1/1/2008#)

StartDate = Now
EndDate = WorksheetFunction.WorkDay(StartDate, 12, Range("b2:b15"))


End Function


The above excludes weekends and calculates the end date of the task based on the no. of days


Calculate the End date programmatically, Code Calculate Workdays - Excel VBA,

Calculate Workdays - Excel VBA

Calculate the End date based on No. of Days using Excel Function / VBA

Most 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 Integer
Dim StartDate As Date
Dim EndDate As Date

StartDate = Now
EndDate = WorksheetFunction.WorkDay(StartDate, 12)


End Function

The above excludes weekends and calculates the end date of the task based on the no. of days


Calculate the End date programmatically, Code Calculate Workdays - Excel VBA,

Delete Comments from Excel Workbook using VBA

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 it

The following code uses RemoveDocumentInformation. It removes all information of the specified type from the workbook. It is compatible with Excel 2007

Sub Remove_Comments_From_WKBK()
'
' Remove Comments from Excel 2007 Workbook
'

'
ActiveWorkbook.RemoveDocumentInformation (xlRDIComments)
End Sub

If you want the same for Excel 2003 and before here is the code

Sub 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
Next
End Sub

Opening Dynamic Text file in Excel

Query Table for Text / CSV Files

If 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 regularly

Sub TXT_QueryTable()

Dim ConnString As String

Dim qt As QueryTable

ConnString = "TEXT;C:\Temp.txt"


Set qt = Worksheets(1).QueryTables.Add(Connection:=ConnString, _
Destination:=Range("B1"))

qt.Refresh



End Sub


The 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.

Query Table with Excel as Data Source

Query tables can be of great help if you need to extract particular data from a data source

It 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 collection

However, 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 QueryTable

Here is one such example, which extracts data from MS Excel sheet


Sub Excel_QueryTable()

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String

Dim qt As QueryTable

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SubFile.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "Select * from [Sheet1$]"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _
Destination:=Range("B1"))

qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If


If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

Use the Add method to create a new query table and add it to the QueryTables collection.

You can loop through the QueryTables collection and Refresh / Delete Query Tables

If you use the above code for Excel 2010, you need to change the connection string to  the following

ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Om\Documents\SubFile.xlsx;Extended Properties=Excel 12.0;Persist Security Info=False"
Else it will thrown an 3706 Provider cannot be found. It may not be properly installed. error

See also:

Opening Comma Separate File (CSV) through ADO

Using Excel as Database using VBA (Excel ADO)

Create Database with ADO / ADO Create Database

ADO connection string for Excel

Combining Text Files using VBA

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 one

It uses File System Object and you need to add a reference of Microsoft Scripting Runtime

Sub Append_Text_Files()

Dim oFS As FileSystemObject
Dim oFS1 As FileSystemObject

Dim oTS As TextStream
Dim oTS1 As TextStream

Dim vTemp

Set oFS = New FileSystemObject
Set oFS1 = New FileSystemObject


For 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 i1

End Sub

The 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

Here are the primitive commands to open an XML file in Microsoft Excel.

Sub Open_XML_File()

Dim oWX As Workbook

Set oWX = Workbooks.OpenXML("c:\sample.xml")

End Sub




Sub Open_XML_File_As_List()

Dim oWX As Workbook

Set oWX = Workbooks.OpenXML(Filename:="c:\sample.xml", LoadOption:=XlXmlLoadOption.xlXmlLoadImportToList)

End Sub


This option will work for Excel 2003 and above

Visual Basic - Special Folders (Temp Folder , System Folder)

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 Runtime

On Error GoTo Show_Err

Dim oFS As FileSystemObject
Dim sSystemFolder As String
Dim sTempFolder As String
Dim sWindowsFolder As String

Set oFS = New FileSystemObject

' System Folder - Windows\System32
sSystemFolder = oFS.GetSpecialFolder(SystemFolder)

' Temporary Folder Path
sTempFolder = oFS.GetSpecialFolder(TemporaryFolder)

' Windows Folder Path
sWindowsFolder = oFS.GetSpecialFolder(WindowsFolder)

Dim a
a = oFS.GetFolder("m:\9.3 BulkLoad\BLT1_Base15.6\Reports\08-Nov-2007\Output\")

If Not oFS Is Nothing Then Set oFS = Nothing

Show_Err:

If Err <> 0 Then
MsgBox Err.Number & " - " & Err.Description
Err.Clear
End If
End Sub

You need to have reference to Microsoft Scripting Runtime to execute the above code

Array Dimensioning in Visual Basic

Dimensioning Arrays 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 two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The following example shows how you can increase the size of the last dimension of a dynamic array without erasing any existing data contained in the array.

Here is an example of Array redimensioning

Sub Array_Dimensioning()

Dim arPreserved() As Integer ' Preserved Array
Dim arErased() As Integer ' Array without Preserve

ReDim Preserve arPreserved(1, 1)
ReDim arErased(1, 1)

arPreserved(1, 1) = 1

ReDim Preserve arPreserved(1, 2)
arPreserved(1, 2) = 2

ReDim Preserve arPreserved(1, 3)
arPreserved(1, 3) = 3

ReDim Preserve arPreserved(2, 3) ' This statement will throw and error

' whereas the following statement will not as the Array is not preserved (Erased)
ReDim arErased(2, 1)

End Sub

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 two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array. The arPreserved falls under this category. However, arErased you can redimension the array in any dimension, but the contents will be erased with every Redim statement

Comparing two Word Documents using Word VBA

Compare Word Documents using VBA

Here is a simple routine, which will compare two Microsoft Word documents and return the status.


Sub IsDocument_Equal()

Dim oDoc1 As Word.Document
Dim oResDoc As Word.Document

' Delete the tables from both the document

' Delete the images from both the document

' Replace Paragraphs etc

Set oDoc1 = ActiveDocument

' comparing Document 1 with New 1.doc
oDoc1.Compare Name:="C:\New 1.doc", CompareTarget:=wdCompareTargetNew, DetectFormatChanges:=True

'This will be the result document
Set oResDoc = ActiveDocument

If oResDoc.Revisions.Count <> 0 Then
'Some changes are done
MsgBox "There are Changes "
Else
MsgBox "No Changes"
End If

End Sub

Convert URLs to Hyperlinks using VBA

Automatically create Hyperlinks for all URLs in a document

Microsoft 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.AutoFormatReplaceHyperlinks = True
ActiveDocument.Select
Selection.Range.AutoFormat
Selection.Collapse

Options.AutoFormatReplaceSymbols
End Sub

Warning: 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

Run a Automatic Macro in Word Document

Execute Word Macro on File Open

There 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 VBA

Sub Run_Macro_In_WordDocument()

Dim oWD As Word.Document
Set oWD = Documents.Add("c:\dcomdemo\sample.dot")
oWD.RunAutoMacro wdAutoOpen

End Sub

Here a new document is open based on the Sample.dot template and once the document is open the AutoOpen macro is fired

RunAutoMacro 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 happens

On the other hand, if a normal macro (not auto open etc) needs to be executed, Run method can be used

Application.Run "Normal.FormatBorders"