"Undefined Function" in Expression when using VBA module in query

[sirskeezy]

Hi all,

I am trying to calculate percentile values by creating a VBA code:

Option Compare Database
Public Function DPercentileExcel(expr As String, domain As String, Percentile As Double) As Variant
Dim strSQL As String
Dim N As Integer
Dim nSubk As Double
Dim vSubk As Variant
Dim vSubkPlus1 As Variant
Dim rs As DAO.Recordset

strSQL = "SELECT " & expr & " FROM " & domain
strSQL = strSQL & " WHERE NOT " & expr & " IS NULL ORDER BY " & expr
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If Not (rs.EOF And rs.BOF) Then
rs.MoveLast
rs.MoveFirst
Else
Exit Function
End If

N = rs.RecordCount

nSubk = Percentile / 100 * (N - 1) + 1
'using NIST nSubK = Percentile / 100 * (N+1)

If nSubk = 1 Then
DPercentileExcel = rs.Fields(expr)
ElseIf nSubk = N Then
rs.MoveLast
DPercentileExcel = rs.Fields(expr)
Else
rs.AbsolutePosition = nSubk - 1 '0 based
vSubk = rs.Fields(expr)
rs.AbsolutePosition = nSubk '0 based
vSubkPlus1 = rs.Fields(expr)
'Debug.Print nSubk
'Debug.Print vSubk
'Debug.Print vSubkPlus1
DPercentileExcel = vSubk + (nSubk - Int(nSubk)) * (vSubkPlus1 - vSubk)
End If
End Function

Public Sub testPercentileExcel()
Debug.Print DPercentileExcel("operatingcostspersf", "step1_operatingcostspersf", 0)
End Sub

When I try to use this function in my query, it says that DPercentileExcel is an undefined function in the expression...

My SQL looks like this:

SELECT Percentiles.percentile, DPercentileExcel("operatingcostsperSF","step1_operatingcostspersf",[percentile]) AS percentileoperatingcostspersf
FROM Percentiles;

Please Help!

O and btw when I try to save the module name from the 'Module 5' that it automatically gave to a new name, it says file not found. Does this mean anything??

 

[goodfellow]

Probably something was corrupted in your excel file. Try to create new one and copy/paste your code into it.