Microsoft Office Tutorials and References

In Depth Information

**Some Useful Worksheet Functions**

inside the loop performs a check to see whether the cell being checked is the cell that contains

the function. If so, that cell is ignored to avoid a circular reference error.

You can easily modify this function to perform other cross-worksheet calculations, such

as minimum, average, sum, and so on.

Returning an array of nonduplicated random integers

The function in this section,
RandomIntegers
, returns an array of nonduplicated integers. The

function is intended to be used in a multicell array formula.

{=RandomIntegers()}

Select a range and then enter the formula by pressing Ctrl+Shift+Enter. The formula returns an

array of nonduplicated integers, arranged randomly. For example, if you enter the formula into a

50-cell range, the formulas will return nonduplicated integers from 1 to 50.

The code for
RandomIntegers
follows:

Function RandomIntegers()

Dim FuncRange As Range

Dim V() As Variant, ValArray() As Variant

Dim CellCount As Double

Dim i As Integer, j As Integer

Dim r As Integer, c As Integer

Dim Temp1 As Variant, Temp2 As Variant

Dim RCount As Integer, CCount As Integer

‘ Create Range object

Set FuncRange = Application.Caller

‘ Return an error if FuncRange is too large

CellCount = FuncRange.Count

If CellCount > 1000 Then

RandomIntegers = CVErr(xlErrNA)

Exit Function

End If

‘ Assign variables

RCount = FuncRange.Rows.Count

CCount = FuncRange.Columns.Count

ReDim V(1 To RCount, 1 To CCount)

ReDim ValArray(1 To 2, 1 To CellCount)

‘ Fill array with random numbers

‘ and consecutive integers

For i = 1 To CellCount

ValArray(1, i) = Rnd

ValArray(2, i) = i