My user defined VBA function is updating all cells using it with the same thing, I don't want it too

Question

I am trying to make it easier for users to work with an excel sheet instead of having to modify an arduous expression anytime a change is needed. I am dynamically placing the function's result into a cell. I got everything functioning how it should except:

If I go to another sheet and use the formula, it will return the proper results; however, when returning to another sheet that was already using using it, that sheet will display the most resent results and no longer its own instance of passed variables. These sheets also tie into a dashboard sheet so I need to make sure that if I calculate one sheet, it doesn't tamper with the others. I wasn't sure how to word this issue, so if there is nomenclature in place that I am not using or if this has been answered in the past, let me know and I will close this out.

'-------------------
'getScore
' This function is called from a cell and is passed an intager.
' The integer represents the section that it is being called from.
' There is also the sheet title that is passed thrugh to the range.
'-------------------

Function getScore(section As Integer, sheetTitle As String)
Application.Volatile

Dim rngSt As Integer
Dim rngEnd As Integer

rngSt = getRange(section, sheetTitle, 1) 'Gets start range for formula
rngEnd = getRange(section, sheetTitle, 2) 'Gets end range for formula


Dim Formula As String   'Broken into seperate concatinated lines for  readablility
'-(COUNTBLANK(H" & rngSt & ":H" & rngEnd & ")),"
' This section uses nested if statements to acrue the score through each level.

Formula = "=IF(SUM(D" & rngSt & ":D" & rngEnd & ")= nonBlank(D" & rngSt &  ":D" & rngEnd & "),"
Formula = Formula & "IF(SUM(F" & rngSt & ":F" & rngEnd & ")= nonBlank(F" &  rngSt & ":F" & rngEnd & "),"
Formula = Formula & "IF(SUM(H" & rngSt & ":H" & rngEnd & ")= nonBlank(H" &  rngSt & ":H" & rngEnd & "),"
Formula = Formula & "IF(SUM(J" & rngSt & ":J" & rngEnd & ")= nonBlank(J" &  rngSt & ":J" & rngEnd & "),"
Formula = Formula & "IF(SUM(L" & rngSt & ":L" & rngEnd & ")= nonBlank(L" &  rngSt & ":L" & rngEnd & "),5,4),3),2),1), 0)"

getScore = Eval(Formula) 'Evaluates formula and returns a score of 0-5.

End Function

Here is the getRange fucntion

   Function getRange(section As Integer, sheetName As String, rangePoint As Integer)
   Application.Volatile

   Dim FindRow As Range

   Dim ws As Worksheet
   Dim wb As Workbook

   Set wb = ActiveWorkbook

   If section = 1 Then
   
       If rangePoint = 1 Then
           With wb.Sheets(sheetName)
               Set FindRow = .Range("C9:C9")
           End With
           getRange = FindRow.Row
       End If
   
   If rangePoint = 2 Then
       With wb.Sheets(sheetName)
           Set FindRow = .Range("C:C").Find(What:="rngEnd", LookIn:=xlValues)
       End With
       getRange = FindRow.Row - 1
   End If
   End IF

   End Function

Here is my Eval fuction

   Function Eval(Ref As String)
       Application.Volatile
   Eval = Evaluate(Ref)
   End Function

nonBlank fucntion

   Function nonBlank(r As Range) As Long 'Counts and returns the number of non blank cells found in given range.
   Application.Volatile
   nonBlank = r.Cells.Count - WorksheetFunction.CountBlank(r)
   End Function

Since this is a user defined function (UDF), you don't have to use `Formula`. All of your logic can simply be in VBA to determine the result to pass back to the cell. — PeterT, Sep 27 '16 at 16:14
Is it because of the Formula approach that I am having this issue? — Scott Peabody, Sep 27 '16 at 16:20
Also can you post the formula by which you call the UDF that is changing? — Scott Craner, Sep 27 '16 at 16:22
I updated the question to include the other code. Basically what I am doing is evaluating the formula and passing the result back to the cell. — Scott Peabody, Sep 27 '16 at 16:34
You need to put the sheet name in the formula: `"=IF(SUM('" & sheetName & "'!D" & rngSt & ":D" & rngEnd & ")...` Do that for every range in the formula. — Scott Craner, Sep 27 '16 at 16:45
Gotcha, I will give it ago and let you know what happens shortly. — Scott Peabody, Sep 27 '16 at 16:46
I attempted it and had no luck unfortunately. I get a "#VALUE!" — Scott Peabody, Sep 27 '16 at 17:15

Zack Barresse · Answer 1 · 2016-09-27T22:38:49.923

In your case, the function is returning exactly what you tell it to. Your UDF has no specification of worksheet anywhere. What you see on the first sheet, after the second sheet calculates, is the returned value of the function, since it calculated on the second sheet. It's a little confusing, so let me try to break it down another way.

You enter a formula with UDF on Sheet1
UDF calculates on Sheet1, with Sheet1 ranges
You navigate to Sheet2 and recalculate UDF entered there
UDF calculates on Sheet2, with Sheet2 ranges
Concurrently on Sheet1 the UDF also calculates, with Sheet2 ranges (this is why you get the same results)

Since calculation doesn't happen when you change sheets, you still see the results calculated correctly.

Bottom line (TL;DR): Your UDF is poorly written.

To help with an answer to your question, please post your getRange function as Scott asked, as well as an example of how you are calling the UDF. Edit: I see you posted the getRange function, but it's not complete. I think you're missing an End If statement perhaps. Also, your getScore function doesn't compile because you have an extra ">" character in there. Not sure what it's doing in there.

Formula = "=IF(SUM('" & sheetTitle & "'D" & rngSt & ":D" & rngEnd & ")= nonBlank('" & sheetTitle & "'D" & rngSt & ":D" & rngEnd & "),"
Formula = Formula & "IF(SUM('" & sheetTitle & "'F" & rngSt & ":F" & rngEnd & ")= nonBlank('" & sheetTitle & "'F" & rngSt & ":F" & rngEnd & "),"
Formula = Formula & "IF(SUM('" & sheetTitle & "'H" & rngSt & ":H" & rngEnd & ")= nonBlank('" & sheetTitle & "'H" & rngSt & ":H" & rngEnd & "),"
Formula = Formula & "IF(SUM('" & sheetTitle & "'J" & rngSt & ":J" & rngEnd & ")= nonBlank('" & sheetTitle & "'J" & rngSt & ":J" & rngEnd & "),"
Formula = Formula & "IF(SUM('" & sheetTitle & "'L" & rngSt & ":L" & rngEnd & ")= nonBlank('" & sheetTitle & "'L" & rngSt & ":L" & rngEnd & "),5,4),3),2),1), 0)"

Please note this is the quick fix. I wouldn't write a UDF this way. But we would need much more detail if we delve into that.

EDIT: If I understood what you need, this is a much shorter version and should fix the issue you're seeing...

Function Score( _
    ByVal Section As Long, _
    ByVal Anchor As Range _
    ) As Long

    Dim CheckRange As Range

    Application.Volatile True
    Set CheckRange = Anchor.Parent.Range("C9", Anchor.Parent.Cells(Anchor.Parent.Rows.Count, "C").End(xlUp))

    Score = Abs(CLng(WorksheetFunction.CountA(CheckRange.Offset(0, 1)) = CheckRange.Cells.Count) + _
                CLng(WorksheetFunction.CountA(CheckRange.Offset(0, 3)) = CheckRange.Cells.Count) + _
                CLng(WorksheetFunction.CountA(CheckRange.Offset(0, 5)) = CheckRange.Cells.Count) + _
                CLng(WorksheetFunction.CountA(CheckRange.Offset(0, 7)) = CheckRange.Cells.Count) + _
                CLng(WorksheetFunction.CountA(CheckRange.Offset(0, 9)) = CheckRange.Cells.Count))

End Function

You would then call these from any cell like this...

=Score(1,A1)
=Score(1,Sheet2!A1)
=Score(1,'Some other sheet'!A1)

I'm not even sure what the 'Section' variable is for. There isn't much explanation here.

Thanks, Zack Barresse

I posted it in an edit above. I appreciate the honestly, I am still learning. I thought that because I specify the sheet in the function it would keep the context of what ever sheet it was passed. — Scott Peabody, Sep 27 '16 at 16:42
It's a good thought, but the range passed to be evaluated doesn't get the sheet name, so it assumes the active sheet. This is the crux. Also, you don't define what "nonBlank" is. I'll edit the solution to include what needs to happen. — Zack Barresse, Sep 27 '16 at 16:50
Ah, right. nonBlank counts the number of cells that have information in them. — Scott Peabody, Sep 27 '16 at 17:01
I have fixed my post and did attempt the quick fix but didn't have any luck with it. I get a "#VALUE!" error. — Scott Peabody, Sep 27 '16 at 17:13
Why does your 'getRange' function look for "rngEnd" in column C of the specified worksheet? Would it not want to find the last row with any data in it? — Zack Barresse, Sep 27 '16 at 22:25
Due to how the column is set up I cannot, as there are gaps in the range. this goes for all of them. With that said, the range is still the same for all of them. If more rows are added to it, I want the range to be able to be flexible and automatic. — Scott Peabody, Sep 29 '16 at 23:39
Each sheet varies too. Also with different sections I can get my relative ranges for each section of a sheet. My rngEnd is just my way of knowing where each section ends regardless of data. The user cannot read it as it is formatted to be invisible. I have also created buttons that add rows for the user if they need to add more to it. I have gotten the code to work entirely now, except that I cannot evaluate the formula that it creates with a function so I am in to process of doing it differently. — Scott Peabody, Sep 29 '16 at 23:39
I will try and implement your suggestion next week. I have had a priory change as of yesterday so I will be away from this problem till then! I appreciate the feedback and will let you know my results then. — Scott Peabody, Sep 29 '16 at 23:46
The function I gave you should work. Doesn't matter if there are gaps, it looks from the bottom of the sheet upwards until it finds any cell with data in it and uses that as the last cell. Ensure you set the anchor cell to the desired sheet and it won't matter what sheet you're on. You can also use Application.Caller to get the range calling the UDF, then use the Parent property of it to get the Sheet. — Zack Barresse, Sep 30 '16 at 23:41

My user defined VBA function is updating all cells using it with the same thing, I don't want it too

1 Answers1