Custom Funtions

[jonc31]

I am really confused at this point. I created a custom function in vba that goes to another tab in the same workbook and grabs a correspoding number based off the statements in the function. The thing is when I intially enter the formula it is grabbing the correct data but if I update the tab the next day the formula is not updating with the new value.

How do you get custom functions to continually recalculate?

 

[MrKowz]

Can you please post the code for the UDF (User Defined Function) you are using?

 

[jonc31]

Function GetLocalBalancePru(TabName As String, AcctN As String, Curr As String) As Double
Dim lngLastRow As Long
Dim intCell As Integer
With ThisWorkbook.Worksheets(TabName)
lngLastRow = .Range("A1").End(xlDown).Row
intCell = 2
Do Until intCell > lngLastRow
If .Range("B" & intCell).Value = AcctN Then
If .Range("C" & intCell).Value = "USD" Then
Exit Do
ElseIf .Range("C" & intCell).Value = Curr Then
GetLocalBalancePru = .Range("D" & intCell).Value
Exit Do
End If
End If
intCell = intCell + 1
Loop
End With

End Function

 

[Sektor]

Add to the first line of code:

Application.Volatile

 

[MrKowz]

The problem is that there is nothing in the function that is being changed. A workaround would be to add Application.Volatile to the function. This way it recalculates anytime there is any calculations done to the worksheet:

Function GetLocalBalancePru(TabName As String, AcctN As String, Curr As String) As Double
Dim lngLastRow As Long
Dim intCell As Integer
[B][COLOR=red]Application.Volatile
[/COLOR][/B]With ThisWorkbook.Worksheets(TabName)
    lngLastRow = .Range("A1").End(xlDown).Row
    intCell = 2
    Do Until intCell > lngLastRow
        If .Range("B" & intCell).Value = AcctN Then
            If .Range("C" & intCell).Value = "USD" Then
                Exit Do
            ElseIf .Range("C" & intCell).Value = Curr Then
                GetLocalBalancePru = .Range("D" & intCell).Value
                Exit Do
            End If
        End If
        intCell = intCell + 1
    Loop
End With
End Function

However, I would not recommend this. It looks like you are using this function to effectively do a dual-condition lookup. Try, instead, using an array-entered INDEX/MATCH function:

=INDEX(TabName!D:D,MATCH(1,IF(TabName!B:B=AcctN,IF(TabName!C:C=Curr,1,"")),0))

Confirm entry with CTRL+SHIFT+ENTER (Not just enter) to get {brackets} around the formula. Note that TabName, AcctN, and Curr are variables you need to explicitly define in the formula.

 

[jonc31]

Before the function name or after?

 

[Sektor]

After.

 

[jonc31]

The problem is that there is nothing in the function that is being changed. A workaround would be to add Application.Volatile to the function. This way it recalculates anytime there is any calculations done to the worksheet:

Function GetLocalBalancePru(TabName As String, AcctN As String, Curr As String) As Double
Dim lngLastRow As Long
Dim intCell As Integer
[B][COLOR=red]Application.Volatile[/COLOR][/B]
With ThisWorkbook.Worksheets(TabName)
    lngLastRow = .Range("A1").End(xlDown).Row
    intCell = 2
    Do Until intCell > lngLastRow
        If .Range("B" & intCell).Value = AcctN Then
            If .Range("C" & intCell).Value = "USD" Then
                Exit Do
            ElseIf .Range("C" & intCell).Value = Curr Then
                GetLocalBalancePru = .Range("D" & intCell).Value
                Exit Do
            End If
        End If
        intCell = intCell + 1
    Loop
End With
End Function

However, I would not recommend this. It looks like you are using this function to effectively do a dual-condition lookup. Try, instead, using an array-entered INDEX/MATCH function:

=INDEX(TabName!D:D,MATCH(1,IF(TabName!B:B=AcctN,IF(TabName!C:C=Curr,1,"")),0))

Confirm entry with CTRL+SHIFT+ENTER (Not just enter) to get {brackets} around the formula. Note that TabName, AcctN, and Curr are variables you need to explicitly define in the formula.

Where would I put that index match function in excel or vba?

 

[MrKowz]

It would be in Excel, in place of the VBA function, and I must make a correction. Array entered formulas cannot have entire column references. Try:

=INDEX(TabName!D1:D1000,MATCH(1,IF(TabName!B1:B1000=AcctN,IF(TabName!C1:C1000=Curr,1,"")),0))

If the last row of your data changes often, we can create a few named ranges to accomodate for this.

 

[jasonb75]

However, I would not recommend this. It looks like you are using this function to effectively do a dual-condition lookup. Try, instead, using an array-entered INDEX/MATCH function:

=INDEX(TabName!D:D,MATCH(1,IF(TabName!B:B=AcctN,IF(TabName!C:C=Curr,1,"")),0))

Confirm entry with CTRL+SHIFT+ENTER (Not just enter) to get {brackets} around the formula. Note that TabName, AcctN, and Curr are variables you need to explicitly define in the formula.

MrK, Looking at the UDF, Tabname!D:D would need to be INDIRECT("'"&Tabname&"'!D:D") or similar, likewise for the other instances of Tabname, defeating the object of trying to make a non-volatile formula.