在VB中使用工作表函数 [英] Using worksheet functions in VB

问题描述

如何在内存中使用数组作为通常是范围对象的工作表函数的参数？

我理解如何使用单元格范围来完成此操作但是想要通过不必将值写入单元格来加快执行速度。

具体来说，我想利用内存中的矩阵操作函数来操作存储在数组中的矩阵。

解决方案

这实际上取决于你想要使用的工作表函数....

Sub TestMacro（）

    Dim v As Variant

    Dim w As Variant

    v =数组（1,5,6,7,8,9）

    w =数组（1,2,3,4,5,6）

    MsgBox"SP：" &安培; Application.WorksheetFunction.SumProduct（v，w）

    MsgBox"Max：" &安培; Application.WorksheetFunction.Max（v）

    MsgBox"Min：" &安培; Application.WorksheetFunction.Min（v）

    MsgBox"Avg：" &安培; Application.WorksheetFunction.Average（v）

    MsgBox" Count：" &安培; Application.WorksheetFunction.Count（v）

    MsgBox"匹配：" &安培; Application.WorksheetFunction.Match（7，v，False）



结束次级

How can I use an array in memory as the argument to a worksheet function that would normally be a range object?

I understand how to use cell ranges to accomplish this but want to speed up execution by not having to write the values to cells.

Specifically I want to utilize matrix manipulation functions in memory to operate on matrices stored in arrays.

解决方案

It really depends on the worksheet function you want to use....

Sub TestMacro()

    Dim v As Variant
    Dim w As Variant
    v = Array(1, 5, 6, 7, 8, 9)
    w = Array(1, 2, 3, 4, 5, 6)
    MsgBox "SP: " & Application.WorksheetFunction.SumProduct(v, w)

    MsgBox "Max: " & Application.WorksheetFunction.Max(v)
    MsgBox "Min: " & Application.WorksheetFunction.Min(v)
    MsgBox "Avg: " & Application.WorksheetFunction.Average(v)
    MsgBox "Count: " & Application.WorksheetFunction.Count(v)
    MsgBox "Match: " & Application.WorksheetFunction.Match(7, v, False)

End Sub

这篇关于在VB中使用工作表函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！