VBA - difference between numeric variable = 0 and default val?

[Guest]

Hi, I am trying to find a way of determining whether variables (in a large
array of 35000 data points, defined as Single) are actually blank or are
really zero.

I appreciate that numeric variables default to 0. In my case, I dimension my
large array as single, then set it equal to a range. The range will contain
empty cells (perhaps up to half the time), signifying missing data, but may
also contain data values that are 0. I want to distinguish between these
possibilities. I appreciate that I could declare the array as variant or
string, then test to see if individual elements are blank or null string,
then convert as necessary to numeric variable, perform my operations and so
on but this would seem to be a very inefficient process.
Is there some more efficient way of determining whether a numeric variable
has been changed from its default initialisation value? Or is there some way
that I can simply instruct VBA to set all blanks in the range I am reading in
(which is large) to some other value? Or can I change the default value that
VBA gives to a particular variable type?
Any help, as always, would be most gratefully appreciated.
Many thanks, Boris.

 

[Bob Phillips]

Maybe this will start you off

Public Sub Test()
Dim rng As Range
Dim ary
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then rng.Value = "~"
ary = Selection
If Not Application.CountIf(Selection, "~~") > 0 Then
MsgBox "some blanks"
End If
End Sub


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Dave Peterson]

I've always tested with something like:

Option Explicit
Sub testme()
Dim myArr As Variant
Dim iCtr As Long

With ActiveSheet
myArr = .Range("a1:a5").Value
End With

For iCtr = LBound(myArr, 1) To UBound(myArr, 1)
If Application.IsNumber(myArr(iCtr, 1)) Then
MsgBox "A number"
Else
MsgBox "not a number"
End If
Next iCtr

'or

For iCtr = LBound(myArr, 1) To UBound(myArr, 1)
If IsEmpty(myArr(iCtr, 1)) = False _
And IsNumeric(myArr(iCtr, 1)) Then
MsgBox "A number"
Else
MsgBox "not a number"
End If
Next iCtr

End Sub

 

[Guest]

Dear Bob,
Many thanks for this. I was hoping for a statement like "DefaultSingle=9999"
(or some other number I know I am not using) to avoid having to test the
entries but I guess one cannot have everything.
I am still struggling to get my data into arrays (I was hoping to be able to
dimension the arrays to the size I need every time - which ranges from about
34000 rows to just over 35000 depending on the data set) but VBA didn't like
that too much so I am dimensioning them to the largest size I will need. Now
(currently) when I set my array to a range, I get an error saying "can't
assign to array" but I am sure I will get there in the end (I am probably not
counting and mismatching the sizes or something - or I need to have my array
as a variant array, not a double or date array).
Thanks for all you help.
Best wishes, Boris

 

[Guest]

Dear Dave,
Many thanks for your suggestion. I was, of course, hoping not to havbe to
test the variables before reading them in to my array but if, as it seems, I
will need teed so to do, so be it.
Many thanks again and best wishes, Boris.

 

[Guest]

Dear Bob
I have just posted this further question to you and Niek on the thread about
approximate matches in VBA but though I might solicit your response by
posting it here too...
After all your help, I am still struggling!
I have now tried setting up 2 large arrays, one for the date/time and one
for my data. My problem is this: if I set my array up to a specific size and
type (Dim TimeSArray(34944) as Double for my current data - as VBA objects
to me using a variable to set the array size), I get the error that I cannot
set the
array to a range (a6:a34950) (the error is a compile error "can't assign to
array"). If I set the array up as variant, and do not specify the size, I can
set it equal to the range I want but then the line that calls BSearchNumber
gives me a compile error ("type mismatch: array or user defined type
expected"). I am calling BSearchNumber with a line like:
SomEInteger=BSearchNumber(TimeSArray, TimeSArray(j-1)+SomeConst) and was
hoping to loop through this for my 34000 searches (j is the loop counter).
I am sure I am making a very rudimentary error but so far have been unable
to spot it... Any suggestions? This bit of code is beginning to drive me up
the wall....
Many hanks for all your help.