Excel VBA：如何通过另一个宏修改目标宏的students数组？

问题背景 #

需要通过ListeEleve宏向MacroLundi宏传递从工作表“ListeEleve”A1单元格读取的学生名单，替换原MacroLundi中硬编码的students数组，实现动态更新学生日程，但现有代码无法正常运行。

原MacroLundi宏代码 #

Sub MacroLundi()

    Dim students As Variant
    Dim student As Variant
    Dim mySheet As Worksheet
    Set mySheet = ThisWorkbook.Sheets("Lundi")
    students = Array("Armando", "Mathéo", "Djebril", "Anase", "Thibo_M")

    ' Loop through the students and update their schedules
    For Each student In students
        ' Emploi du temps Professionel 1
        If InStr(mySheet.Range("C6").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B11").Value = mySheet.Range("C5").Value
        If InStr(mySheet.Range("C8").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B12").Value = mySheet.Range("C7").Value
        If InStr(mySheet.Range("C10").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B13").Value = mySheet.Range("C9").Value
        If InStr(mySheet.Range("C13").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B15").Value = mySheet.Range("C12").Value
        If InStr(mySheet.Range("C15").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B16").Value = mySheet.Range("C14").Value
        If InStr(mySheet.Range("C17").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B17").Value = mySheet.Range("C16").Value
        If InStr(mySheet.Range("C19").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B18").Value = mySheet.Range("C18").Value
End Sub

用户尝试的代码 #

Sub ListeEleve()

    Dim students As Variant
    Set mySheet = ThisWorkbook.Sheets("ListeEleve")
    students = Split(Range("A1").Value, ",")
    
    MacroLundi students

End Sub

Sub MacroLundi(ByVal students As Variant)

    Dim i As Integer
    For i = LBound(students) To UBound(students)
        students(i) = Trim(students(i))
    Next i

End Sub

问题原因 #

• 核心功能丢失：修改后的MacroLundi仅保留了数组去空格逻辑，完全删除了原宏中更新学生日程的核心代码，导致宏执行后没有任何实际效果。
• 工作表未指定：ListeEleve中的Range("A1")未关联到mySheet对象，会默认读取当前激活工作表的A1单元格，若激活表不是“ListeEleve”则会读取错误数据。
• 字符串拆分不彻底：A1单元格内容为"Alexandre", "Mathéo"，直接按逗号拆分后，每个元素会包含双引号（如"Alexandre"），仅用Trim无法去除双引号，导致后续学生名匹配失败。

修正后的完整代码 #

ListeEleve宏（读取并处理学生名单） #

Sub ListeEleve()
    Dim students As Variant
    Dim rawStr As String
    Dim i As Integer
    Dim mySheet As Worksheet
    
    Set mySheet = ThisWorkbook.Sheets("ListeEleve")
    ' 读取A1内容并清理首尾无关字符
    rawStr = Trim(mySheet.Range("A1").Value)
    ' 去掉首尾的双引号（如果存在）
    If Left(rawStr, 1) = """" Then rawStr = Mid(rawStr, 2)
    If Right(rawStr, 1) = """" Then rawStr = Left(rawStr, Len(rawStr) - 1)
    
    ' 按逗号拆分
    students = Split(rawStr, ",")
    
    ' 清理每个元素的空格和双引号
    For i = LBound(students) To UBound(students)
        students(i) = Trim(Replace(students(i), """", ""))
    Next i
    
    ' 传入MacroLundi执行
    MacroLundi students
End Sub

MacroLundi宏（接收参数并更新日程） #

Sub MacroLundi(ByVal students As Variant)
    Dim student As Variant
    Dim mySheet As Worksheet
    Set mySheet = ThisWorkbook.Sheets("Lundi")

    ' Loop through the students and update their schedules
    For Each student In students
        ' Emploi du temps Professionel 1
        If InStr(mySheet.Range("C6").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B11").Value = mySheet.Range("C5").Value
        If InStr(mySheet.Range("C8").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B12").Value = mySheet.Range("C7").Value
        If InStr(mySheet.Range("C10").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B13").Value = mySheet.Range("C9").Value
        If InStr(mySheet.Range("C13").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B15").Value = mySheet.Range("C12").Value
        If InStr(mySheet.Range("C15").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B16").Value = mySheet.Range("C14").Value
        If InStr(mySheet.Range("C17").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B17").Value = mySheet.Range("C16").Value
        If InStr(mySheet.Range("C19").Value, student) > 0 Then Workbooks("EdtEleve.ods").Sheets(student).Range("B18").Value = mySheet.Range("C18").Value
    Next student
End Sub

关键修改说明 #

1. 指定工作表对象：所有Range操作都关联到对应的工作表，避免因激活表变化导致的读取错误。
2. 完善字符串处理：先清理A1字符串的首尾双引号，再拆分，最后用Replace去除每个元素的双引号并去空格，确保学生名格式与原宏一致。
3. 恢复核心功能：MacroLundi重新保留了原有的日程更新逻辑，同时接收外部传入的students数组，实现动态更新。

内容的提问来源于stack exchange，提问作者AlexandreTELOUK