Prev: Help with printing rows and/or columns
Next: Sheet and workbook code: All sheets should have row 1 to 5 of Shee
From: OssieMac on 25 Mar 2010 16:19
Thanks Chip. I've now tested in xl2002. I was not aware that it did not work
in earlier versions of xl.

To Bradly,

Just to make it clear what you need to do.
In the Worksheet you need the Add-In Analysis ToolPak - VBA.
In the VBA Editor select menu item Tools -> References and check the box
against atpvbean.xls. (Ensure you check the box; not just select the line)
and then OK.

Then your code is as follows.

lngRandom = [atpvbaen.xls] _
.RandBetween(1, lngCells)

or you can leave out [atpvbaen.xls]. as follows. (However, I do like Chip's
suggestion to include it because it provides documentation.)

lngRandom = RandBetween(1, lngCells)


or you can use the alternative code I gave you as follows and you then do
not need Analysis ToolPak or the Reference in VBA. Probably the better
solution.

Randomize
lngRandom = Int((lngCells * Rnd) + 1)


--
Regards,

OssieMac


"Chip Pearson" wrote:

> >There is Analysis ToolPak and Analysis ToolPak - VBA. I think you need both.
> >However, if still won't work then replace the following code
>
> Because RandBetween is not a native Excel function (in 2003 and
> earlier), it is not going to be found under WorksheetFunction. If you
> have the ATP VBA reference (you don't need both ATP references, just
> the VBA one), you can call RandBetween as if it were a native VBA
> function. Note that you must have the add-in loaded and your project
> must reference the atpvbaen.xla library.
>
> L = RandBetween(1, 100)
>
> When I use functions from another library, I like to qualify the name
> of the function with the library name, just to keep things clear and
> well documented.
>
> L = [atpvbaen.xls].RandBetween(1, 100)
>
> The [ ] chars are required because the library name contains a period
> (and yes, the referenced library is "xls" not "xla").
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional,
> Excel, 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> On Wed, 24 Mar 2010 13:01:02 -0700, OssieMac
> <OssieMac(a)discussions.microsoft.com> wrote:
>
> >Hi Bradley,
> >
> >There is Analysis ToolPak and Analysis ToolPak - VBA. I think you need both.
> >However, if still won't work then replace the following code
> >
> >lngRandom = WorksheetFunction _
> > .RandBetween(1, lngCells)
> >
> >with these 2 lines of code
> >
> >Randomize
> >lngRandom = Int((lngCells * Rnd) + 1)
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Help with printing rows and/or columns
Next: Sheet and workbook code: All sheets should have row 1 to 5 of Shee