Can someone provide a VBA function to populate 1000's
of cells with a simple expression, for example =RAND()?

That is, I want to have a program/macro to populate the cells
instead of having to replicate them manually (e.g, by dragging),
which is painfully slow for such large numbers of cells. If
there is an alternative that is equally fast and easy to use,
I would appreciate hearing about it.

I would also appreciate step-by-step instructions for how to
enter the macro and how to execute it.

And since I am interested in an expression involving RAND(),
I would appreciate advice on how to prevent re-execution of
the macro each time the spreadsheet is recalculated -- other
than turning off automatic recalculation. (But if that is the
only way, I would appreciate confirmation.) Or is that the
norm; i.e, are macros executed only when you invoke them
explicitly from the keyboard?

I know I should read a book, and I will in time. But I am
hoping this is a simple enough request that someone will
not mind filling in the blanks sooner than I digest a book.

Thanks.

One way:

Range("A1:J1000").Formula = "=RAND()"

This will insert the formula, which will recalculate unless you turn
Calculation to Manual.

You could convert the formulae to values so they won't recalculate:

With Range("A1:J1000")
.Formula = "=RAND()"
.Value = .Value
End With

Another way, that inserts the value of VBA's Rnd method, not a formula:

Dim rng As Range
Dim vArr As Variant
Dim i As Long
Dim j As Long
Set rng = Range("A1:J1000")
ReDim vArr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
For i = 1 To UBound(vArr, 1)
For j = 1 To UBound(vArr, 2)
vArr(i, j) = Rnd
Next j
Next i
rng.Value = vArr

In article . com,
wrote:

Can someone provide a VBA function to populate 1000's
of cells with a simple expression, for example =RAND()?

That is, I want to have a program/macro to populate the cells
instead of having to replicate them manually (e.g, by dragging),
which is painfully slow for such large numbers of cells. If
there is an alternative that is equally fast and easy to use,
I would appreciate hearing about it.

I would also appreciate step-by-step instructions for how to
enter the macro and how to execute it.

And since I am interested in an expression involving RAND(),
I would appreciate advice on how to prevent re-execution of
the macro each time the spreadsheet is recalculated -- other
than turning off automatic recalculation. (But if that is the
only way, I would appreciate confirmation.) Or is that the
norm; i.e, are macros executed only when you invoke them
explicitly from the keyboard?

I know I should read a book, and I will in time. But I am
hoping this is a simple enough request that someone will
not mind filling in the blanks sooner than I digest a book.

Thanks.

This should fit the bill:
Sub RandMacro()
' Macro by Bernard V Liengme
Range(ActiveCell, ActiveCell.Offset(rowOffset:=10,
columnOffset:=0)).Select
Selection.FormulaR1C1 = "=RAND()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub


Just change the "rowOffset:=10" part to "rowOffset:=1000"

With Excel running and the required file open, copy the code above from you
email program.
Use Tools|Macro-Visual Basic Editor (or press ALT+F11)
In the VB Editor, click the Insert menu item and then the Module item from
the drop down menu.
Paste the code into the work space.
Return to Excel; move to the first cell where the RAND function is needed.
Use Tools|Macro-Macros and from the dialog box click the RandMacro entry

A quick way to copy formulas when you have a column of cells (Say A1:A100)
with something entered in each: type the formula in the cell next to the top
entry (in this case in B1) and now double click the fill handle (left black
box in lower right corner) of B1. Hey pesto! the stuff gets copied all the
way down.

Come back if you have questions. Reading a book is a great idea!

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
ups.com...
Can someone provide a VBA function to populate 1000's
of cells with a simple expression, for example =RAND()?

That is, I want to have a program/macro to populate the cells
instead of having to replicate them manually (e.g, by dragging),
which is painfully slow for such large numbers of cells. If
there is an alternative that is equally fast and easy to use,
I would appreciate hearing about it.

I would also appreciate step-by-step instructions for how to
enter the macro and how to execute it.

And since I am interested in an expression involving RAND(),
I would appreciate advice on how to prevent re-execution of
the macro each time the spreadsheet is recalculated -- other
than turning off automatic recalculation. (But if that is the
only way, I would appreciate confirmation.) Or is that the
norm; i.e, are macros executed only when you invoke them
explicitly from the keyboard?

I know I should read a book, and I will in time. But I am
hoping this is a simple enough request that someone will
not mind filling in the blanks sooner than I digest a book.

Thanks.

Assuming you don't want the values to change once you
place them, since a recalculate event will change them all.
This is macro I use to create 3 digit numbers (up to 3 digits)
as constants within a selection(s).

Sub Random003()
'David McRitchie 2002-08-02
Dim cell As Range
Randomize ' Initialize random-number generator.
For Each cell In Selection
cell.Value = Int(Rnd * 100)
Next cell
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

wrote in message ...
Can someone provide a VBA function to populate 1000's
of cells with a simple expression, for example =RAND()?

Thanks so much to all -- JE, Bernard and David and anyone
else that follows.

JE and David (or anyone else), can you comment on VBA Rnd
v. Excel RAND(). Are they the same algorithm? if not, is one
known to be better than the other?

Bernard, your instructions were impeccable. I can correct
the off-by-one error (rowOffset:=10 yields 11 cells, not 10).

How can I make 10 a "variable" -- an input or parameter to
the macro or a reference to a cell value? I can live with
editing the macro for now; or I can use David's idea with
"for each ... in selection". But I thought this should be a
simple embellish to Bernard's macro (famous last words!).

Borrowing FE's formula syntax (thanks very much) and taking
a wild guess (I know: RTFM!), the following did not work.
But it demonstrates what I want to do -- and of course, I am
open to any better ideas.

Dim mysz as long
mysz = "=IF($A$1 0, $A$1, 10)"
Range(ActiveCell, ActiveCell.Offset(rowOffset:=mysz, columnOffset:=0)).Select

I tried various incarnations with ".Formula" and ".Value", even
a hypothetical Value(...) function, to no avail. Obviously I need
to get that book or study one of the tutorial web sites.

In any case, your responses have been a great motivator, and
it gave me a head start. Thanks again.

" wrote:
Can someone provide a VBA function to populate 1000's
of cells with a simple expression, for example =RAND()?

Thanks again to those who provide some macro solutions.
I have a question about macro security.

When I entered those macro examples into one workbook,
I had no trouble running them.

But after I saved the workbook, when I reopen it, I get a
warning that macros are disabled due to my security settings.

I can follow the instructions to allow me to run these macros.
But it is tedious to flip-flop security settings and close/reopen
files.

Other than signing the macros (which I would have to figure
out), is there any way to permanently enable unsigned macros
in just this workbook without enabling macros in any other
workbook?

After all, that seems to be the behavior of Excel when I created
the macros at the outset.

How can I make 10 a "variable" -- an input or parameter to
the macro or a reference to a cell value?

Try this change to Bernard's code. (You might want to use Long instead of
Integer.)

Sub RandMacro()
' Macro by Bernard V Liengme

Dim I As Integer
Dim J As Integer
I = Range("a1").Value
J = Range("a2").Value

Range(ActiveCell, ActiveCell.Offset(rowOffset:=I, _
columnOffset:=J)).Select
Selection.FormulaR1C1 = "=RAND()"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

HTH
Regards,
Howard

"nomail1983" wrote in message
...
Thanks so much to all -- JE, Bernard and David and anyone
else that follows.

JE and David (or anyone else), can you comment on VBA Rnd
v. Excel RAND(). Are they the same algorithm? if not, is one
known to be better than the other?

Bernard, your instructions were impeccable. I can correct
the off-by-one error (rowOffset:=10 yields 11 cells, not 10).

How can I make 10 a "variable" -- an input or parameter to
the macro or a reference to a cell value? I can live with
editing the macro for now; or I can use David's idea with
"for each ... in selection". But I thought this should be a
simple embellish to Bernard's macro (famous last words!).

Borrowing FE's formula syntax (thanks very much) and taking
a wild guess (I know: RTFM!), the following did not work.
But it demonstrates what I want to do -- and of course, I am
open to any better ideas.

Dim mysz as long
mysz = "=IF($A$1 0, $A$1, 10)"
Range(ActiveCell, ActiveCell.Offset(rowOffset:=mysz,
columnOffset:=0)).Select

I tried various incarnations with ".Formula" and ".Value", even
a hypothetical Value(...) function, to no avail. Obviously I need
to get that book or study one of the tutorial web sites.

In any case, your responses have been a great motivator, and
it gave me a head start. Thanks again.

Well the advantages of using the VBA function instead of the Excel
function is that is would run faster and would not have to be converted
to constants.

You make the selection beforehand, so that you would not have to
change any range in the macro.

Usually when I write a macro with a selection, it is limited to the
used range. This macro is an exception. You must preselect the exact
range you want from one cell on up.

As far as being the same algorithm is concerned, I have no idea.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"nomail1983" wrote
JE and David (or anyone else), can you comment on VBA Rnd
v. Excel RAND(). Are they the same algorithm? if not, is one
known to be better than the other?

Tools Macros Security Settings

Set to Low

Close excel

Re Open excel and Security should still be set to low.



"nomail1983" wrote:

" wrote:
Can someone provide a VBA function to populate 1000's
of cells with a simple expression, for example =RAND()?

Thanks again to those who provide some macro solutions.
I have a question about macro security.

When I entered those macro examples into one workbook,
I had no trouble running them.

But after I saved the workbook, when I reopen it, I get a
warning that macros are disabled due to my security settings.

I can follow the instructions to allow me to run these macros.
But it is tedious to flip-flop security settings and close/reopen
files.

Other than signing the macros (which I would have to figure
out), is there any way to permanently enable unsigned macros
in just this workbook without enabling macros in any other
workbook?

After all, that seems to be the behavior of Excel when I created
the macros at the outset.

"exceluserforeman" wrote:
Tools Macros Security Settings
Set to Low
Close excel
Re Open excel and Security should still be set to low.

Obviously, you do not understand my question. What you
suggest will apply to all workbooks. (And Low would be
very dangerous, in that case.) I asked: how can I apply
the low security to "just this workbook without enabling
macros in any other workbook?".

Obviously, I could toggle security back to High after entering
the workbook. But then I have to toggle security back to Low
when I want to reopen the workbook later. As I wrote earlier,
that would be "tedious".

joe,
You are trying to fight the purpose of macro security.
Look into using SelfCert.exe, which is part of Office, IIRC and sign your
code.

NickHK

" wrote
in message ...
"exceluserforeman" wrote:
Tools Macros Security Settings
Set to Low
Close excel
Re Open excel and Security should still be set to low.

Obviously, you do not understand my question. What you
suggest will apply to all workbooks. (And Low would be
very dangerous, in that case.) I asked: how can I apply
the low security to "just this workbook without enabling
macros in any other workbook?".

Obviously, I could toggle security back to High after entering
the workbook. But then I have to toggle security back to Low
when I want to reopen the workbook later. As I wrote earlier,
that would be "tedious".

"L. Howard Kittle" wrote:
How can I make 10 a "variable" -- an input or parameter to
the macro or a reference to a cell value?
[....]
Try this change to Bernard's code.
[....]
Dim I As Integer
Dim J As Integer
I = Range("a1").Value
J = Range("a2").Value
Range(ActiveCell, ActiveCell.Offset(rowOffset:=I, _
columnOffset:=J)).Select

Thanks for the hint. To address the purpose implied by
my pseudocode, it is at least as simple as (also correcting
the original off-by-one error):

Dim sz as Integer
sz = Range("A1").Value
if sz <= 1 then sz = 10
Range(ActiveCell, ActiveCell.Offset(rowOffset:=sz-1, [...])

Great stuff! Thanks all for your examples and patience.

setting security to medium is usually an acceptable compromise. You would
get a warning and choose whether to disable or not.

--
Regards,
Tom Ogilvy


" wrote
in message ...
"exceluserforeman" wrote:
Tools Macros Security Settings
Set to Low
Close excel
Re Open excel and Security should still be set to low.

Obviously, you do not understand my question. What you
suggest will apply to all workbooks. (And Low would be
very dangerous, in that case.) I asked: how can I apply
the low security to "just this workbook without enabling
macros in any other workbook?".

Obviously, I could toggle security back to High after entering
the workbook. But then I have to toggle security back to Low
when I want to reopen the workbook later. As I wrote earlier,
that would be "tedious".

That's fine, except that I am trying to distribute the spreadsheet with
macros to a my team, and selfcert.exe warns: "Office will only allow you to
trust a self-signed certificate on the machine on which it was created."
Most users to whom the spreadsheet would go are NOT admins nor PowerUsers on
their machines so apparently they cannot change the security levels. How do
I deal with that?

Thanks!
Caveman

"NickHK" wrote:

joe,
You are trying to fight the purpose of macro security.
Look into using SelfCert.exe, which is part of Office, IIRC and sign your
code.

NickHK

" wrote
in message ...
"exceluserforeman" wrote:
Tools Macros Security Settings
Set to Low
Close excel
Re Open excel and Security should still be set to low.

Obviously, you do not understand my question. What you
suggest will apply to all workbooks. (And Low would be
very dangerous, in that case.) I asked: how can I apply
the low security to "just this workbook without enabling
macros in any other workbook?".

Obviously, I could toggle security back to High after entering
the workbook. But then I have to toggle security back to Low
when I want to reopen the workbook later. As I wrote earlier,
that would be "tedious".

Caveman,
I do not have this with Office 2000, so I can only guess it's a feature of
later versions.
If it is, then it makes the point of signing your WB pretty useless if no
other machine will honour your certificate.
I guess you'd have to buy a certificate.
Or speak with the Admin to relax the Office policy.

NickHK

"Caveman" wrote in message
...
That's fine, except that I am trying to distribute the spreadsheet with
macros to a my team, and selfcert.exe warns: "Office will only allow you
to
trust a self-signed certificate on the machine on which it was created."
Most users to whom the spreadsheet would go are NOT admins nor PowerUsers
on
their machines so apparently they cannot change the security levels. How
do
I deal with that?

Thanks!
Caveman

"NickHK" wrote:

joe,
You are trying to fight the purpose of macro security.
Look into using SelfCert.exe, which is part of Office, IIRC and sign
your
code.

NickHK

"
wrote
in message ...
"exceluserforeman" wrote:
Tools Macros Security Settings
Set to Low
Close excel
Re Open excel and Security should still be set to low.

Obviously, you do not understand my question. What you
suggest will apply to all workbooks. (And Low would be
very dangerous, in that case.) I asked: how can I apply
the low security to "just this workbook without enabling
macros in any other workbook?".

Obviously, I could toggle security back to High after entering
the workbook. But then I have to toggle security back to Low
when I want to reopen the workbook later. As I wrote earlier,
that would be "tedious".