IsDate user defined function for vb.net excel interop

Question

Following code is simplified version of TnTinMn's code.

I also understand what this is doing: values(i, 1) = DateTime.TryParse(CStr(obj).Trim, dt)

Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    Dim app As New Excel.Application
    app.Visible = True
    Dim wb As Excel.Workbook = app.Workbooks.Add()
    Dim ws As Excel.Worksheet = CType(wb.Worksheets(1), Excel.Worksheet)

    ws.Range("A1:A10000").Value2 = "Hello"
    ws.Range("A1").Value2 = "1/1/2000"
    ws.Range("A2").Value2 = "I am not a date"
    ws.Range("A3").Value2 = 100.123

    Dim values As Object(,) = CType(ws.Range("A1:A10000").Value, Object(,))

    For i As Integer = 1 To values.GetUpperBound(0)

        Dim typDate As Type = GetType(DateTime)
        Dim typString As Type = GetType(String)

        Dim obj As Object = values(i, 1)
        Dim typeOfObj As Type = obj.GetType

        Dim dt As DateTime

        If typeOfObj Is typDate Then
            values(i, 1) = True
        ElseIf typeOfObj Is typString Then
            values(i, 1) = DateTime.TryParse(CStr(obj).Trim, dt)
        Else
            values(i, 1) = False
        End If
    Next

    ws.Range("B1:B10000").Value = values
End Sub

Another version;

Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
    Dim app As New Excel.Application
    app.Visible = True
    Dim wb As Excel.Workbook = app.Workbooks.Add()
    Dim ws As Excel.Worksheet = CType(wb.Worksheets(1), Excel.Worksheet)

    ws.Range("A1:A10000").Value2 = "Hello"
    ws.Range("A1").Value2 = "1/1/2000"
    ws.Range("A2").Value2 = "I am not a date"
    ws.Range("A3").Value2 = 100.123

    Dim values As Object(,) = CType(ws.Range("A1:A10000").Value, Object(,))

    For i = 1 To values.GetUpperBound(0)
        If IsDate(values(i, 1)) Then
            values(i, 1) = True
        Else
            values(i, 1) = False
        End If
    Next

    ws.Range("B1:B10000").Value = values
End Sub

score 0 · Answer 1 · answered Apr 29 '17 at 07:48

0

Use DateTime.TryParse():

Public Function IsDate(ByVal Input As String) As Boolean
    Return DateTime.TryParse(Input, Nothing)
End Function

answered Apr 29 '17 at 07:48

Visual Vincent

18,045
5
28
75

@KenKeniee : Don't think so. VBA and VSTO are two completely different things. VSTO is mainly for plugins that affect the program itself (new menus, toolbars, functions, etc.). – Visual Vincent Apr 29 '17 at 10:17
@KenKeniee : Such functions (called UDF) are not supported by default, but there are workarounds and helper libraries that'll make you able to use it like so. For instance there's ExcelDna: http://stackoverflow.com/questions/957575/how-to-easily-create-an-excel-udf-with-vsto-add-in-project – Visual Vincent Apr 29 '17 at 10:21

score 0 · Accepted Answer · answered Apr 29 '17 at 19:00

I dont want to use following code because following code is very slow.

   For i = 1 To 10000
        If Not IsDate(WorkSheet1.Range("A" & i).Value.ToString) Then
            WorkSheet1.Range("B" & i).Value = "FALSE"
        End If
   Next

Based on this statement, I am assuming that the IsDate function is not available to the Workbook.

When working with contiguous Excel ranges, it is best to pull the values into an array and process them. You can also write a array to an identically sized Range. Such reads and writes will be relatively quick.

The example below creates a new Workbook and writes out some data to Column A. The GetColumnA_SetColumnB method is what you should focus on. It retrieves the values in Column A and then processes them. The code reuses the the array created by reading the values to hold the result of determining if the value is a valid DateTime. Note that since the VBA IsDate function will evaluate a string that contains a valid date as True, such function is replicated in the method shown.

Private Const lastRow As Int32 = 10000

Private Sub demo()
    Dim app As New Excel.Application
    Dim wb As Excel.Workbook = app.Workbooks.Add()
    Dim ws As Excel.Worksheet = CType(wb.Worksheets(1), Excel.Worksheet)
    CreateColumnA(ws)   ' creates some values to work with
    GetColumnA_SetColumnB(ws)
    ' leave excel open and in user control
    app.Visible = True
    app.UserControl = True
End Sub


Private Sub CreateColumnA(ws As Excel.Worksheet)
    Dim values As Object(,) = CType(Array.CreateInstance(GetType(Object), New Int32() {lastRow, 1}, New Int32() {1, 1}), Object(,))
    For i As Int32 = 1 To lastRow
        values(i, 1) = DateTime.Now()
    Next
    ws.Range("A1:A" & lastRow.ToString).Value = values  ' set formatted values
    ws.Range("A1").Value2 = "  1/1/2000"    ' set a string that Vba.IsDate interprets as a Date
    ws.Range("A2").Value2 = "I am not a date"
    ws.Range("A3").Value2 = 100.123
    ws.Range("A3").ClearFormats()   ' make sure its formatted as a number
End Sub

Private Sub GetColumnA_SetColumnB(ws As Excel.Worksheet)
    Dim rngA As Excel.Range = ws.Range("A1:A" & lastRow.ToString)
    Dim rngB As Excel.Range = rngA.Offset(0, 1)

    Dim values As Object(,) = CType(rngA.Value, Object(,)) ' retrieve the formatted values

    Dim typDate As Type = GetType(DateTime)
    Dim typString As Type = GetType(String)

    For i As Int32 = 1 To values.GetUpperBound(0)
        Dim obj As Object = values(i, 1)
        Dim typeOfObj As Type = obj.GetType

        If typeOfObj Is typDate Then
            values(i, 1) = True
        ElseIf typeOfObj Is typString Then
            ' it still may be a Date
            Dim dt As DateTime
            values(i, 1) = DateTime.TryParse(CStr(obj).Trim, dt)
        Else
            values(i, 1) = False
        End If
    Next
    ' write Column B
    rngB.Value = values
End Sub

@KenKeniee, that statement is evaluating whether or not a string value can be interpreted as a date value. It is to replicate that same functionality of the VBA.IsDate function. In the VBA IDE immediate window, enter: `? VBA.IsDate(" 1/1/2017 ")` and press the enter key. It will evaluate to `True`. The `DateTime.TryParse` is not as forgiving as the VBA IsDate function in what it will accept as a valid date. If you need more cases to evaluate as True, you will need to add that logic. — TnTinMn, Apr 30 '17 at 17:25
@KenKeniee, LOL, I totally forgot about the compatibility IsDate function. Yes, use that function. It also handles the case where the retrieved object is Nothing (null) that I forgot to account for in my code. — TnTinMn, Apr 30 '17 at 18:16

IsDate user defined function for vb.net excel interop

2 Answers2