Identify End of Document using Word VBA

Find End Of Document using Word VBA


Most often when we loop through the document, we need to know the End of Word Document. We can achieve that by using Bookmarks

Sub Drive_IS_EOD()

If IS_EOD(Selection.Range) = True Then
MsgBox "End of Document"
Else
MsgBox "Miles to Go:)"
End If

End Sub

The function below uses the Exists method to check if the bookmark exist in the specified range

Function IS_EOD(ByRef MRange As Range) As Boolean

If MRange.Bookmarks.Exists("\EndOfDoc") = True Then
IS_EOD = True
End If

End Function

\EndOfDoc is a predefined bookmark, which is used here

Check Existence of BookMark using VBA (Word VBA)

Find BookMarks using VBA

BookMarks are vital in Word. However, when you look for a particular bookmark using VBA, it will cease to exist causing 5101 - This bookmark does not exist error.

To avoid this it is better to use Exists Method to check if the Bookmark exist.

Sub Check_If_BookMark_Exists()

If ActiveDocument.Bookmarks.Exists("TempBKMK") = True Then
ActiveDocument.Bookmarks("TempBKMK").Range.Text = "Something"
End If

End Sub


Exists method determines whether the specified bookmark or task exists. Returns True if the bookmark or task exists

Adding Images to Header using VBA (Word VBA)

Use Pictures/Images in Word Document Header using Word VBA

Most often we will be using company's logo in Header. Here is the way to do it using VBA

Sub Add_File_Header()

Set docActive = Word.ActiveDocument

ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.InlineShapes.AddPicture "C:\My Documents\My Pictures\MYPicture.bmp"

docActive.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader

ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary).Range.Text = "Header text" ' ERROR

objWord.ActiveDocument.ActiveWindow.View.SeekView = wdSeekMainDocument

With docActive.PageSetup

.DifferentFirstPageHeaderFooter = False 'Set this to false will put text on first page, else will not.

End With

End Sub

Updating Word Fields (VBA)

Using Word VBA to Update all Field Codes in Word Document


Here is a simple macro that will update all the fields in a Word document

Sub Update_Field()

ActiveWindow.ActivePane.View.Type = wdPrintView
Application.ScreenUpdating = True
Selection.WholeStory
ActiveDocument.Fields.Update

End Sub

Save and Reopen all Workbooks (Excel VBA)

Save All Open Workbooks and Re-open them using VBA

Use SaveWorkspace method to save the current workspace and use the Open method to open the files again

Sub WorkPlace_Save_And_Resume()

' Save Workspace

Application.DisplayAlerts = False

Application.SaveWorkspace "C:\New Folder\TempWorkSpace.XLW"

Workbooks.Close

' Do my work here without anyother workbooks

Workbooks.Open "C:\New Folder\TempWorkSpace.XLW"

Application.DisplayAlerts = True

End Sub

The files in the application will be saved and re-opened using Open method

See also :

Excel VBA - 1004 -- The file could not be accessed

Save and Reopen all Workbooks (Excel VBA)

Save copy of the workbook

SaveAs Dialog - Controlled Save

Save RTF document as word

Run a Macro from Different Workbook

Execute a macro in a different workbook

Runs a macro or calls a function. This can be used to run a macro written in Visual Basic or the Microsoft Excel macro language, or to run a function in a DLL or XLL.

Sub Run_Macro_In_Different_WorkBook()

Application.Run "'C:\CanBeDeleted.xlsx.xlsm'!AnotherWrkBook_Macro"

End Sub


Runs a macro or calls a function. This can be used to run a macro written in Visual Basic or the Microsoft Excel macro language, or to run a function in a DLL or XLL.

Sub Run_Macro_In_Different_WorkBook_With_Arguments()

On Error GoTo Err_Trap

Application.Run "'C:\CanBeDeleted.xlsx.xlsm'!Function_Two_Args", "Argument 1", "Argument 2"

Err_Trap:
If Err <> 0 Then
Debug.Print Err.Number & Err.Description

End If


End Sub

The above code passes arguments to a macro in another workbook

The following errors need to be handled for forward compatibility of your macro

450 - Wrong number of arguments or invalid property assignment

449 - Argument not optional

Find Number of Days in a month using VBA (Excel 2007)

Calculate Number of Days in a Month

EOMONTH returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

Function No_of_Days_in_Month()

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

StartDate = Now
EndDate = WorksheetFunction.EoMonth(StartDate, 0)

' if you want the days count
MsgBox "No of days in current month := " & Day(EndDate)


End Function

The above code will work only in Excel 2007

Find Last Day of the Month using VBA (Excel 2007)

Calculate Last Day of the Month

EOMONTH returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.

Function Last_Day_Of_the_Month()

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

StartDate = Now
EndDate = WorksheetFunction.EoMonth(StartDate, 0)

MsgBox "Last day of the current month is := " & EndDate

End Function

The above code will work only in Excel 2007

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