Excel formula calculating once then deleting

Question

I have an excel formula:

=SplitKey(GetSysCd(INDEX([ReportValue],MATCH("mtr_make_model",[FieldName],0)),INDEX([ListName],MATCH("mtr_make_model",[FieldName],0))), 0)

which is running a few subroutines in VBA, but mainly matching values and inserting those values into a cell. When it finds a value for "mtr_make_model" it runs and matches the values inside a sys codes table. The issue I am having is that it is calculating once and then it removes the formula and now has solely the value... In the event that I go to the mtr_make_model field and change the value, the formula does not recalculate. Has anyone heard of this happening? Is this due to something in the VBA code? How do I make that formula stay put and if certain values change, the formula recalculates?

Thanks in advance.

Here are the two functions:

    Public Function GetSysCd(ByVal name As String, sysCdTableName As String) As String
    Dim r As Integer
    Dim sysCdTable As Range
    Dim nameList As Variant
    Dim sysCd As String

On Error GoTo GetSysCd_Error

    Set sysCdTable = Worksheets("sys_cd").Range(sysCdTableName)

    nameList = WorksheetFunction.Index(sysCdTable, 0, 2)
    r = WorksheetFunction.Match(name, nameList, 0)
    sysCd = WorksheetFunction.Index(sysCdTable, r, 1)

    GetOutOfHere:
    On Error GoTo 0
        GetSysCd = sysCd
        Exit Function

    GetSysCd_Error:
        sysCd = ""
        GoTo GetOutOfHere

    End Function


Public Function SplitKey(s As String, v As Integer)
Dim aString As Variant
Dim r As Integer

If Len(s) > 2 Then
    aString = Split(s, "_")

    If v = 0 Or v = 1 Then
        SplitKey = aString(v)
    Else
        SplitKey = aString(0)
    End If

Else
    SplitKey = ""
End If

End Function

I don't think the functions are relevant at this point, but rather just a matter of the function not recalculating when a variable in the formula changes...

Answer 1

The problem could be that Excel only recalculates functions when one of their arguments changes, and your GetSysCd function is referring to a range that is not in its argument list

Set sysCdTable = Worksheets("sys_cd").Range(sysCdTableName)

where sysCdTableName is just a string rather than a reference.