Statically Set Table Column on Table That Resizes

[MikeMcCollister]

I have a budget spreadsheet that has a table in it. Over the years, I just kept the table to 999 rows. This was more rows that I ever needed and things worked well. In some of my VBA functions, I would statically set the columns for items that I would look for. As they are static, I don't have to do a set every time there is a recalculation. By using static on the ranges, I got significant performance improvement (i.e. a factor of 2 to 4). Here is a portion of what I have been doing:

Private Const gMainCheckingWorksheet As String = "Main Checking Sheet"
Private Const gMainCheckingColumnCategory As String = "MainChecking[Category]"
Private Const gMainCheckingColumnFilledDate As String = "MainChecking[Filled Date]"

Function BudgetLineItemDate(category As String) As Variant
    Static columnCategory As Range
    Static columnFilledDate As Range

    ' bunch of stuff removed

    With Application.ThisWorkbook.Worksheets(gMainCheckingWorksheet)
        If (columnCategory Is Nothing) Then
            Set columnCategory = .Range(gMainCheckingColumnCategory)
        End If
        If (columnFilledDate Is Nothing) Then
            Set columnFilledDate = .Range(gMainCheckingColumnFilledDate)
        End If
    End With

    ' bunch of stuff removed
End Function

I started to mess around with the table in the budget spreadsheet and I decided not to make the table 999 rows but make it as big as needs to be. That is, I will add a row whenever on is needed. However, this messes up my static columns. If the columns are statically defined and I add a row to the table, the range does not change even though I am using the name of the table and the column name (i.e. "MainChecking[Category]"). It appears to set the range to the number of rows at the time it is set. If I close the spreadsheet and open it again, then it works for this time.

Is there any way to specify a range of a table column and have it work the way that I want?

I know that I can probably revert back to making the table 999 rows but I don't know if I want to do that .

Thanks,

Mike

 

[jasonb75]

If you're using your udf as a worksheet formula then you either need to set it as volatile (extremely inefficient) or add a second argument to the function which would need to refer to a table column in order to trigger recalculation.

Function BudgetLineItemDate(category As String, rng As Range) As Variant

If you're calling the function from a vba procedure then it should work as it is.

 

[MikeMcCollister]

Jason,

Thanks. I am using a UDF. I'll look into volatile or specifying the range. I'm also looking at the table size and if it changes then update the static variables.

Regards,

Mike

 

[jasonb75]

To elaborate a bit more on my earlier reply. Hopefully this makes sense, I've added an example at the end using 2 simple formulas to aid with explanation.

With the way that you have written your function it would only recalculate if there was a change to the category (that is assuming that the string is in a cell and not entered directly into the formula).

For your function to work accurately with the existing code you would need to make it volatile which would be counter intuitive given that you are looking for methods that permit performance improvement. A volatile function is recalculated whenever certain actions are performed, even if that action should have no effect on the function. Naturally, the more complex the formula, the greater the performance degradation.

For a non volatile function used as a formula to recalculate there needs to be a change to one of the arguments (category As String is your only argument).
By using a second argument as a range it will detect the change, whether that is a new row added or an alteration to the data in that range, although this would not detect changes to other ranges used within the function.

For example, if you used my suggestion of Function BudgetLineItemDate(category As String, rng As Range) As Variant and then changed the formula to something like =BudgetLineItemDate("Cat1",MainChecking[Category]) any new rows added to the table would be identified and the formula would recalculate.
It would also recalculate if any changes were made to existing data within the [Category] column but not for any changes made to the [Filled Date] column because it is not set as a precedent in the function arguments.

The same would apply to any other range references that might be in the sections that you removed.

Simple formula example, =OFFSET(A1,1,2,1,1) is a volatile function that will retrieve the content of B3. =INDEX(A1:C5,2,3) is a non volatile function that will do exactly the same thing. Neither of these functions look at D4. If D4 is changed then a volatile function will recalculate, a non volatile function will not. If the first formula was not volatile then it would not detect a change in B3 as there is no reference to B3 in the formula itself. The non volatile formula would detect the change as B3 falls inside the range A1:C5.

Hope this is of use.

 

[MikeMcCollister]

Jason,

Thank you for the additional insight. My UDFs are already set to volatile. I purposely did not include the as part of the function call because some of my other UDFs are dependent on up to five ranges. As each month summary has tens of UDF calls, I opted for using the volatile and setting the ranges in the functions to make things cleaner.

Again, thanks for your insights.

Regards,

Mike