Application.Run和range参数? [英] Application.Run and range parameter?

问题描述

如果我有一个excel函数，例如:

If I have an excel function such as:

=my_function(A1:C4)

我想通过VBA调用它，例如:

and I want to call this from VBA like:

Dim t as variant
t = Application.Run("my_function",X)

X代表A1:C4的最简单方法是什么?

What is the simplest way for X to represent A1:C4?

推荐答案

您的范围必须来自工作表，因此，如果您先定义该范围，然后再将其传递给其他工作簿中的函数(我想这就是您的意思)想要这样做，因为您将不会使用Application.Run).因此，以下代码可以解决问题:

Your range has to come from a worksheet, so if you define that range first and then pass it to the function in your other workbook (I assume that's what you want to do since you wouldn't use Application.Run otherwise). So the following code will do the trick:

Sub RunFunctionInAnotherWorkbook()
    Dim rThisRange as Range
    Dim vResult as Variant

    Set rThisRange = ActiveSheet.Range("A1:C4")

    vResult = Application.Run("'MyOtherWorkbook.xls'!TheModuleName.TheSubName", rThisRange)
End Sub

工作簿"MyOtherWorkbook.xls"中的函数:

And the function in the workbook "MyOtherWorkbook.xls":

Function TheSubName(inputRange as Range) as Variant

    'Work your magic here

End Function

这篇关于Application.Run和range参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！