Excel VBA:将ParamArray转发到Application.Run [英] Excel VBA: Forward ParamArray to Application.Run

问题描述

在Excel VBA中进行编程时，我经常关闭计算"和屏幕更新"(有时还包括DisplayAlerts等)，进行一些冗长的计算，然后将它们切换回以前的状态(不一定要启用，因为这些函数可能会相互调用).所以我想有一个标准的方法.

VBA中没有lambda函数，但是Application.Run可能是一个可以接受的解决方案，所以我来到了这段代码:

 公共函数FastRun(strMacro引用为字符串，ParamArray varArgs()引用为变量)将blnOldScreenUpdating设为布尔值:blnOldScreenUpdating = Application.ScreenUpdatingApplication.ScreenUpdating = FalseDim clcOldCalculation as XlCalculation:clcOldCalculation = Application.CalculationApplication.Calculation = xlCalculationManualFastRun = Application.Run(strMacroQuoted，varArgs(0))Application.Calculation = clcOldCalculationApplication.ScreenUpdating = blnOldScreenUpdating结束功能 

我可以将varArgs中的所有参数一一指定(从0到29)，因此我的FastRun不会限制参数的数量超出Run设置的限制.

我的问题是:有没有比彼此后写30个参数更好(更整洁)的解决方案了?

解决方案

调用 Application.Run 的原因并不明显.我猜有两种可能:1.您正在从另一个工作簿或外接程序中调用过程，或者2.由于某种原因，您需要通过其名称在当前项目中调用过程，并以一个字符串.

假设您要坚持将所有参数通过 ParamArray 传递到例程中，然后调用 Application.Run 的结构，则运行"过程将需要使用 Variant 作为参数，而不是 ParamArray . ParamArray 是一个数组，而 Variant 在传递时也将如此解释.因此您的代码应如下所示:

  Public Sub Main1()CallingRoutine"MyRoutine"，1、2、3结束子Public Sub CallingRoutine(例程名作为字符串，ParamArray varArgs()作为变量)Application.Run例程名称，varArgs结束子Public Sub MyRoutine(arr为变体)Debug.Print项目0 =";arr(0)Debug.Print项目1 =";arr(1)Debug.Print项目1 =";arr(2)结束子 

我怀疑可能会有更好的方法来完成任务，如果您能提供更多细节，我们可能会为您提供更多帮助.主要问题是为什么您需要通过 Application.Run 调用例程，代码位于何处，并且 varArgs 数组中包含哪些变量?

When programming in Excel VBA, I often switch off Calculation and ScreenUpdating (sometimes also DisplayAlerts, etc.), do some lengthy calculations, and switch them back to their previous states (not necessarily on because these functions may call each other). So I would like to have a standard way for this.

There are no lambda functions in VBA but Application.Run may be an acceptable solution, so I came to this code:

Public Function FastRun(strMacroQuoted As String, ParamArray varArgs() As Variant) As Variant
    Dim blnOldScreenUpdating As Boolean: blnOldScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Dim clcOldCalculation As XlCalculation: clcOldCalculation = Application.Calculation
    Application.Calculation = xlCalculationManual

    FastRun = Application.Run(strMacroQuoted, varArgs(0))

    Application.Calculation = clcOldCalculation
    Application.ScreenUpdating = blnOldScreenUpdating
End Function

I could give all parameters from varArgs to Run one by one (from 0 to 29), and so my FastRun would not constrain the number of parameters beyond the limit set by Run.

My question is: Is there any better (neater) solution than writing 30 parameters after each other?

解决方案

It's not obvious why you're calling Application.Run. I guess there are two possibilities: 1. that you're calling a procedure from another workbook or an add-in, or 2. that, for some reason, you need to call a procedure within your current project by its name, passed as a string.

Assuming you want to stick with the structure of passing all of your arguments via a ParamArray into a routine which then calls Application.Run, your 'run' procedure would need to have a Variant as its argument instead of a ParamArray. The ParamArray is an array and the Variant would interpret as such when passed. So your code would look something like this:

Public Sub Main1()
    CallingRoutine "MyRoutine", 1, 2, 3
End Sub

Public Sub CallingRoutine(routineName As String, ParamArray varArgs() As Variant)
    Application.Run routineName, varArgs
End Sub

Public Sub MyRoutine(arr As Variant)
        Debug.Print "Item 0 ="; arr(0)
        Debug.Print "Item 1 ="; arr(1)
        Debug.Print "Item 1 ="; arr(2)
End Sub

I suspect there might be better ways of achieving your task and if you could provide a little more detail we might be able to help you more. The main questions would be why do you need to call your routine by Application.Run, where is your code located and what are the variables contained in your varArgs array?

这篇关于Excel VBA:将ParamArray转发到Application.Run的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！