CODEWITHSUNDEEP
excelvba
PhilNBlanks
PhilNBlanks

Reputation: 125

Why does my function fail when moved to Personal.xlsb?

I have a function that checks if a date value is between two dates and returns "True" if it is and "False otherwise.

The problem I'm having is that the function works perfectly if it is in the same module where it is being called from but produces "Compile error: Expected Function or variable" if it is in PERSONAL.XLSB.

I have a reference set to personal.xlsb in my project and all my other other Functions and Subs located in Personal.xlsb work without a problem.

Here is the Function in question:

Public Function InDateRange() As Boolean
     Dim DateCell As Range
          Set DateCell = Range(Column_Letter(ActiveCell.Column) & 1)

     If DateCell < Range("HISTMIN") Or DateCell > Range("HISTMAX") Then
          InDateRange = False
     Else
          InDateRange = True
     End If
End Function

And here is the routine that calls it:

Sub Filter_Summary()
     If InDateRange() Then
           With Sheets("HistoryRptSummary").Range("A1").CurrentRegion
                .Range("A1:AH3000").AutoFilter Field:=2, Criteria1:=">=" & .Range("STARTDATE"), _
                Operator:=xlAnd, Criteria2:="<" & .Range("ENDDATE")     '.Range("F1").Value
           End With
      Else
           'Send Error msg
      End If
End Sub

I have tried send "Range(Column_Letter(ActiveCell.Column) & 1)" as an argument (as type Range and as type String) but got the error "Wrong number of arguments or invalid property assignment"

Any help will be greatly appreciated.

Upvotes: 0

Views: 87

Answers (1)

AsUsual
AsUsual

Reputation: 524

This works..

Personal.xlsb:

Public Function InDateRange(DateCell As Double, hMin As Double, hMax As Double) As Boolean
    If DateCell > hMin And DateCell < hMax Then InDateRange = True
End Function

Worksheet/Module:

Sub Filter_Summary()
    If Application.Run("PERSONAL.XLSB!InDateRange", Cells(1, ActiveCell.Column), [HISTMIN], [HISTMAX]) = True Then
        With Sheets("HistoryRptSummary").Range("A1").CurrentRegion
            .Range("A1:AH3000").AutoFilter Field:=2, Criteria1:=">=" & .Range("STARTDATE"), _
            Operator:=xlAnd, Criteria2:="<" & .Range("ENDDATE")     '.Range("F1").Value
        End With
    Else
        MsgBox "Error msg"
    End If
End Sub

Upvotes: 1

Related Questions