Excel vba performing user defined function from another workbook even though it is not being called

Question

I have a subroutine that generates 6 unique random numbers within a certain range and then places them, in ascending order, in individual cells in the worksheet (yes, this is a lottery ticket generator). The code works perfectly fine, except for when I have my other workbook open which contains a few user defined functions. Whenever my code gets to the point where it is placing the values in the cells, it enters my UDF ToColletter() and performs it several times (which does nothing except bog my program down majorly). I do not understand why it is executing my UDF, seeing as I am not calling it from within my subroutine, and it is not being called from within my workbook at all. I tried setting the module with the UDF to Option Private Module, but it would still call it and execute it several times. Even making the function itself a private function did nothing. Can anyone explain to me why this is happening and how I can stop the UDF from being executed unnecessarily? I am working in Excel 2016. Here is my code for the random number generator:

Sub LotteryNumbers()
Dim Num1 As Integer
Dim Num2 As Integer
Dim Num3 As Integer
Dim Num4 As Integer
Dim Num5 As Integer
Dim PwrNum As Integer
Dim NumbersArr(5) As Integer
Application.ScreenUpdating = False

Range("A6").Activate

'Each loop generates one ticket containing 6 random numbers
'The '+1' is the winning numbers
For y = 1 To Range("NumTickets").Value + 1
    'Generate random numbers
    Num1 = WorksheetFunction.RandBetween(1, 70)
    Num2 = Num1
    Num3 = Num2
    Num4 = Num3
    Num5 = Num4
    PwrNum = WorksheetFunction.RandBetween(1, 25)
    'These loops prevent any of the numbers from equalling each other
    Do While ((Num2 = Num1) Or (Num2 = Num3) Or (Num2 = Num4) Or (Num2 = Num5))
        Num2 = WorksheetFunction.RandBetween(1, 70)
    Loop
    Do While ((Num3 = Num1) Or (Num3 = Num2) Or (Num3 = Num4) Or (Num3 = Num5))
        Num3 = WorksheetFunction.RandBetween(1, 70)
    Loop
    Do While ((Num4 = Num1) Or (Num4 = Num2) Or (Num4 = Num3) Or (Num4 = Num5))
        Num4 = WorksheetFunction.RandBetween(1, 70)
    Loop
    Do While (Num5 = Num4) Or (Num5 = Num3) Or (Num5 = Num2) Or (Num5 = Num1)
        Num5 = WorksheetFunction.RandBetween(1, 70)
    Loop

    'Place the numbers in an array so they can be easily sorted
    NumbersArr(1) = Num1
    NumbersArr(2) = Num2
    NumbersArr(3) = Num3
    NumbersArr(4) = Num4
    NumbersArr(5) = Num5

    'Sort the numbers in ascending order and place them in 6 individual cells
    For x = 1 To 5
        'This is the line that calls the ToCollet() function
        ActiveCell.Value = WorksheetFunction.Small(NumbersArr(), x + 1)
        ActiveCell.Offset(0, 1).Activate
    Next x
    ActiveCell.Value = PwrNum
    'Finish the loop at the beginning of the next row
    ActiveCell.Offset(1, -5).Activate
Next y

Application.ScreenUpdating = True

End Sub

Here is my code for the UDF ToCollet(), which is in a separate workbook and in a private module:

Function ToColletter(Collet)
   ToColletter = Split(Cells(1, Collet).Address, "$")(1)
End Function

Best guess is that there is some volatile worksheet function (indirect, offset, today, now, address, etc) that is precedent, dependent or directly involved with collet. I'm unsure but it could be WorksheetFunction.RandBetween which is also volatile. — user4039065
Put a breakpoint on the line in the ToColletter function. Once it is hit, check the call stack (Ctrl + L). If there is only one line, you can be fairly sure it is called from a cell. If there are more lines, you have found the code line you were looking for. — Sam
(btw, there are easier ways to generate 6 'random' unique numbers) — user4039065
Well, it's not your use of WorksheetFunction.RandBetween so look at collet. — user4039065
Thanks for the feedback! I did as @Sam suggested and placed a breakpoint in the ToColletter function and checked the call stack from there. It was pointing to ActiveCell.Value = WorksheetFunction.Small(NumbersArr(), x + 1) . I did as recommended by Josh Eller in his answer and placed the Application.Calculation = xlCalculationManual at the beginning and it no longer enters the the UDF ToColleter() until the very end when I set it back to xlCalculationAutomatic. So that seemed to do the trick! — Nathan Wulf

Josh Eller Josh Eller · Accepted Answer · 2018-10-24T19:48:33

Your ToColletter function is what's called a volatile function. This means it'll be recalculated every time your workbook is recalculated, regardless of whether the input values have changed. It's being called because your first function is updating values in a workbook, causing Excel to recalculate.

This is because you're not directly giving your function values; instead, the function needs to go lookup a value from the worksheet, so the function can't know whether it needs to be recalculated until it actually goes and looks up that value.

What you can do to fix it is get rid of the cells references in your function, and make the output depend only on the inputs. Alternatively, you can set Application.Calculation = xlCalculationManual prior to running your main sub, and set it back to Application.Calculation = xlCalculationAutomatic after it's finished. This will prevent Excel from automatically recalculating while your sub is running.

Excel vba performing user defined function from another workbook even though it is not being called

1 Answers