CODEWITHSUNDEEP
vbaexcel
user5873603
user5873603

Reputation:

ByRef argument type mismatch - Excel VBA

Afternoon,

I have a function to calculate week day name from a date:

Function calcWeekdayName(calcDate As Date) As String

Dim calcWeekday As Integer
calcWeekday = Weekday(calcDate, vbMonday)

calcWeekdayName = weekdayName(calcWeekday, True, vbMonday)

End Function

I am then using a Public variable to pass the date to this function. The Public variable is declared on Sheet 2:

Public searchDate As Date

The variable is then set in the sub below my function:

searchDate = Worksheets("Update Data").Range("B3").Value

B3 on Update Data is formatted dd/mm/yyyy

When I try to set the variable weekdayName using the function and the searchDate variable I get a ByRef argument type mismatch.

On Sheet 2:

 Public weekdayName As String

In Sub:

 weekdayName = calcWeekdayName(searchDate)

The current value of B3 is 28/03/2016 but I have also tried 01/01/2016.

Cheers

Upvotes: 3

Views: 783

Answers (2)

D_Bester
D_Bester

Reputation: 5911

You have a problem with WeekDayName variable. You should rename it myWeekDayName to avoid confusion with the VBA function of the same name.

Public searchDate As Date
Public myWeekDayName As String

Function calcWeekdayName(calcDate As Date) As String
    Dim calcWeekday As Integer
    calcWeekday = Weekday(calcDate, vbMonday)
    calcWeekdayName = VBA.weekdayName(calcWeekday, True, vbMonday)
End Function

Sub test()
    searchDate = Worksheets("Sheet1").Range("B3").Value
    myWeekDayName = calcWeekdayName(searchDate)
End Sub

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

Sheet2 is a worksheet object - the code-behind for it is a class module.

Class modules and "standard modules" are different in that their Public members require an instance to be accessible.

So you can do Sheet2.MyPublicVariable = 42, but you can't do just MyPublicVariable = 42 unless you're in the same module that's declaring that variable.

Your problem would have been prevented by specifying Option Explicit at the top of the module; then, searchDate would have been unaccessible and thus undeclared, and the VBA code would have refused to compile.

By not specifying Option Explicit, you allowed the use of undeclared variables, and that is the root of all evil in VBA.

Use. Option. Explicit.

Upvotes: 4

Related Questions