Combining two vba functions

Question

I have the following VBA functions in Excel (datecleanup and date1) that I would like to combine together into date1. Or preferably take the logic in dateclean and put into date1. This I'm sure is very simple, however I am new to VBA and not sure how I can accomplish this.

datecleanup function:

Function datecleanup(inputdate As Variant) As Variant

If Len(inputdate) = 0 Then
  inputdate = "01/01/1901"
Else
  If Len(inputdate) = 4 Then
    inputdate = "01/01/" & inputdate
  Else
    If InStr(1, inputdate, ".") Then
        inputdate = Replace(inputdate, ".", "/")
    End If
    dateclean = Split(strInput, Chr(32))(0)
  End If
End If
End Function

date1 function:

datecleanup = inputdate

Function date1(strInput) As String
 date1 = Split(strInput, Chr(32))(0)

End Function

I would like the date1 logic to occur as the final part of the dateclean function. How can I accomplish this? Thanks very much!

EDIT:

This is the correct datecleanup function:

Function datecleanup(inputdate As Variant) As Variant

If Len(inputdate) = 0 Then
    inputdate = "01/01/1901"
Else
  If Len(inputdate) = 4 Then
    inputdate = "01/01/" & inputdate
  Else
    If InStr(1, inputdate, ".") Then
        inputdate = Replace(inputdate, ".", "/")
    End If

  End If
End If

datecleanup = inputdate

End Function

Create a subroutine, or use an existing subroutine, and call both functions in the order you want them performed. This may require using global variables to store the data between the two functions. — Cyril
– Cyril, Commented May 18, 2018 at 17:15

JNevill · Accepted Answer · 2018-05-18 17:52:09Z

2

Here's some cleaned up logic with it all combined into a single function:

Function datecleanup(inputdate As Variant) As String

    If Len(inputdate) = 0 Then
        inputdate = "01/01/1901"
    ElseIf Len(inputdate) = 4 Then
        inputdate = "01/01/" & inputdate
    ElseIf InStr(1, inputdate, ".") Then
        inputdate = Replace(inputdate, ".", "/")
    End If  

    datecleanup = Split(inputDate, Chr(32))(0)
End Function

Alternatively, you could keep them separate functions and just call the datecleanup function from within your date1 function:

Private Function datecleanup(inputdate As Variant) As String
    If Len(inputdate) = 0 Then
        inputdate = "01/01/1901"
    ElseIf Len(inputdate) = 4 Then
        inputdate = "01/01/" & inputdate
    ElseIf InStr(1, inputdate, ".") Then
        inputdate = Replace(inputdate, ".", "/")
    End If  

    datecleanup = inputdate     
End Function

Function date1(strInput) As String
    date1 = Split(datecleanup(strInput), Chr(32))(0)
End Function

This is nice because it keeps the logic separate (if that's desirable here...)

edited May 18, 2018 at 17:52

answered May 18, 2018 at 17:23

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

JBinson88 Over a year ago

I tried your first method, however I am getting a "Subscript out of range" run-time error. Any ideas?

JBinson88 Over a year ago

I just edited the post above as the first function was not correct. If you wouldn't mind taking a look. It it giving that error for: datecleanup = Split(strInput, Chr(32))(0).

JNevill Over a year ago

Oh! of course it would. Sorry. Fat fingered that line. I have corrected.

JNevill Over a year ago

And given your edit (it makes sense now... I was wondering how that would have even functioned before)... either one of these options should work.

JBinson88 Over a year ago

Awesome this is great!! Works as expected! I'm using the Split function because I have some data integrity issues with text being added into date fields. The Split function works great at capturing only the date as long as the date appears first in the string. However if Text appears first then the Split is returning back the first word, and ignoring the subsequent date in the string. An example is the string 'Mumps on 8/10/15' the split returns back 'Mumps'. Probably should be asked in another thread I'm assuming...

|

Collectives™ on Stack Overflow

Combining two vba functions

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related