PDA

View Full Version : UDF Returns circular reference...?



ragingradish
07-09-2008, 06:38 PM
I've written a user defined function which loads from a xla file. The unfortunate thing is, it results in a circular reference and I have no idea why. It seemed to be working correctly originally but when I tested it on other machines in the office and at home began to misbehave.

Excel complains as follows: "MS Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference." Near as I can tell, no other cell is being referenced.

What this function does is interfaces with an accounting program which is sort-of based around a database - it retrieves an account balance based on the given parameters, year and account number.

Any idea how to troubleshoot this?


Public Function act(yr as String, acct as String) as Double

Application.Volatile (False)

yr = UCase(yr)

Dim cwPath As String
Dim FS As Office.FileSearch

Dim objCaseware As Object
Dim colCLients As Caseware.CWClients
Dim oClientFile As Caseware.CWClient
Dim colAccts As Caseware.CWAccounts
Dim acctNum, currentBalance

cwPath = ActiveWorkbook.path

Set FS = Application.FileSearch

With FS

.NewSearch
.LookIn = cwPath
.SearchSubFolders = False
.Filename = "*.ac"
.LastModified = msoLastModifiedAnyTime
.Execute

End With

fileCaseware = FS.FoundFiles(1)

Set objCaseware = CreateObject("Caseware.Application")

Set colCLients = objCaseware.Clients

On Error Resume Next

Set oClientFile = colCLients.Open(fileCaseware)

Select Case Err.Number

Case 0:
'do nothing

Case Else:

Set objCaseware = Nothing
Exit Function

End Select

On Error GoTo 0

Set colAccts = oClientFile.Accounts

Set acctNum = colAccts.Get(acct)

Set currentBalance = acctNum.Balances

Select Case yr

Case "AY", "YR0":
act = currentBalance.ReportBalance(0, 0, -1, True, False, False, False, 0)

Case "PY", "YR1":
act = currentBalance.ReportBalance(1, 0, -1, True, False, False, False, 0)

Case "AY:FX", "YR0:FX":
act = currentBalance.ReportBalance(0, 0, -1, True, True, False, False, 0)

Case "PY:FX", "YR1:FX":
act = currentBalance.ReportBalance(1, 0, -1, True, True, False, False, 0)

End Select

Set objCaseware = Nothing

End Function

TomSchreiner
07-10-2008, 10:50 AM
Assuming that the yr and acct arguments are ranges some or all of the time...

Change any reference to yr and acct to a value type instead of a reference type. Variable length strings are reference types.

Try this first:
Public Function act(ByVal yr As String, ByVal acct As String) As Double

If that fails, try this:
Public Function act(yr As Variant, acct As Variant) As Double
Application.Volatile (False)

Dim cwPath As String
Dim FS As Office.FileSearch

Dim objCaseware As Object
Dim colCLients As Caseware.CWClients
Dim oClientFile As Caseware.CWClient
Dim colAccts As Caseware.CWAccounts
Dim acctNum, currentBalance
Dim yr2 As String, acct2 As String

If TypeName(yr) = "Range" Then
yr2 = UCase(yr.Text)
Else
yr2 = CStr(yr)
End If

If TypeName(acct) = "Range" Then
acct2 = UCase(acct.Text)
Else
acct2 = CStr(acct)
End If


cwPath = ActiveWorkbook.Path

Set FS = Application.FileSearch

With FS

.NewSearch
.LookIn = cwPath
.SearchSubFolders = False
.Filename = "*.ac"
.LastModified = msoLastModifiedAnyTime
.Execute

End With

fileCaseware = FS.FoundFiles(1)

Set objCaseware = CreateObject("Caseware.Application")

Set colCLients = objCaseware.Clients

On Error Resume Next

Set oClientFile = colCLients.Open(fileCaseware)

Select Case Err.Number

Case 0:
'do nothing

Case Else:

Set objCaseware = Nothing
Exit Function

End Select

On Error GoTo 0

Set colAccts = oClientFile.Accounts

Set acctNum = colAccts.Get(acct2)

Set currentBalance = acctNum.Balances

Select Case yr2

Case "AY", "YR0":
act = currentBalance.ReportBalance(0, 0, -1, True, False, False, False, 0)

Case "PY", "YR1":
act = currentBalance.ReportBalance(1, 0, -1, True, False, False, False, 0)

Case "AY:FX", "YR0:FX":
act = currentBalance.ReportBalance(0, 0, -1, True, True, False, False, 0)

Case "PY:FX", "YR1:FX":
act = currentBalance.ReportBalance(1, 0, -1, True, True, False, False, 0)

End Select

Set objCaseware = Nothing

End Function