CODEWITHSUNDEEP
excelvba
user3591858
user3591858

Reputation: 73

Getting type mismatch error in replace function in vba

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

Upvotes: 1

Views: 2251

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

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

Upvotes: 1

Related Questions