Link to home
Start Free TrialLog in
Avatar of mcs26

asked on

VBA User Definded Types


I'm little confused as to why the function below does not work. The error message is, Compile Error: User-defined type is not defined. What's strange is if a turn the function into a sub instead and take out the last line, BenchmarkDates = BMDates
the code works and recognises the user defined type.

Use user defined types a lot in the same module and have no issues?

Thanks for any help,

Private Type TypeBMDates
    YTD As Date
    MTD As Date
    TMinus5 As Date
    TMinus1 As Date
End Type

Function BenchmarkDates() As TypeBMDates

Dim BMDates As TypeBMDates

    BMDates.YTD = DateSerial(Year(Now), 1, 0)
    BMDates.YTD = Last_Working_Day(BMDates.YTD)
    BMDates.MTD = DateSerial(Year(Now), Month(Now), 0)
    BMDates.MTD = Last_Working_Day(BMDates.MTD)
    BMDates.TMinus5 = Last_Working_Day(DateAdd("d", -7, Date))
    BMDates.TMinus1 = Last_Working_Day(DateAdd("d", -1, Date))

    BenchmarkDates = BMDates
    End Function

Open in new window

Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

The Type should be declared as Public
I think that there might be a bug (or at least a difference between the documentation and what actually happens)
Avatar of vb_elmar
Flag of Germany image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What is Last_Working_Day()?