gpt4 book ai didi

excel - 将excel的新动态数组功能与VBA相结合

转载 作者:行者123 更新时间:2023-12-04 20:23:08 25 4
gpt4 key购买 nike

我尝试在 excel 中结合 vba 更多地使用动态数组。我的问题是我不能用 vba 返回表格列。这是我想做的一个最小的例子:
我有两个表 TabFeb 和 TabMar(见下图)。他们每个人都有一个列成本,我想单独总结一下。结果应放入新表中。这可以在 Excel 中分别使用 =SUM(TabFeb[Costs]) 和 =SUM(TabMar[Costs]) 轻松完成。我现在的想法是编写一个 VBA 函数,它接受一个字符串作为输入,在这个例子中它将是月份,并根据输入返回表。之后,它将被总结并在单元格中给出结果。
我尝试了以下方法:

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
这个想法的问题是这个函数只是复制表数据。因此,如果我更改输入表数据,总和不会改变。必须手动重新计算每个单元格。不知何故,我需要 VBA 为输入“Feb”返回 TabFeb[Costs]。有谁知道如何做到这一点?
Example

最佳答案

真的只是一个单线 (除非你想做一些功能内错误检查)

Function Selectmon(mon As String) As Range
Set Selectmon = Range("Tab" & mon & "[Costs]")
End Function
正如@ceci 所暗示的,这个公式不会随着 的变化而更新。表 .根据工作表的其他细节,您可以通过以下方式更新它
  • 将其嵌入工作表更改事件代码中;
  • 或添加行 Application.Volatile到函数本身。

  • 当工作表上的任何更改可能导致重新计算时,后一种方法将强制重新计算。
    第一种方法只能在数据发生变化时限制重新计算,但还有其他限制。
    工作表更改方法的限制之一是它只能在相关工作表上工作。
    如果您使用工作簿工作表更改方法,则不会有此限制。
    无论哪种情况,您都可以限制代码仅在表更改时运行。
    这是一种通用的方法:
    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
    您可以使用其他代码来查找相关公式并更新该公式 - 可能不值得付出努力。

    关于excel - 将excel的新动态数组功能与VBA相结合,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/67545769/

    25 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com