调用名称在单元格文本中的宏 [英] Call a macro which name is in the text of a cell

问题描述

请您提供以下帮助:

1. 我需要调用一个名称可变的宏，该宏位于Workbook1的Sheet1的单元格C5中.
2. 我需要在另一个工作簿(例如Workbook2)中运行该宏.

到目前为止(在Workbook1中)我拥有的代码是:

Code that I have (in Workbook1) so far is:

Public Sub RS()
    'Setting source worksheet and workbook where the macros are saved
    Dim ws1 As Worksheet
    Dim wb1 As Workbook
    Set wb1 = ActiveWorkbook
    Set ws1 = ActiveWorkbook.ActiveSheet

    'Defining the cell that contains the name of the chosen macro to be run
    Dim Macro1 As String
    Macro1 = Range("C5").Value

    'Selecting target workbook
    Workbooks("Workbook2").Activate
    ActiveSheet.Select
    Dim ws2 As Worksheet
    Dim wb2 As Workbook
    Set wb2 = ActiveWorkbook
    Set ws2 = ActiveWorkbook.ActiveSheet

    'Running in Workbook2, the macro selected in workbook1
    Call Macro1
End Sub

问题是尚未识别出 Macro1 .我也一直在尝试使用 CallByName 函数和 Application.Run Macro1 ，但也没有运气.请告诉我我所缺少的或应该修改的内容.

The problem is that Macro1 has not been recognized. I've been also trying with CallByName function, and Application.Run Macro1, but no luck either. Please tell me what I'm missing or what I should modify.

推荐答案

替换行

    Call Macro1

与此:

    Application.Run "'" & wb2.Name & "'!VBAProject." & ws2.CodeName & "." & Macro1

您的代码暗含"Macro1"在ws2的代码模块中.如果它位于工作簿"wb2"的另一个模块中，则将上面的"ws2.CodeName"替换为该模块的代号.

Your code implies the "Macro1" is in the ws2's code module. If it is located in another module of the workbook "wb2" then replace "ws2.CodeName" above with the codename of that module.

第二次更新基于Flavinho的信息.代码行变为:

    'Application.Run "Workbook1.xlsm!VBAProject.Module3.ts1"
    Application.Run "Workbook1.xlsm!VBAProject.Module3." & ts1

这篇关于调用名称在单元格文本中的宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！