UDF - Identify if it's a recalculation or first execution

Question

I want to program a UDF to query a access db. I'm wondering if it's possible to have 2 different Workflows, depending whether it's a first execution or only a recalculation of the UDF.
Ideally I would have a UDF that you can feed the primary key of the db and the UDF presents an overview of possible values of the access db table. If it's a recalculation I don't want to have a userform popup again. Is this possible at all? Can somebody point me in the right direction?
Thx!

Edit An attempt to show some (dummy) code:

public function key_from_table(primarykey as string) as string
' Read-out column names from Access table for userform
' Trigger userform with possible column names and let user choose
' readout Chosen column names
key_from_table = Call get_from_db(Primary_key, column_names)
end function

Function get_from_db(Primarykey as string, column_names as string) as string
'call Access db and readout result
end Function

If a recalculation is triggered the userform popup comes up again
I'm still new to Excel vba - pls tell me if this is rather stupid :)

Use a global variable to save the primary key. https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba — kaza, Sep 14 '17 at 14:06
@bulbus that only works if I always want the same value from the table — dv3, Sep 14 '17 at 14:16
@Zac My first call is I get all column names from the table, then trigger the form with the possible values - not sure how else to Show this — dv3, Sep 14 '17 at 14:17
A bit confused about the criterion to decide if it is first call or 1+n call. Is `primaryKey` already chosen is one or are all `possible-key-List` is populated? Both of them could be solved by a global variable? — kaza, Sep 14 '17 at 14:21
In case your criterion is `possible-key-List` already populated. You've to show UDF to select `primaryKey` anyways right? — kaza, Sep 14 '17 at 14:23
@bulbus I've added some Explanation - does this clear it up? — dv3, Sep 14 '17 at 14:31
Declare a global Boolean variable (i.e. `bFormTriggered`) in your module where UDF is. When you first trigger the form in your `key_from_table` UDF, set the value of this variable to `TRUE` after you have triggered the form. Just before you trigger the form, check the value of this variable. Only trigger the form if the value of this variable is not `TRUE` — Zac, Sep 14 '17 at 14:41
@Zac that works if I only want to call 1 column. If I need a second column I need to userform to popup again.... — dv3, Sep 14 '17 at 14:43
So basically if a recalculation happens or if I drag the UDF down I don't want the userform. If the user Needs a different column from the database - it Needs to come up again — dv3, Sep 14 '17 at 14:45
I think I understand the issue now. In that case, similar approach: declares a global dictionary variable. Just before you trigger the form. check if dictionary already has the column name. If it does, don't trigger the form. If it doesn't, trigger the form and add the column name to dictionary once form is closed — Zac, Sep 14 '17 at 14:45
oh that sounds smart @Zac what are the drawbacks if I don't set the dict to nothing? — dv3, Sep 14 '17 at 14:49
In your case, you shouldn't set the dictionary to nothing because it will lose the value and when you come around the next time. it will trigger the form again. You are not talking about a huge dictionary so it shouldn't impact the performance or memory usage on your PC. Drawback is if you don't clear your objects, you could have memory leaks. To avoid this (again don't think it should affect you), you could set the variable to nothing in `Workbook_BeforeClose` — Zac, Sep 14 '17 at 14:57
oh and it will be impossible to change the selection....@Zac — dv3, Sep 14 '17 at 14:57
Not sure what you mean by `it will be impossible to change the selection` — Zac, Sep 14 '17 at 14:59
@Zac disregard my last cmmt - I meant that if somebody wants to Change the selection, there Needs to be a method to delete the dict-entry but I solved it. The solution is actually quite neat. If you want the stack-points, please submit something as an answer and I'll accept. Thanks for your help — dv3, Sep 15 '17 at 07:01

score 1 · Accepted Answer · answered Sep 15 '17 at 08:43

Declares a global dictionary variable. Just before you trigger the form, check if dictionary already has the column name. If it does, don't trigger the form. If it doesn't, trigger the form and add the column name to dictionary once form is closed. You can clear the variable in Workbook_BeforeClose just to be clean

tigeravatar · Answer 2 · 2017-09-14T15:42:23.490

Something like this should work for you:

Public pub_sRecalcCheck As String

Public Function MyTest() As Boolean

    Dim bReCalc As Boolean

    If InStr(1, " " & pub_sRecalcCheck & " ", " " & Application.Caller.Address(External:=True) & " ", vbTextCompare) = 0 Then
        'This is a brand new calculation
        'Add this cell to the public variable storing where calculations for this UDF have occurred
        bReCalc = False
        pub_sRecalcCheck = WorksheetFunction.Trim(Replace(pub_sRecalcCheck, " " & rCell.Address(External:=True) & " ", " "))

        ''''''''''''''''''''''''''''''''''''''''''''
        '                                          '
        '    Your code here for new calculation    '
        '                                          '
        ''''''''''''''''''''''''''''''''''''''''''''

    Else
        'This is a recalculation
        bReCalc = True

        ''''''''''''''''''''''''''''''''''''''''''
        '                                        '
        '    Your code here for recalcuations    '
        '                                        '
        ''''''''''''''''''''''''''''''''''''''''''

    End If

    MyTest = bReCalc

End Function

EDIT: And just in case the formula gets deleted from the cell, use this in the ThisWorkbook module to clear that cell's address from the RecalcCheck public string variable so that if a new formula is put there it is treated as a new calculation:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim rCell As Range

    For Each rCell In Target.Cells
        'Check if cell has been deleted/cleared
        If IsEmpty(rCell) Then
            'Found deleted cell, check if it is stored for the recalc checks
            If InStr(1, " " & pub_sRecalcCheck & " ", " " & rCell.Address(External:=True) & " ", vbTextCompare) > 0 Then
                'It is stored, remove it so that if formula is put back it is treated as a new calculation
                pub_sRecalcCheck = WorksheetFunction.Trim(Replace(pub_sRecalcCheck, " " & rCell.Address(External:=True) & " ", " "))
            End If
        End If

    Next rCell

End Sub

UDF - Identify if it's a recalculation or first execution

2 Answers2