'Unable to find circular reference in custom made formula for excel
Ok, so I'm completely unable to understand why there is a circular refence, or why the function is being called again in the following code:
Option Explicit
Enum rc
rc1 = 2
rl1 = 2
rcwork = 10 ' Has to be an even number, it's the column index where the 'Work Hours' column is
End Enum
Public Function WorkHours(currentRow As Long) As Variant
Dim i As Long, j As Long, lastCol As Long, lastColTrue As Long ' ,lastRow As Long
Dim workTime As Variant
Dim conditionMissingExit As Boolean, conditionMissingEntry As Boolean, conditionMissingEntryExit As Boolean
Application.Volatile
'Init values
conditionMissingExit = False
conditionMissingEntry = False
i = currentRow
lastCol = CurrentMonth.Cells(i, rc1).End(xlToRight).Column
lastColTrue = CurrentMonth.Cells(i, rcwork).End(xlToLeft).Column
If lastCol = rcwork And lastColTrue >= rc1 Then
lastCol = lastColTrue
ElseIf lastCol = rcwork And lastColTrue < rl1 And CurrentMonth.Cells(i, rc1 + 1) = "" Then
lastCol = rc1 + 1
ElseIf lastCol = rcwork And CurrentMonth.Cells(i, rcwork - 2) <> "" Then
lastCol = rcwork - 1
End If
If lastColTrue = lastCol Then
lastCol = lastColTrue
Else
If lastColTrue = 1 And CurrentMonth.Cells(i, rc1).Value = "" Then
workTime = ""
GoTo SafeExit
End If
For j = lastCol To lastColTrue
If Not IsNumeric(Cells(i, j).Value) Then
workTime = "Invalid Entry at cell (" & i & "," & ColIndexToLetter(j) & ")"
GoTo SafeExit
End If
If CurrentMonth.Cells(i, j).Value = "" And (j Mod 2 = 0) Then
conditionMissingEntry = True
If conditionMissingEntry Then
If CurrentMonth.Cells(i, j + 1).Value = "" And Not ((j + 1) Mod 2 = 0) Then
workTime = "Missing entry and exit"
GoTo SafeExit
Else
workTime = "Missing entry"
GoTo SafeExit
End If
End If
ElseIf CurrentMonth.Cells(i, j).Value = "" And Not (j Mod 2 = 0) Then
conditionMissingExit = True
If conditionMissingExit Then
If CurrentMonth.Cells(i, j + 1).Value = "" And ((j + 1) Mod 2 = 0) Then
workTime = "Missing entry and exit"
GoTo SafeExit
Else
workTime = "Missing exit"
GoTo SafeExit
End If
End If
End If
Next j
End If
For j = rc1 To lastCol Step 2
conditionMissingExit = (Not CurrentMonth.Cells(i, j).Value = "" And CurrentMonth.Cells(i, j + 1).Value = "")
conditionMissingEntry = (CurrentMonth.Cells(i, j).Value = "" And Not CurrentMonth.Cells(i, j + 1).Value = "")
conditionMissingEntryExit = (CurrentMonth.Cells(i, j).Value = "" And CurrentMonth.Cells(i, j + 1).Value = "")
If Not IsNumeric(Cells(i, j).Value) Then
workTime = "Invalid Entry at cell (" & i & "," & ColIndexToLetter(j) & ")"
Exit For
ElseIf Not IsNumeric(Cells(i, j + 1).Value) Then
workTime = "Invalid Entry at cell (" & i & "," & ColIndexToLetter(j + 1) & ")"
Exit For
End If
If Not conditionMissingExit And Not conditionMissingEntry And Not conditionMissingEntryExit Then
workTime = workTime + DateDiff("n", CurrentMonth.Cells(i, j).Value, CurrentMonth.Cells(i, j + 1).Value) / 60
ElseIf conditionMissingEntry Then
workTime = "Missing entry"
Exit For
ElseIf conditionMissingExit Then
If CurrentMonth.Cells(i, j + 2).Value = "" And j + 2 <= lastCol Then
workTime = "Missing entry and exit"
Exit For
Else
workTime = "Missing exit"
Exit For
End If
ElseIf conditionMissingEntryExit Then
workTime = "Missing entry and exit"
Exit For
End If
Next j
SafeExit:
WorkHours = workTime
End Function
Public Function ColIndexToLetter(ColIndex As Long) As String
'This function returns the a String corresponding to the letter associated to the column of index ColIndex
Dim CellAddress As String
CellAddress = Cells(1, ColIndex).Address
ColIndexToLetter = Split(CellAddress, "$")(1)
End Function
The formula on cell J2 is:
=WorkHours(2)
which calls the public function WorkHours
I do not understand what is going on because the macro-formula works , not perfectly, because the function keeps calling itself for some reason when I fill the other cells, it will give the circular error only when I fill the H column cell without filling also the I column cell (See image below)
Anyway the main problem here is that the function keeps being called for the same row several times, and I do not know why.
UPDATE:
Thank you Tim Williams and Toddleson for taking your time to look at my problem. Tim, following your advice I modified the WorkHours function like this:
Public Function WorkHours(currentRow As Long) As Variant
Dim i As Long, j As Long, lastCol As Long, lastColTrue As Long ' ,lastRow As Long
Dim workTime As Variant
Dim conditionMissingExit As Boolean, conditionMissingEntry As Boolean, conditionMissingEntryExit As Boolean
Application.Volatile
Debug.Print Application.Caller.Address
End Function
The formula is present only on cell J2 and it's being called like this:
=WorkHours(2)
So that no reference at all to any cell is inputed into the formula. After that as you can see from the code of the function, the is no cell reading whatsoever.
Now the output in the immediate window is:
$J$2
Meaning the function is being called only once...
I need to analyse in my previous code what is reading/accessing the calling cell...
Anyway, thank you very much Tim Williams, I didn't know that reading the calling cell would cause this behaviour as it's the first time I use application.volatile
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|