File is GENERATOR(X,Y) Macros are enabled for subs from where I am calling GENERATOR. Called it with ... Application.Run "PERSONAL.XLSB!GENERATOR(X,Y)" X and Y had been defined prior to the call. Previous answers did not involve the PERSONAL.XLSB workbook. Previous answers use "Open workbook" As I understand it PERSONAL.XLSB is always open.

Answer provided did work up to a point. Call to GENERATOR revised as in answer worked; GENERATOR provided R (e.g. 9). However upon return to calling routine R was "0", i.e. was not 9. R was "Dim" in both calling module and in PERSONAL.XLSB module. MRE [After fixing the 'call' IAW answer]

Code in PERSONAL:

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

Code in calling module:

    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 

Running the exact same GENERATOR code w/ GENERATOR in the same module as is the calling routine works. So the prob is that the value for R is lost when transferring process execution from PERSONAL to the calling routine.

File is GENERATOR(X,Y) Macros are enabled for subs from where I am calling GENERATOR. Called it with ... Application.Run "PERSONAL.XLSB!GENERATOR(X,Y)" X and Y had been defined prior to the call. Previous answers did not involve the PERSONAL.XLSB workbook. Previous answers use "Open workbook" As I understand it PERSONAL.XLSB is always open.

Answer provided did work up to a point. Call to GENERATOR revised as in answer worked; GENERATOR provided R (e.g. 9). However upon return to calling routine R was "0", i.e. was not 9. R was "Dim" in both calling module and in PERSONAL.XLSB module. MRE [After fixing the 'call' IAW answer]

Code in PERSONAL:

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

Code in calling module:

    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 

Running the exact same GENERATOR code w/ GENERATOR in the same module as is the calling routine works. So the prob is that the value for R is lost when transferring process execution from PERSONAL to the calling routine.

• excel
• vba

• 1 This question is similar to: Cannot run the macro. "The macro may not be available or all macros may be disabled". If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – Shrotter Commented Mar 23 at 16:44
• 3 Please post a minimal reproducible example. Also try to include the path to the file. – Shrotter Commented Mar 23 at 20:10
• 2 You're not passing the arguments correctly: previously here google/… – Tim Williams Commented Mar 23 at 21:10

2 Answers 2

You can shorten your Generator code to:

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

and then call it using:

Sub Test()

    MsgBox Application.Run("PERSONAL.XLSB!GENERATOR", 9, 0)
    
End Sub

Microsoft reference says the syntax is:

expression.Run (Macro, Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

So the call should be:

Application.Run "PERSONAL.XLSB!GENERATOR", X, Y

The Run method will pass X and Y to your sub.

If you want to return a value from the macro you called, you can define your GENERATOR as a function returning the value (for R):

Public Function GENERATOR(X as integer,Y as integer) as integer
   Randomize (Now * 1000000000)
   GENERATOR= Int((X-Y+1)*Rnd+Y)
End Function

and call it like:

R=Application.Run( "PERSONAL.XLSB!GENERATOR", X, Y)

The assignment to R will retrieve the result of the function into R.