Can I use a macro to enter Poisson random numbers?

[raphile]

Hey, I'm sure this has been asked before but I couldn't find it using a quick search so here goes...

I have a spreadsheet which has a column of 100 Poisson-distributed random numbers (lambda=2) which I generated using Data Analysis -> Random Number Generation in Excel. I want to create a macro to put these numbers in for me. When I tried recording one, it didn't work. Is there a way to do it?

I can provide more info if needed! Thanks!

[StephenR]

When I recorded a macro, I got this. It should be possible to adapt, no?

Please Login or Register  to view this content.

[raphile]

I also got a similar piece of code (I'm using Excel 2007 by the way):

Please Login or Register  to view this content.

But when I try running the macro, it gives me an error:

Run-time error '1004':

Method 'Run' of object '_Application' failed

How can I fix this?

[StephenR]

Copy what the macro recorder throws up:

Please Login or Register  to view this content.

[raphile]

Hi Stephen,

When I tried copying the line exactly as you entered it, i.e.

Please Login or Register  to view this content.

I got an error which said "ATPVBAEN.XLA!Random" could not be found.

When I changed it to:

Please Login or Register  to view this content.

(with an extra 'M' in the filename), as the macro recorded gave me originally, I got the error that I described above:

Run-time error '1004':

Method 'Run' of object '_Application' failed

Any suggestions?

[StephenR]

Are you using Excel 2003? I think the extra M is for 2007. Have you definitely installed the add-in? Tools > Add-Ins.

[raphile]

I'm using 2007. Sorry, I've just updated my profile info to reflect that. The Data Analysis stuff is already there in 2007, without me having to install it. I could try to get it to work on one of the college computers which uses Excel 2003, but I would still like it to get it work on my computer at home if I can.

[StephenR]

This worked for me in 2007. Note the extra "".

Please Login or Register  to view this content.

[raphile]

I still get an error by using that, but a different one this time:

Run-time error '1004':

Application-defined or object-defined error

[StephenR]

Can you attach a file? I haven't used 2007 much. I thought this might be a simple query to resolve!

[raphile]

Yeah, I'm only just learning VBA, so maybe I'm doing something basic wrong.

In this file I have 2 macros, the first is in Sheet 1 and contains the line of code exactly as you typed it in your last post. The other macro is in the module and is what I got by recording. They both produce different error messages when I try to run them.

[StephenR]

Most odd, both of those work for me.

Perhaps there is a setting which needs to be tweaked.

See if somebody with more knowledge of 07 can shed light on this.

EDIT: there is a 2007 forum here, I suggest you ask there.

[raphile]

I've figured it out now, I did have to tweak an option in Excel. I had to go to Excel Options > Add-Ins > Manage Excel Add-Ins and then click the "Analysis ToolPak - VBA" box. Previously, only the "Analysis ToolPak" (without the VBA) was selected. Thanks for the help!

[shg]

That's not going to give you Poisson variates. You could use this:

Please Login or Register  to view this content.

Invoke, for example, as

Please Login or Register  to view this content.

[raphile]

Why won't it give me Poisson variates? Surely it should do if I recorded it and selected the Poisson option in Data Analysis?

[shg]

My apologies, I assume you're right. I've been generating my own random numbers for a long time, and never used Data Analysis to do so.