Volatile Functions causing issues

**penfold1992** · 09-24-2014, 05:48 AM

Hello all,

I have a user defined function that essentially does a bunch of "count if's" and returns and answer.
When I alt:tab between spreadsheets I have noticed all the values that were viewable changed to display "#Value!".

Nothing within the spreadsheet has changed and vba code also hasnt changed.

so... to try to fix this I created a refresh macro "Refresh" which is just a simple...

Sub Refresh()
Dim r As Integer

r = 1

Do While r < 80
    Range("A" & r).Calculate
    r = r + 5
Loop

End Sub

and this fixed it!.... once. If i want to get this to run a second time, I have to modify the VBA (even if it means just deleting the 5 and adding it back in....)

Is there a refresh button that I can use that would actually correct this every time?
Is there a way I can prevent the function being so volatile and easy to turn to "#Value!"?

Kind Regards,
Penfold

**Kaper** · 09-24-2014, 05:56 AM

How about short macro in Thisworksheet code:

Private Sub Workbook_Activate()
Application.Calculate 'or you can recalculate only specific sheet
End Sub

**AlphaFrog** · 09-24-2014, 06:50 AM

Try adding...
Application.Volatile
...to the top of your UDF procedure.

Otherwise, can you post the UDF code here?

Application.Volatile Method

**penfold1992** · 09-24-2014, 07:22 AM

I have two UDF's both cause the problem...

Function Plan_Target(MCA_Value As String, CalcType As String, Optional Given_Month As String, Optional Given_Year As Integer) As Single
Application.Volatile
Dim RAW As ListObject
    Set RAW = Sheets("Data").ListObjects("Table_awur_1")

If CalcType = "T" Then
    'Target
        Dim Prio(6) As Integer, x As Integer  ' Create an array to store numbers
        Prio(0) = 0         ' Not used, we do not have a priority 0

        'Count the number of priority 1 - 5
        For x = 1 To 5
            Prio(x) = WorksheetFunction.CountIfs(Range("Planning_Sht[[#Data],[MCA]]"), MCA_Value, Range("Planning_Sht[[#Data],[Priority]]"), x)
        Next x

        'Calculate and Return Target
        Plan_Target = ((Prio(1) * 9) + (Prio(2) * 6) + (Prio(3) * 3) + _
                        (Prio(4) * 2) + Prio(5)) / 6

Else
    'Ensuring all criteria are met.
    If Given_Month = "" Or IsNull(Given_Year) = True Then
        Plan_Target = CVErr(xlErrValue)
    End If
    
    Dim This_Month As Date, Next_Month As Date
        This_Month = Format(Given_Month & "/1/" & Given_Year, "mm/dd/yyyy")
    If This_Month = "12/1/" & Given_Year Then
        Next_Month = "1/1/" & Given_Year + 1
    Else
        Next_Month = DateAdd("d", 1, This_Month)
    End If
    
    'Preparation for calculation
        If CalcType = "P" Then
            'Planned
            x = 7
        ElseIf CalcType = "A" Then
            'Achieved
            x = 9
        Else
            'Cause an error if Plan_Target does not have a calculation type.
            Plan_Target = CVErr(xlErrValue)
        End If
        
    'Calculation
        Plan_Target = WorksheetFunction.CountIfs( _
            RAW.ListColumns(3).Range, "NIEB", _
            RAW.ListColumns(4).Range, "RED", _
            RAW.ListColumns(5).Range, "=" & MCA_Value, _
            RAW.ListColumns(x).Range, ">=" & This_Month, _
            RAW.ListColumns(x).Range, "<" & Next_Month)
End If

End Function

and

Function PCM_Checker(SIG As String, WP As String, Given_Month As String, Given_Year As Integer, Optional MCA As String) As Integer
Application.Volatile
Dim RAW As ListObject
    Set RAW = Sheets("Data").ListObjects("Table_awur_1")
    
Dim This_Month As Date, Next_Month As Date
    This_Month = Format(Given_Month & "/1/" & Given_Year, "mm/dd/yyyy")
    
If This_Month = "12/1/" & Given_Year Then
    Next_Month = "1/1/" & Given_Year + 1
Else
    Next_Month = DateAdd("d", 1, This_Month)
End If

' If the SIG is "ACTIVE" then the MCA must be given
If SIG <> "* ACTIVE" Then
    PCM_Checker = WorksheetFunction.SumIfs(RAW.ListColumns(10).Range, _
            RAW.ListColumns(3).Range, "NIEB", _
            RAW.ListColumns(4).Range, "RED", _
            RAW.ListColumns(6).Range, SIG & " - " & WP, _
            RAW.ListColumns(7).Range, ">=" & This_Month, _
            RAW.ListColumns(7).Range, "<" & Next_Month)
ElseIf MCA <> "" Then
    PCM_Checker = WorksheetFunction.SumIfs(RAW.ListColumns(10).Range, _
            RAW.ListColumns(3).Range, "NIEB", _
            RAW.ListColumns(4).Range, "RED", _
            RAW.ListColumns(6).Range, SIG & " - " & WP, _
            RAW.ListColumns(5).Range, "=" & MCA, _
            RAW.ListColumns(7).Range, ">=" & This_Month, _
            RAW.ListColumns(7).Range, "<" & Next_Month)
Else
    PCM_Checker = CVErr(xlErrValue)
End If

End Function

**MrShorty** · 09-24-2014, 08:37 AM

Your first function is suffering from the same error discussed here: http://www.excelforum.com/excel-prog...ml#post3839103 You are using an unqualified reference to a range -- which usually means activesheet.range(...). When you step away from the sheet containing the UDF to another sheet, activesheet obviously changes, and the data in activesheet is likely incompatible with what the function wants -- causing the error.

As before, I would suggest that you pass all information needed by the UDF to the UDF through the argument list. This should alleviate the errors you are getting, and will it also avoid any need to make the function volatile, because Excel will be able to fit the function into its calculation tree.

**penfold1992** · 09-24-2014, 08:59 AM

is there any other way I can get around that? The range will always be constant to the workbook and macro.
Is it possible to do something like...

Dim RAW As ListObject
    Set RAW = ThisWorkbook.Sheets("Data").ListObjects("Table_owssvr_1")

would that work better or no different?
(I assume you are talking about the "ListObject" table.)

**MrShorty** · 09-24-2014, 10:30 AM

Originally Posted by penfold1992

is there any other way I can get around that? The range will always be constant to the workbook and macro.
Is it possible to do something like...

Dim RAW As ListObject
    Set RAW = ThisWorkbook.Sheets("Data").ListObjects("Table_owssvr_1")

would that work better or no different?
(I assume you are talking about the "ListObject" table.)

Yes it is possible and it is syntactically correct. Only you can know if the data and reference are truly static. I find that, when I have done this sort of thing in the past thinking that it would always be static, when I come back to it, I tend to wish that I had coded it as an argument rather than hard coding it into the body of the function. Maybe think of it as a useful "best practices rule" (for which there will occasionally be exceptions). Many of us on here believe that passing data through the argument list is the best way to do it -- if you think your situation warrants an exception to this "rule", then you can certainly do it the way you are proposing.

One way or another, it seems that the solution to your problem is to make sure that Excel/VBA cannot accidentally pull data from somewhere other than the intended range.

**penfold1992** · 10-21-2014, 07:58 AM

Originally Posted by MrShorty

One way or another, it seems that the solution to your problem is to make sure that Excel/VBA cannot accidentally pull data from somewhere other than the intended range.

unfortunately I am still unable to get this to work as I want it to...
I am returning to see if you can offer any further insight.

Current Code:

Function Plan_Target(SIG_Value As String, CalcType As String, Optional Given_Month As String, Optional Given_Year As Integer) As Single
Dim Dta_WS As Worksheet, RED_WS As Worksheet
    Set Dta_WS = ThisWorkbook.Sheets("Data")
    Set RED_WS = ThisWorkbook.Sheets("RED Planning")
    
Dim RAW As ListObject
    Set RAW = Dta_WS.ListObjects("Table_owssvr_1")

If CalcType = "T" Then
    'Target
        Dim Prio(6) As Integer, x As Integer  ' Create an array to store numbers
        Prio(0) = 0         ' Not used, we do not have a priority 0

        'Count the number of priority 1 - 5
        For x = 1 To 5
            Prio(x) = WorksheetFunction.CountIfs(RED_WS.Range("RED_Planning[[#Data],[SIG]]"), SIG_Value, RED_WS.Range("RED_Planning[[#Data],[Priority]]"), x)
        Next x

        'Calculate and Return Target
        Plan_Target = ((Prio(1) * 9) + (Prio(2) * 6) + (Prio(3) * 3) + _
                        (Prio(4) * 2) + Prio(5)) / 6

Else
    'Ensuring all criteria are met.
    If Given_Month = "" Or IsNull(Given_Year) = True Then
        Plan_Target = CVErr(xlErrValue)
    End If
    
    Dim This_Month As Date, Next_Month As Date
        This_Month = Format(Given_Month & "/1/" & Given_Year, "mm/dd/yyyy")
    If This_Month = "12/1/" & Given_Year Then
        Next_Month = "1/1/" & Given_Year + 1
    Else
        Next_Month = DateAdd("d", 1, This_Month)
    End If
    
    'Preparation for calculation
        If CalcType = "P" Then
            'Planned
            x = 7
        ElseIf CalcType = "A" Then
            'Achieved
            x = 9
        Else
            'Cause an error if Plan_Target does not have a calculation type.
            Plan_Target = CVErr(xlErrValue)
        End If
        
    'Calculation
        Plan_Target = WorksheetFunction.CountIfs( _
            RAW.ListColumns(3).Range, "RED", _
            RAW.ListColumns(4).Range, "NEIB", _
            RAW.ListColumns(5).Range, "=" & SIG_Value, _
            RAW.ListColumns(x).Range, ">=" & This_Month, _
            RAW.ListColumns(x).Range, "<" & Next_Month)
End If

The only area of issue I can potentially see is with the WorksheetFunction call.
Otherwise, I have no clue where the additional errors are coming from.

Kind Regards,
Penfold

**romperstomper** · 09-24-2014, 09:31 AM

Not just that - all the Range statements need qualifying too - like this one:

Range("Planning_Sht[[#Data],[MCA]]")

**penfold1992** · 09-29-2014, 10:17 AM

ok I am still getting this issue occassionally... I will repost my code.

Function Plan_Target(MCA_Value As String, CalcType As String, Optional Given_Month As String, Optional Given_Year As Integer) As Single
Dim Dta_WS As Worksheet, PCM_WS As Worksheet
    Set Dta_WS = ThisWorkbook.Sheets("Data")
    Set PCM_WS = ThisWorkbook.Sheets("PCM Planning")
    
Dim RAW As ListObject
    Set RAW = Dta_WS.ListObjects("Table_awur_1")

If CalcType = "T" Then
    'Target
        Dim Prio(6) As Integer, x As Integer  ' Create an array to store numbers
        Prio(0) = 0         ' Not used, we do not have a priority 0

        'Count the number of priority 1 - 5
        For x = 1 To 5
            Prio(x) = WorksheetFunction.CountIfs(PCM_WS.Range("PCM_Planning[[#Data],[MCA]]"), MCA_Value, PCM_WS.Range("PCM_Planning[[#Data],[Priority]]"), x)
        Next x

        'Calculate and Return Target
        Plan_Target = ((Prio(1) * 9) + (Prio(2) * 6) + (Prio(3) * 3) + _
                        (Prio(4) * 2) + Prio(5)) / 6

Else
    'Ensuring all criteria are met.
    If Given_Month = "" Or IsNull(Given_Year) = True Then
        Plan_Target = CVErr(xlErrValue)
    End If
    
    Dim This_Month As Date, Next_Month As Date
        This_Month = Format(Given_Month & "/1/" & Given_Year, "mm/dd/yyyy")
    If This_Month = "12/1/" & Given_Year Then
        Next_Month = "1/1/" & Given_Year + 1
    Else
        Next_Month = DateAdd("d", 1, This_Month)
    End If
    
    'Preparation for calculation
        If CalcType = "P" Then
            'Planned
            x = 7
        ElseIf CalcType = "A" Then
            'Achieved
            x = 9
        Else
            'Cause an error if Plan_Target does not have a calculation type.
            Plan_Target = CVErr(xlErrValue)
        End If
        
    'Calculation
        Plan_Target = WorksheetFunction.CountIfs( _
            RAW.ListColumns(3).Range, "NIEB", _
            RAW.ListColumns(4).Range, "RED", _
            RAW.ListColumns(5).Range, "=" & MCA_Value, _
            RAW.ListColumns(x).Range, ">=" & This_Month, _
            RAW.ListColumns(x).Range, "<" & Next_Month)
End If

End Function

Do I still not have alll the ranges covered?

**romperstomper** · 10-21-2014, 08:07 AM

I strongly suggest you go back to the original suggestion of passing the ranges to the function as arguments.

**MrShorty** · 10-21-2014, 10:40 AM

IMO, "Here's my code, it doesn't work" is one of the least useful "error statements" a debugger or an OP can give. What do you mean by "doesn't work"? Does it hang up? Does it give an error message? Does it run to completion, but the results are incorrect?

I don't offhand see anything syntactically wrong with the worksheetfunction.countifs() call, so if there is an error here, it is related to the data in those ranges. You will need to look at the data in those ranges and the parameters sent to the countif function to see if there is some "incompatibility" that is causing an error/incorrect result.

I know that one of the most used debugging strategies that I use is to set a breakpoint early in the function and step through the code one line at a time: http://www.cpearson.com/excel/DebuggingVBA.aspx If a statement errors, I will know which statement causes the error and can focus my debugging on that statement (through the locals window and various watches). If I am getting incorrect results, I can follow the values of the different variables to identify where in the calculation an unexpected result is occurring. It sounds like you have an added difficulty in that the error only occurs some of the time, which can mean looking at different contexts to understand when the errors are occurring.

Volatile Functions causing issues

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Volatile Functions causing issues

Re: Volatile Functions causing issues

Re: Volatile Functions causing issues

Re: Volatile Functions causing issues

Re: Volatile Functions causing issues

Re: Volatile Functions causing issues

Re: Volatile Functions causing issues

Re: Volatile Functions causing issues

Re: Volatile Functions causing issues

Re: Volatile Functions causing issues

Re: Volatile Functions causing issues

Re: Volatile Functions causing issues

Thread Information

Users Browsing this Thread

Similar Threads

Volatile Functions needed for Roulette

External Links Causing Issues

Volatile functions across books

Negative value causing issues

[SOLVED] If then statement causing value issues in Macros

Bookmarks

Bookmarks

Posting Permissions