CODEWITHSUNDEEP
vbaexceldateobject
Mauro
Mauro

Reputation: 479

Error storing a date in an object using workday function

I have the following simple code:

    Sub newsub()

Dim currentday As Date
Dim cd As String

currentday = Format(WorskheetFunction.WorkDay(today, -1), dd / mm / aaaa)
cd = currentday
MsgBox cd

End Sub

But I get

424 ERROR: AN OBJECT IS REQUIRED

Anyone could tell me why is this happening?

Upvotes: 0

Views: 222

Answers (3)

braX
braX

Reputation: 11755

You need quotes around "dd/mm/aaa" (which I think should actually be "dd/mm/yyyy")

You also may want to use this instead since Format returns a string, not a date:

currentday = CVDate(Format(WorksheetFunction.WorkDay(Now(), -1), "dd/mm/yyyy"))

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

WorskheetFunction.WorkDay

You have a typo here, VBA runtime doensn't know what to resolve WorkskheetFunction to, and because Option Explicit isn't specified, it's creating an undeclared variable on-the-fly, an implicit Variant whose underlying type depends on how and where you're using it.

And because ABC.XYZ(foo, bar) can only grammatically be a member call on some object, VBA runtime expects WorkskheetFunction to be an object, and it isn't - it's never assigned, so its underlying type is Variant/Empty, which isn't an object - hence, an "Object Required" error is thrown.

Always use Option Explicit.

Then you won't be happily compiling and running code with typos.

You could also leverage IntelliSense to help prevent typos, by qualifying WorksheetFunction with the global Application object:

Application.WorksheetFunction.WorkDay(...)

That way you get autocomplete/IntelliSense when you type the . dot. Another difference is that if the worksheet function throws an error, with this early-bound syntax you'll get an actual VBA runtime error, whereas with the late-bound syntax you're using (with which you're not getting parameter tooltips / intellisense) if the function results in an error value it will return an error and make an invalid assignment to a Date variable, throwing a potentially confusing "Type Mismatch" error.

Upvotes: 1

Xabier
Xabier

Reputation: 7735

You seem to be trying to format Today, I would replace that with Now() and it works as expected.

Change your code to the following:

Sub newsub()
Dim currentday As Date
currentday = Format(Now() - 1, "dd/mm/yyyy") 'use Now() and format to your liking to get the right date
MsgBox currentday 
End Sub

Upvotes: 1

Related Questions