Converting from string to date

Question

Short story:

My question is two fold:

1. How do I copy over this text 05-Jun-2014 from a text file so that the text 05-Jun-2014 does not get displayed as #NAME? or get cut off at 5? (I need the whole thing copied over.)

2. How do I take that text and make it a date?

Long story

In this text file I have dates in this format day-month-year, e.g 05-Jun-2014. As you see, the "e" in June is cut off. If I type in 05-Jun-2014 in excel, it recognizes it. I use CDate to convert the string to a date.

My problem is multi-fold. Essentially I want excel vba to recognize 05-Jun-2014 as a date.

If I try to copy the phrase 05-Jun-2014 from my text document. I will only get 5 to copy over. I copy the string to a jagged array jaggArray and from there I call it.

Below you can see my code for how I copy text:

Private Function createGCStruct(ByRef tempString() As String, ByVal aNum As Integer, ByVal oNum As Integer, _
ByRef oList() As String, ByVal index As Integer, ByVal gcSoiDate As Date, ByVal gcFName As String, _
ByVal typeName As String) As gcBStruct

'...code...

' JaggedArray:
Dim jaggArray() As Variant

'...code...

'2) Capture structure information from textfile array

    'A) remove unnecessary spaces from existing Array and Place into jaggedArray
    jaggArray = splitStringArrayElements(tempString)

'...code...

End Function

splitStringArrayElements Function():

Private Function splitStringArrayElements(tempString() As String) As Variant()
' 1) Variables:
    Dim j As Long
    Dim trimmedString As String
    Dim jaggArray() As Variant
    ReDim jaggArray(LBound(tempString()) To UBound(tempString()))
    Dim emptyStringArrayPlaceholder() As String
    ReDim emptyStringArrayPlaceholder(0 To 0)
' 2) Remove unwanted spaces from tempString() and create a JaggedArray
    For j = LBound(tempString()) To UBound(tempString())
        ' Remove spaces inbetween
        trimmedString = Trim(tempString(j))
        ' If empty line, array is empty
        If trimmedString = "" Then
            jaggArray(j) = emptyStringArrayPlaceholder
        ' Else add array without spaces
        Else
            jaggArray(j) = SplitAdv(trimmedString, " ")
        End If
    Next j

    splitStringArrayElements = jaggArray
End Function

splitAdv() Function:

Public Function SplitAdv(ByVal InputText As String, Optional ByVal Delimiter As String) As Variant

' This function splits the sentence in InputText into
' words and returns a string array of the words. Each
' element of the array contains one word.

    ' This constant contains punctuation and characters
    ' that should be filtered from the input string.
    Const CHARS = """-" 'Potential options are: "!?,.;:""'()[]{}"
    Dim strReplacedText As String
    Dim intIndex As Integer

    ' Replace tab characters with space characters.
    strReplacedText = Trim(Replace(InputText, _
         vbTab, " "))

    ' Filter all specified characters from the string.
    For intIndex = 1 To Len(CHARS)
        strReplacedText = Trim(Replace(strReplacedText, _
            Mid(CHARS, intIndex, 1), " "))
    Next intIndex

    ' Loop until all consecutive space characters are
    ' replaced by a single space character.
    Do While InStr(strReplacedText, "  ")
        strReplacedText = Replace(strReplacedText, _
            "  ", " ")
    Loop

    ' Split the sentence into an array of words and return
    ' the array. If a delimiter is specified, use it.
    'MsgBox "String:" & strReplacedText
    If Len(Delimiter) = 0 Then
        SplitAdv = VBA.Split(strReplacedText)
    Else
        SplitAdv = VBA.Split(strReplacedText, Delimiter)
    End If
End Function

If I remove the - when I copy, the three parts become: 5-Jan-00 #NAME? 14:42:58

Here is where I copy over the date to a structure (assuming i removed the "-"). My goal was to copy over the parts of the date "05", "Jun", and "2014", and then recombine them in this format: 05 Jun,2014, and then try to convert that to a date:

Dim rDStart As Integer      ' row of first gcDate
Dim cDStart As Integer      ' col of first gcDate
Dim tempD1 As Date
Dim tempD2 As Date
rDStart = 6                                            ' the row of the first gcDate
cDStart = 2                                            ' the column of the first gcDate
'Collect gcDate
'***I get my errors below***
tempD1 = CDate(jaggArray(rDStart - 1)(cDStart - 1) & " " & jaggArray(rDStart - 1)(cDStart) & ", " & jaggArray(rDStart - 1)(cDStart + 1))
tempD2 = CDate(jaggArray(rDStart - 1)(cDStart + 2))
createGCStruct.gcDate = tempD1 + tempD2

Answer 1

The way to create a "#NAME?" message with VBA is to do the following:

Range("A1") = "=05-Jun-2014"

Note that the "=" operator causes Excel to treat the following as an expression which in this case would be 5 minus Jun minus 2014. Because you have no function defined with the NAME 'Jun' you get the "#NAME?" message.

The following does not produce an error and Excel also correctly recognises the value as a date.

Range("A1") = "05-Jun-2014"

I suggest that you review where you are placing the value into the sheet which I believe is the likely source of the bug.

Answer 2

You could just use CDate without splitting the string at all. CDate will recognise many date formats, and as long as they are not ambiguous (which "05-Jun-2014" isn't), the conversion should work just fine. This works for me:

Dim d As Date
d = CDate("05-Jun-2014")
With Range("A1")
    .Value = d
    .NumberFormat = "dd-mmm-yyyy"
End With