2

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

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

To apply this every time the table is updated you'll call it from within a worksheet change sub (e.g: Sub Worksheet_Change(ByVal Target As Range)) and e.g. If Not Intersect(Target, [Table1[ColumnA]]) Is Nothing Then
@ceci Good point. Or add Application.Volatile but that might be excessive
Thank you both ceci end Ron. Both ideas solve my problem. I think Application.Volatile would be very computationally intensive. Hence, it is not practical for larger excel-files. Do you know how inbuild excel functions work? More like the first or second way?
@Diralo It is complicated. Built-in Excel functions (and UDF's for that matter), will recalculate when there is a change in the precedent cells. Some built-in functions are Volatile (OFFSET, INDIRECT) and will recalculate with any recalculation (and, indeed, can slow down large sheets). Your UDF, in construction, is more like INDIRECT, hence the need for Application.Volatile (or the Change event) to force updating. Excel also has a dependency tree to further control when functions recalculate, but that is not accessible from VBA.
@ceci there is a problem with your idea. It only works one time after I changed the VBA code. Might it be a problem that I want to call a function and not a sub?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.