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
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