Getting type mismatch error in replace function in vba

Question

My vba code is to replace current time and date to 4_17_2014 8_09_00 PM format

But i am getting type mismatch error while running below VBA code

Function Current_Date_Time_Stamp()

Dim CurrTime As Date

CurrTime = Now
MsgBox Now
CurrTime = Replace(CurrTime, "-", "_")
MsgBox CurrTime
CurrTime = Replace(CurrTime, ":", "_")
Current_Date_Time_Stamp = CurrTime

End Function

Can anybody help me why i am getting error

Replace operates on Strings, and you're giving it a Date. Try looking at Format() if you want a specific representation of your Date value. — Tim Williams
– Tim Williams, Commented May 8, 2014 at 4:32

Dmitry Pavliv · Accepted Answer · 2014-05-08 07:30:55Z

1

As @Tim Williams mentioned in comments, Replace works only with string variables, but CurrTime is date (actually, date variables doesn't store format of date, but only date itself. Format when displaying date depends on regional settings and number format of your cell).

However you can use function with single line of code like this:

Function Current_Date_Time_Stamp()
    Current_Date_Time_Stamp = Format(Now, "mm_dd_yyyy h_mm_ss AM/PM;@")
End Function

answered May 8, 2014 at 7:30

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

Getting type mismatch error in replace function in vba

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related