Validate Date in VBA

Sub Feed_Check_Date_Function()

‘ Date is within the Valid Range
Check_Date “2011-09-01?, “2011-09-10?, “2011-09-01?

‘ Date is NOT within the Valid Range
Check_Date “2011-09-01?, “2011-09-10?, “2011-09-21?

End Sub

Function Check_Date(ByVal StartDate As Date, ByVal EndDate As Date, ByVal DateTobeChecked As Date)

If DateDiff(“d”, StartDate, DateTobeChecked) <> 0 Then
MsgBox “Enter a correct date!!!!!”
End If

End Function

Check Column Exist in Excel

'Check the column exist in Excel File. In Fnd string pass the Column heading text.

Function CheckColumnExist(Fnd As String) As Boolean
'Check the column exist in Excel File.
CheckColumnExist = False
Set r1 = Cells.Find(What:=Fnd, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not r1 Is Nothing Then
CheckColumnExist = True
End If
End Function

To get windows temp directory path

'This private method is used to get windows temp directory path

Private Function GetTmpPath()

Dim sFolder As String ' Name of the folder
Dim lRet As Long ' Return Value

sFolder = String(MAX_PATH, 0)
lRet = GetTempPath(MAX_PATH, sFolder)

If lRet <> 0 Then
GetTmpPath = Left(sFolder, InStr(sFolder, _
Chr(0)) - 1)
Else
GetTmpPath = vbNullString
End If

End Function

To Find the Last Used Row in Excel

Function FindLastRow() As String
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
FindLastRow = LastRow
End If
End Function

To Find the Last Used Column in Excel

Function FindLastColumn() As String
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
FindLastColumn = LastColumn
End If
End Function

Inserting and retrieving text from a Bookmark.

There are several methods of inserting text at/into a bookmark. The method you use depends on whether you need to retrieve the text from the bookmark at a later time.

Lets look at the more obvious ways of inserting text at a bookmark.

ActiveDocument.Bookmarks("myBookmark").Range.Text = "Inserted Text"

If the bookmark is a placeholder bookmark, the inserted text will look like this:

I Inserted Text

If the bookmark is an enclosing bookmark, it will be deleted, and the inserted text will appear in it's place.

ActiveDocument.Bookmarks("myBookmark").Range.InsertBefore _
"Inserted Text"

ActiveDocument.Bookmarks("myBookmark").Range.InsertAfter _
"Inserted Text"

With both these methods, if the bookmark is a placeholder bookmark, the text will be inserted after the bookmark:

I Inserted Text

With enclosing bookmarks (even if the bookmark only encloses a space), the following occurs:

InsertAfter – [ Original Text ] Inserted Text
InsertBefore – [ Inserted Text Original Text ]

In order to retrieve the text in a bookmark, the bookmark needs to be
an enclosing bookmark. Then you can use the following to retrieve the text from the bookmark:

strBookmark = ActiveDocument.Bookmarks("myBookmark").Range.Text

You have already seen how to add text to an enclosing bookmark using the InsertBefore method above. But what if you want to insert text into a placeholder bookmark (making it an enclosing bookmark) so that you can retrieve the text from it at a later time ? And what if the bookmark is already an enclosing bookmark but you want to replace the text inside it ? There is no single command in VBA to achieve this. What you need to do is replace the bookmark with the inserted text (the bookmark is deleted), then re-create the bookmark around the inserted text. The following code is an example of how this is done:

Dim bmRange As Range

Set bmRange = ActiveDocument.Bookmarks("myBookmark").Range

bmRange.Text = "Inserted Text"

ActiveDocument.Bookmarks.Add _
Name:="myBookmark", _
Range:=bmRange.

Thanks!

Working with Bookmarks in VBA

Types of Bookmarks
The most important thing you need to know when working with bookmarks in Word is that there are two “types” of bookmarks – “placeholder” bookmarks and “enclosing” bookmarks.

Before we proceed, and whenever you work with bookmarks, you should turn on display of bookmarks by going to Tools | Options | View and selecting “Bookmarks”. This makes it easier to see what's actually happening.

(1) Placeholder Bookmarks
If you click somewhere in the document and insert a bookmark it will look like a beam I – this is a “placeholder” bookmark.

(2) Enclosing Bookmarks
Now, if you select some text and insert a bookmark it will look like the selected text is enclosed in square brackets ie: [selected text] – this is an “enclosing” bookmark.