Why won't this Function Work?

[PNGBill]

This Public Function Works in the immediate window.

?LoanBalanceToDate("7869", "7/29/2010")

But when used in another Function

Private Function TestLateFees()

    Dim LateFees As Currency
    
        LateFees = LoanBalanceToDate("7869", "4/29/2010")

End Function

Error message is:

Compile error:

Sub or Function not defined

Appreciate any advise on where I went wrong. Code Compiles without error.

This is the code I would like to get it working in but would be happy just to get the test code above working.

Private Function GetLateFees() As Currency

    Dim LoanID As String
    Dim LateFeesNew As Currency
    Dim TransDate As Date
    Dim LateFeeRate As Currency
    Dim RepayAmount As Currency
    Dim LoanBalance As Currency
    
    LateFeesNew = 0         'Set Variable to Zero
    LoanID = Me.LDPK        'Set Current Reord LDPK on Form to be Variable LoanID
    TransDate = DLookup("[LDSt]", "TBLLOAN", "LDPK=" & [LoanID]) 'Set First Repayment Due Date of Loan As Variable TransDate
    LateFeeRate = DLookup("[LoanLateFee]", "TBLLOAN", "LDPK=" & LoanID)     'Set Late Fee Amount for this Loan As Variable LateFeeK
    RepayAmount = DLookup("[LDPayK]", "TBLLOAN", "LDPK=" & LoanID)     'Set Repay Amount for this Loan As Variable RepayAmount
    
    LoanBalance = LoanBalanceToDate("LoanID", "TransDate")           'Set Loan Balance as at First Repay Date of Loan
    
    GetLateFees = LoanBalance

End Function

 

[MarkK]

Note that the immediate window is context sensitive. What this means is that you can run a private function in the immediate window if that private function appears in the current code window. Make a different code window current and a private function in the previous module is out of scope and will generate the Sub or Function not defined error.

In your second block of code, this line ....

  LoanBalance = LoanBalanceToDate("LoanID", "TransDate")

should be

  LoanBalance = LoanBalanceToDate(LoanID, TransDate)

You don't want to pass the explicit strings "LoanID" and "TransDate", rather you want to pass the values that those variables represent.

 

[PNGBill]

Thanks lagbolt,

The original function is Public and works anywhere.
The Function TestLateFees() shows as TestLateFees in the window top right of the screen yet I still get the error message when using the immediate window.

I have edited the GetLateFees() Function to read LoanBalanceToDate(LoanID, TransDate) but get a compile error when compiling the code.
TransDate is highlighted and the error is ByRef argument type mismatch.

I an guessing this means there may be something in my earlier code

TransDate = DLookup("[LDSt]", "TBLLOAN", "LDPK=" & [LoanID])

Could this be to do with US date?

 

[MarkK]

When you get a 'Sub or Function not defined' error, is a line of code highlighted? Which line?

In GetLateFees(), probably the function LoanBalanceToDate() requires a string as the second parameter and you are passing in a date.

Dim TransDate As Date

How the date is formatted could be a factor if you pass it as a string since there is no real standard about whether the month or the day comes first. Wherever possible I would try to use date variables when working with dates so maybe the LoanBalanceToDate() function needs some attention.

 

[PNGBill]

When I test "TestLateFees()" in the immediate window I get the "Sub or Function not defined" error and no highlighted code. - code is:

Private Function TestLateFees()

    Dim LateFees As Currency
    
        LateFees = LoanBalanceToDate(7869, "4/29/2010")

End Function

yet the public function LoanBalanceToDate(7869, "4/29/2010") will work in the immediate window.

I would have thought that what will work in the immediate will work in the function.

Here is the full code for LoanBalanceToDate
and I note that the Date Variable AnyDate is a String - should this be changed?
Our data is non US date.

Public Function LoanBalanceToDate(LoanRef As String, AnyDate As String) As Currency        ' Calculate Loan Current Balance As At Selected Date
    
    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim sqlString As String
    Dim LoanTRNDRSum As Currency   'Hold Sum TRNDR for Loan as calculated
    Dim LoanTRNPRSum As Currency   'Hold Sum TRNPR for Loan as calculated
    Dim LoanRepay As Currency      'Hold Loan Repayments as calculated
    
    LoanTRNDRSum = 0                ' set variable to Zero to start
    LoanTRNPRSum = 0                ' set variable to Zero to start
    LoanRepay = 0                   ' set variable to Zero to start
    
                    'sqlString to Sum Loan ID TRNDR's
      sqlString = "SELECT TBLTRANS.LDPK, Sum(TBLTRANS.TRNDR) AS CountOfRec " & _
            "FROM TBLTRANS " & _
            "WHERE (((TBLTRANS.TRNACTDTE)<=#" & Format(AnyDate, "mm/dd/yyyy") & "# AND ((TBLTRANS.TRNTYP)=""Interest"" Or (TBLTRANS.TRNTYP)=""Late fee"" Or (TBLTRANS.TRNTYP)=""Legal Fees""))) " & _
            "GROUP BY TBLTRANS.LDPK " & _
            "HAVING (((TBLTRANS.LDPK)=" & LoanRef & "));"
           
         'Open Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset(sqlString)
    
        If rst.RecordCount <> 0 Then
    rst.MoveFirst
         'Assign SQL result to Variable
    LoanTRNDRSum = NZ(rst!CountOfRec, 0)  'Put sqlString result to variable
        Else
    LoanTRNDRSum = 0
        End If
        
                          'sqlString to Sum Loan ID TRNPR's
     sqlString = "SELECT TBLTRANS.LDPK, Sum(TBLTRANS.TRNPR) AS CountOfRec " & _
            "FROM TBLTRANS " & _
            "WHERE (((TBLTRANS.TRNACTDTE)<=#" & Format(AnyDate, "mm/dd/yyyy") & "# AND ((TBLTRANS.TRNTYP)=""Interest""))) " & _
            "GROUP BY TBLTRANS.LDPK " & _
            "HAVING (((TBLTRANS.LDPK)=" & LoanRef & "));"
   
               'Open Recordset
    Set rst = dbs.OpenRecordset(sqlString)
        
                If rst.RecordCount <> 0 Then
    rst.MoveFirst
         'Assign SQL result to Variable
    LoanTRNPRSum = NZ(rst!CountOfRec, 0)  'Put sqlString result to variable
        Else
    LoanTRNPRSum = 0
        End If

        'SQL String to Sum Loan Repayments by Loan ID
        sqlString = "SELECT Sum(tblMemberRepayments.PaymentAmt) AS SumOfPaymentAmt " & _
            "FROM tblBankStatements INNER JOIN tblMemberRepayments ON tblBankStatements.StatementID = tblMemberRepayments.StatementID " & _
            "WHERE (((tblBankStatements.StatementDate)<=#" & Format(AnyDate, "mm/dd/yyyy") & "#)) " & _
            "GROUP BY tblMemberRepayments.LoanID " & _
            "HAVING (((tblMemberRepayments.LoanID)=" & LoanRef & "));"
   
         'Open Recordset
    Set rst = dbs.OpenRecordset(sqlString)
    
        If rst.RecordCount <> 0 Then
    rst.MoveFirst
        'Assign SQL to Variable
    LoanRepay = NZ(rst!SumOfPaymentAmt, 0)
        Else
    LoanRepay = 0
        End If
    
    LoanBalanceToDate = LoanTRNDRSum + LoanTRNPRSum - LoanRepay
    
    'Close database variables
    rst.Close
    dbs.Close

 

[PNGBill]

This Function Works

Public Function GetMemClubPointsAvailable(RecordID) As Long
            'RecordID is the Control Value on the Form or Report For the MemberID
            
            Dim AvailablePoints As Integer
            Dim PurchasePoints As Integer
            Dim TradedPoints As Integer
            Dim CompletedLoanPoints As Integer
            Dim MemberID As Integer
            
            MemberID = RecordID
            
            PurchasePoints = GetMemPurchClubPoints(MemberID)
            TradedPoints = GetMemPointsTraded(MemberID)
            CompletedLoanPoints = GetMemClubPointsCompleted(MemberID)
            
            AvailablePoints = CompletedLoanPoints + PurchasePoints - TradedPoints
            
            GetMemClubPointsAvailable = AvailablePoints
            
            
End Function

In the immediate window I type "?GetMemClubPointsAvailable(1817) and the result is the correct answer.

Then why won't my earlier function work?? I have tried using functions that don't require a Date but they still don't work.

One difference is that this function, ClubPoints, is a Public Function in a Group Module whereas the one not working is a Private Function in a module attached to a Form.

 

[MarkK]

If I get an error message box from the debugger and no code is highlighted I presume this to be corruption in the file. I commonly get a 'User Defined Type Not Defined' error and no code highlighted and in those cases I create a new file and import all the objects from the damaged file as the only fix I know.

I wouldn't say that you "should" use a date variable. Your code failed because this function is looking for a string and you passed it a date. There's your trouble.
I like to use as specific a datatype as possible because I think this minimizes ambiguity, but it's a sort of religious argument.
In respect to "shoulds" and your code I would say you should indent an If...Else...End If like this

If something Then
  Truepart 
Else
  Falsepart
End If

And the three 'subtotals' you produce ...

LoanTRNDRSum + LoanTRNPRSum - LoanRepay

... should themselves be calculated in separate functions.
If I was your successor coming to service this function I would have preferred that you implement the indents and subroutines as more important issues than the datatype of the date variable.
But that's just my opinion.

 

[PNGBill]

Thanks again lagbolt for the advice on the function - much appreciated.

I have tried a new test function and still get an error. sub or function not defined.

Private Function TestLateFees()

    Dim RecordID As Integer
    RecordID = 1817
    
        TestLateFees = GetMemClubPointsAvailable(RecordID)
       
End Function

In the immediate window, this returns 170 - correct result

?GetMemClubPointsAvailable(1817)

What, apart from corruption, is the problem?? very simple code, I would have thought

 

[boblarson]

You can't assign a value to a function if you don't specify its datatype:

Private Function TestLateFees() [COLOR=red][B]As Currency[/B][/COLOR]
 
    Dim RecordID As Integer
    RecordID = 1817
 
        [COLOR=red]TestLateFees =[/COLOR] GetMemClubPointsAvailable(RecordID)
 
End Function

 

[PNGBill]

Thanks Bob but no difference, still same error message.

 

[boblarson]

In the upper level you have a mistake:

Public Function GetMemClubPointsAvailable(RecordID) As Long

needs to be

Public Function GetMemClubPointsAvailable(RecordID As Long) As Long

Or

Public Function GetMemClubPointsAvailable(RecordID As Integer) As Long

 

[PNGBill]

Thanks Bob, Have update the Public Function and it still works fine.
But the small TestLateFees() still throws same error message - Sub or Function not defined.

 

[jal]

I got the impression a while back that VBA can be a little sensitive to the physical order in which your blocks of code are arranged. For example I seem to recall having a problem doing this:

Private function calculate() as Double

End function

Private Type Customer
LastName as string
End Type

I had to put the "Type" blocks above the sub and function blocks. I had to find the correct order of:

Properties
Types
Subs
Functions
API Calls

but I can't remember the exact order - maybe that's the problem here?

 

[jal]

Oh, sorry, didn't realize you were trying to run it in the Immediate Window

 

[PNGBill]

Thanks jal,
trying to run in immediate window is just for test purposes.

Really trying to make a Do While Loop but so far hardly past stage 1

 

[ChrisO]

Sweep the floor, condense and redefine the problem succinctly.

 

[PNGBill]

Maybe a stupid question but do all functions work in the immediate window??

ie, just because it won't work in the immediate window does this always mean there is something wrong??

 

[ChrisO]

Words like always, all, never, must and anything else absolute do not sit well with me.

By pure definition it implies that the programmer knows all, all that has in the past been created, all that currently exists and all that might be created in the future.

Surely this logical position can’t be substantiated.

Focus on the desired outcome; do you want it to work in the immediate window or do you want it to work a runtime?

 

[PNGBill]

Runtime is the desired. Immediate is just to make sure it works but of course I could be trapped in a time warp if some procedures don't work in immediate.

 

[MarkK]

So where do we stand here? Is this thing moving ahead? Is it solved? Bill, you were saying about a Do...While loop?