[Frank & Pam Hayes]

Hi,

I am trying to call the Data Analysis Random Number Generator from a Macro.
My range of Values and Probabilities is in Sheet 1 in the range A1:B4 and is
named MyRange.

My macro is:

Sub GenRandomNumbers()

Application.Run "ATPVBAEN.XLA!Random", ActiveSheet.Range("$a$15"), 1,
99, _
7, , ActiveSheet.Range("MyRange")

End Sub

This works fine until I move away from using ActiveSheet. If I try to run
the macro using explicit sheet references, the macro fails as in the
following example:

Sub GenRandomNumbers()

Application.Run "ATPVBAEN.XLA!Random", Sheet2.Range("$a$15"), 1, 99, _
7, , Sheet1.Range("MyRange")

End Sub


I would appreciate any guidance the group can provide. I am also interested
in finding out how I can find the list of arguments for a called function
like this.

Thanks,

Frank Hayes

[Kieran[_42_]]

Frank & Pam

Try this amendment

Sub GenRandomNumbers()

Application.Run "ATPVBAEN.XLA!Random", Sheets(2).Range("$a$15"), 1, 99
7, , Sheet(1).Range("MyRange")

End Sub

This assumes that the active workbook will be used.
If you want to reference other than the active workbook you will nee
to further qualify the sheets() reference like thi
workbook.sheets(n).et

--
Message posted from http://www.ExcelForum.com

[Frank & Pam Hayes]

That worked Kieran. Thank you for the advice.

Frank

"Kieran " wrote in message
...
Frank & Pam

Try this amendment

Sub GenRandomNumbers()

Application.Run "ATPVBAEN.XLA!Random", Sheets(2).Range("$a$15"), 1, 99,
7, , Sheet(1).Range("MyRange")

End Sub

This assumes that the active workbook will be used.
If you want to reference other than the active workbook you will need
to further qualify the sheets() reference like this
workbook.sheets(n).etc


---
Message posted from http://www.ExcelForum.com/

[Tom Ogilvy]

Need to get a better newsreader. Problem was solved yesterday.

--
Regards,
Tom Ogilvy

"Jonathan Rynd" wrote in message
. ..
Kieran wrote in
:

Sheets(2)
Sheet(1)

Is it "Sheets" or "Sheet" or do both of them work?

--
My email address has an extra @ (spell it out) and an extra invalid.
Please
remove them if you are not a spammer or list broker and want to reply.

[Jonathan Rynd]

"Tom Ogilvy" wrote in news:Oq9ovbZ8DHA.3360
@tk2msftngp13.phx.gbl:

Need to get a better newsreader. Problem was solved yesterday.

I was asking because I wanted to know. I saw that the problem had been
solved.


--
My email address has an extra @ (spell it out) and an extra invalid. Please
remove them if you are not a spammer or list broker and want to reply.