时间戳

首页 > 解决方案 > 从 InputBox 传递用户选择以用作 Application.Run 的参数

excel - 从 InputBox 传递用户选择以用作 Application.Run 的参数

问题描述

如何传递用户选择用作 Application.Run 的参数的内容？

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Sub Correlation()
Dim UserRange As Range
Dim UserSelect As Variant

'user selects the range
Set UserRange = Application.InputBox(Title:="Correlation Analysis", Prompt:="Select range", Type:=8)
     UserSelect = Selection.Value

'function returns matrix of correlation coefficients based on the selected range
Application.Run "ATPVBAEN.XLAM!Mcorrel", Worksheets("VBA").Selection.Value, Worksheets("Correl").Range("$a$2"), "C", True

End Sub

标签： excelvba

解决方案

UserRange如果您执行以下操作，则用户的选定范围在。请注意，我添加了一些错误处理，因此如果用户按下取消，它就会退出。

Option Explicit

Public Sub Correlation()
    'user selects the range
    On Error Resume Next 'error handling so pressing cancel does not error
    Dim UserRange As Range
    Set UserRange = Application.InputBox(Title:="Correlation Analysis", Prompt:="Select range", Type:=8)

    If UserRange Is Nothing Then Exit Sub  'user pressed cancel
    On Error GoTo 0 'always re-activacte error reporting!!!


    'UserRange now referst to the selected range of the user
    Debug.Print UserRange.Address
End Sub

然后，您可以使用该变量UserRange将其作为参数汇总

Application.Run "ATPVBAEN.XLAM!Mcorrel", UserRange
'adjust it to your need.

推荐阅读