Solved: Function and loop triggered when input hasn't changed

[vanhunk]

I have the function below in a module in a workbook. The function is only used on one sheet in this workbook, nowhere else.

However, if this workbook happens to be open and I am stepping through the macro in another workbook, the moment any cell value changes, it goes to this function and seems to recalculate every single cell containing the function is the first workbook.

Is it at all possible to have the function only run when the cells calling it change? It is seriously annoying! You can’t step through any macro without having to switch off auto calculation.

Function findit(v As Variant, r As Range) As String
  'Will find either numbers or text.  Use like:
  '   =findit("happy",A1:C100)
   
  Dim rr As Range
   
  findit = ""
  For Each rr In r
      If rr.Value = v Then
          findit = rr.Address
      Exit Function
      End If
  Next
  End Function

[snb]

Why using a function ?

[VBA]Sub M_snb()
On Error Resume Next
MsgBox Sheets(1).Cells.Find("hhh", , xlValues, 1).Address
End Sub[/VBA]

[Paul_Hossler]

1. I assume that you're putting the address into aWS cell

2. if you call it like =FindIt(12345, "A:C") it would probablt want to test all 1,000,000 cells in each 3 columns

[VBA]
Option Explicit
Function findit(v As Variant, r As Range) As String

Dim rr As Range, r1 As Range


findit = ""

Set r1 = Nothing
On Error Resume Next
Set r1 = Intersect(r, r.Parent.UsedRange)
On Error GoTo 0

If r1 Is Nothing Then Exit Function

For Each rr In r1.Cells
If rr.Value = v Then
findit = rr.Address
Exit Function
End If
Next
End Function
[/VBA]

3. If the cells in input range to findit is re-calced, it would probably try to re-calc findit also

without sample WB, little hard to say

Paul

[vanhunk]

Why using a function ?

[vba]Sub M_snb()
On Error Resume Next
MsgBox Sheets(1).Cells.Find("hhh", , xlValues, 1).Address
End Sub[/vba]

Thanks snb/Paul,

I am using it in formulas, see attached spreadsheet.

Regards,
vanhunk

[snb]

In H23

=SUM(OFFSET(H24;;;MATCH("Month:";A25:A100;0))) 

[vanhunk]

In H23

=SUM(OFFSET(H24;;;MATCH("Month:";A25:A100;0))) 

The formula works great, I had to change the ";" to "," though, thanks.

=SUM(OFFSET(H24,,,MATCH("Month:",A25:A100,0))) 

[vanhunk]

To get rid of the function all together is a great way of solving the problem, if that wasn't possible I would still like to have an answer to the original question/challenge for future use. What I did discover to work, although it can be a shlepp, is to add the code

Worksheets("Data store").EnableCalculation = False

to strategic positions and then again add the code

Worksheets("Data store").EnableCalculation = True

to where it is required.

[snb]

But disabling autocalculation is amputating the core function of a spreadsheet.

I don't think it's very complicated to get rid of the whole UDF you introduced.

[vanhunk]

But disabling autocalculation is amputating the core function of a spreadsheet.

I don't think it's very complicated to get rid of the whole UDF you introduced.

Hi snb,
I agree with you wholeheartedly, that is why I don't like to do disable the autocalculation for the sheet where the function formulas reside, and yes form what you showed me it would probably not be too difficult to get rid of the UDF all together.

What I was hoping for is that there is a way to prevent the UDF from "running" if changes are made to the workbook that doesn't affect the input and results of the formulas making use of the UDF.

Regards,
vanhunk

[p45cal]

While playing with this I found that once you find yourself in the function's code, if you press Ctrl+Shift+F8 (Step Out in the Debug dropdown) the function continues to run, may be called umpteen times by the sheet, but the code pauses at the line after whatever caused the recalculation.

Another thing I have done in the past, is to rename the function in the code, so instead of Function findit(…, I change it to Function zzzfindit(…. Obviously that function never gets called now and it causes a NAME? error on the sheet concerned but that's resolved when you change the name back. It's hard work if you have lots of UDFs, but if you've been consistent with how you renamed the function you can do a search and replace to re-establish the original names.

Another thing you can do, if you need the function to recalculate while you're stepping through, is to put a break point in the code at the line directly after the one that causes the recalculation, then when you find yourself stepping through the function's code, press F5 to move on to that break point.

[vanhunk]

Hi p45cal,

I have tested your tips and it is really useful,

Thanks,
vanhunk