如何在Test.xlsx中调用Stack.xlsm的VBA函数设置单元格值？

原代码的问题点 #

你的DataTransfer子程序存在两处关键问题：

1. 单元格引用语法错误：VBA中不能直接用cell(A1)，正确写法是Range("A1")或Cells(行号,列号)
2. StudentData函数依赖ActiveSheet，执行时若Test.xlsx处于激活状态，会错误读取Test的单元格数据而非Stack.xlsm的目标数据

修正后的完整实现 #

1. 优化Stack.xlsm中的StudentData函数 #

修改函数，明确指定数据所在的工作簿和工作表，避免依赖激活状态：

Public Function StudentData(Index As Integer) As Variant
    Dim StudentID As Double
    Dim StudentScore As Double
    Dim IntA(1 To 2) As Double
    
    ' 锁定Stack.xlsm的Sheet1（根据实际工作表名称调整）
    With ThisWorkbook.Worksheets("Sheet1")
        StudentID = .Cells(2, 1).Value
        StudentScore = .Cells(2, 2).Value
    End With
    
    IntA(1) = StudentID
    IntA(2) = StudentScore
    
    ' 增加索引合法性校验，防止越界错误
    If Index >= LBound(IntA) And Index <= UBound(IntA) Then
        StudentData = IntA(Index)
    Else
        StudentData = "无效索引"
    End If
End Function

2. 修正Stack.xlsm中的DataTransfer子程序 #

修复语法错误，增加文件状态检查：

Public Sub DataTransfer()
    Dim testWB As Workbook
    Dim targetWS As Worksheet
    
    ' 检查Test.xlsx是否已打开
    On Error Resume Next
    Set testWB = Workbooks("Test.xlsx")
    On Error GoTo 0
    
    If testWB Is Nothing Then
        MsgBox "请先打开Test.xlsx文件！"
        Exit Sub
    End If
    
    ' 指定Test.xlsx的目标工作表
    Set targetWS = testWB.Worksheets("Sheet1")
    
    ' 执行赋值操作
    targetWS.Range("A1").Value = "ID:" & StudentData(1)
    targetWS.Range("B1").Value = "Score:" & StudentData(2)
End Sub

操作说明 #

• 运行DataTransfer前，必须确保Test.xlsx处于打开状态
• 若Stack.xlsm中数据所在工作表不是Sheet1，需修改函数中的工作表名称
• Test.xlsx保持.xlsx格式完全可行，所有宏逻辑均存储在Stack.xlsm中

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