调用PERSONAL.XLSB中的GENERATOR宏后返回值R丢失如何解决？

问题情况 #

• 初始报错：运行PERSONAL.XLSB中的子过程时，提示：Cannot run 'PERSONAL.XLSB![file's name]'. The macro ... macros may be disabled.
• 后续异常：调整调用方式后，GENERATOR宏能正常生成随机数R（例如9），但返回调用程序后，调用模块里的R值变为0。两个模块都用Dim R As Integer声明了模块级变量，同一模块内调用正常，仅跨PERSONAL.XLSB调用时出现返回值丢失。

现有代码 #

PERSONAL.XLSB中的代码：

Dim R As Integer
Sub GENERATOR(X,Y)
 U=X
 L=Y
 Randomize (Now * 1000000000)
 R = Int((U-L+1)*Rnd+L)
End Sub

调用模块中的代码：

Dim X As Integer
Dim Y As Integer
Dim R As Integer
Sub test()
 X=9
 Y=0
 Application.Run _
 "PERSONAL.XLSB!GENERATOR", X, Y
 MsgBox (R)
End Sub 

问题原因 #

两个模块里的Dim R As Integer都是模块级私有变量，作用范围仅限各自所在的模块。PERSONAL中的GENERATOR修改的是自己模块内的R，调用模块里的R是完全独立的另一个变量，自然无法获取到GENERATOR生成的值。同一模块内调用正常是因为共享了同一个模块级变量，跨模块时变量作用域不互通。

解决方案 #

方案1：将Sub改为Function返回值（推荐）

把GENERATOR改成Function过程，直接返回生成的随机数，无需依赖模块级变量：
修改后的PERSONAL.XLSB代码：

Function GENERATOR(X As Integer, Y As Integer) As Integer
    Randomize (Now * 1000000000)
    GENERATOR = Int((X - Y + 1) * Rnd + Y)
End Function

修改后的调用模块代码：

Sub test()
    Dim X As Integer, Y As Integer, R As Integer
    X = 9
    Y = 0
    R = Application.Run("PERSONAL.XLSB!GENERATOR", X, Y)
    MsgBox R
End Sub

方案2：通过ByRef参数传递返回值

如果坚持用Sub过程，可通过ByRef参数将结果传递回调用方：
修改后的PERSONAL.XLSB代码：

Sub GENERATOR(X As Integer, Y As Integer, ByRef R As Integer)
    Randomize (Now * 1000000000)
    R = Int((X - Y + 1) * Rnd + Y)
End Sub

修改后的调用模块代码：

Sub test()
    Dim X As Integer, Y As Integer, R As Integer
    X = 9
    Y = 0
    Application.Run "PERSONAL.XLSB!GENERATOR", X, Y, R
    MsgBox R
End Sub

初始宏禁用报错的解决

确保PERSONAL.XLSB所在文件夹被添加到Excel信任位置，或者在宏设置中启用宏：

• 打开Excel选项 → 信任中心 → 信任中心设置 → 宏设置，选择「启用所有宏」（不推荐长期使用，建议添加信任位置）
• 或在信任中心设置中添加PERSONAL.XLSB所在路径为信任位置

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