[vba] 自定义select单元格函数

最新推荐文章于 2023-06-02 19:20:01 发布

ASKMSSC

最新推荐文章于 2023-06-02 19:20:01 发布

阅读量345

点赞数

文章标签： vba

本文链接：https://blog.csdn.net/qq_26244463/article/details/127989764

版权

Sub test_PST_creatApplication()

arr = fSelect("select process array")

MsgBox (arr(2).Name)
MsgBox (arr(3).Name)
MsgBox (arr(4)(1))
sSelectToWriteAry (arr(1))


End Sub

Function fSelect(Optional rTitle As String)
    If IsMissing(rTitle) Then rTitle = ""
    Dim DataArea As Range
    
    Application.ScreenUpdating = True
    On Error Resume Next
    Set DataArea = Application.InputBox(prompt:=rTitle, Type:=8)
    If Err.Number = 13 Then Exit Function
    Application.ScreenUpdating = False

    selectSheetName = DataArea.Worksheet.Name '所选单元格的表名
    selectBookName = DataArea.Worksheet.Parent.Name '所选单元格的工作簿名

    Dim RowsCount, ColumnsCount, dRow, dCol
    dRow = DataArea.Row '首选 单元格 行
    dCol = DataArea.Column '首选 单元格 列
    
    ColumnsCount = DataArea.Columns.Count '选择列数
    RowsCount = DataArea.Rows.Count '选择行数
    
    ReDim DataAry(1 To RowsCount, 1 To ColumnsCount)
    DataAry = DataArea
    
    Dim arr(1 To 4)
     arr(1) = DataAry
    Set arr(2) = DataArea.Worksheet
    Set arr(3) = DataArea.Worksheet.Parent
     arr(4) = Array(dRow, dCol)
    
    fSelect = arr
    Erase DataAry
End Function