initialize numeric array to non-numeric values?

Topic

New Reply

I want to use the MODE function in a vba script, but I need to apply to a range that goes across multiple worksheets. MODE doesn’t work that way, but I found a solution on the web that goes like this:

Function GetMode()
Dim FS As Integer, LS As Integer, i As Integer, SCnt As Integer, MyArray() As Double
FS = Sheets(“First Sheet”).Index
LS = Sheets(“Last Sheet”).Index
SCnt = LS – FS
Redim MyArray(0 To SCnt)
For i = FS To LS
MyArray(i – FS) = Sheets(i). Range(“K5”)
Next i
GetMode = WorksheetFunction.Mode(MyArray)
End Function

The only problem with this is that where there are blank cells, the MODE function should ignore the contents of that cell, i.e. it only considers cells with numeric values. However with this code “MyArray” is initialized to zeros and the array value stays zero when the cell K5 is blank on a particular sheet. This is giving me an incorrect result for this function since most of the cells will be blank.

How can I set the array value to something that will be skipped by the MODE function when the K5 cell is blank?

Reply | Quote

Replies

Declare MyArray as Variant instead of Double:

Dim FS As Integer, LS As Integer, i As Integer, SCnt As Integer, MyArray() As Variant

Reply | Quote

Declare MyArray as Variant instead of Double:

Dim FS As Integer, LS As Integer, i As Integer, SCnt As Integer, MyArray() As Variant

Excellent, thanks Hans. That seems to work exactly as required.

I guess it’s obvious when you know how. I will note it down for the future reference.

Reply | Quote