excel - Combine new dynamic array features of excel with VBA

Question

I tried to work a bit more with dynamic arrays in excel in combination with vba. My problem is that I cant return a table-column with vba. Here a minimal example of what I want to do:

I have two Tables TabFeb and TabMar (see image below). Each of them has a column costs which I want to sum up individually. The results shall be put into a new Table. This can be easily done in excel with =SUM(TabFeb[Costs]) and =SUM(TabMar[Costs]), respectively. My idea is now to write a VBA function which takes a string as input, in this example it will be the month, and returns the table acording to the input. After that it will be summed up and the result is given in a cell. I tried the following:

Function Selectmon(mon As String) As Range
    If mon = "Feb" Then
        Set Selectmon = Worksheets("Sheet1").ListObjects("TabFeb").ListColumns("Costs").DataBodyRange
    ElseIf mon = "Mar" Then
        Set Selectmon = Worksheets("Sheet1").ListObjects("TabMar").ListColumns("Costs").DataBodyRange
    End If
End Function

The problem of this idea is that this function just copy the table data. Hence, if I would change the input table data the sum would not change. One has to recalculate every cell by hand. Somehow I need VBA to return TabFeb[Costs] for the input "Feb". Does anyone have an idea how this can be done?

Example

标签： excelvbadynamic-arrays

Answer 1

It's really just a one-liner (unless you want to do some in-function error checking)

Function Selectmon(mon As String) As Range
    Set Selectmon = Range("Tab" & mon & "[Costs]")
End Function

As implied by @ceci, this formula will not update with changes in the table. Depending on other particulars of your worksheet, you can have it update either by

• embedding it in a worksheet change event code;
• or by adding the line Application.Volatile to the function itself.

The latter method will force a recalculation when anything changes on the worksheet that might cause a recalculation.

The first method can limit the recalculation only when there has been a change in the data, but has other limitations.

One of the limitations of the Worksheet Change method is that it will only work on the relevant worksheet.

If you use the Workbook sheet change method, you won't have that limitation.

In either event you can limit your code to run only when the table has changed.

Here is one generalized method:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim LOS As ListObjects
    Dim LO As ListObject
    
Set LOS = Sh.ListObjects
For Each LO In LOS

    'could select relevant tables here
    'Could also select relevant worksheets, if you like
    'for example
    Select Case LO.Name
        Case "TabFeb", "TabMar", "TabApr"
            If Not Intersect(LO.DataBodyRange, Target) Is Nothing Then
                Application.EnableEvents = False
                Application.Calculate
            End If
        End Select
Next LO

Application.EnableEvents = True

End Sub

And there is other code you could use to find the relevant formula and just update that formula -- probably not worth the effort.