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

Search JabSto ::

Custom Search