Type mismatch error and then continues executing

[momer123]

I am using a function to randomize the items in an array,

Dim dxr() As Variant 'declared globally
dim number as integer

public sub proc1()
ReDim dxr(number)


If number > 0 Then dxr = RandomNumbers(number, 1, number) ' ERROR occurs here on this line

end sub

I get a Runtime error 13- type mismatch randomly(sometimes it works without errors), and when I click debug and then "continue" in the popup window, the program runs again. I am calling the same procedure in >10000 iterations.

Does anybody have an idea about this? I have pasted the RandomNumbers function below.

Thanks.

Public Function RandomNumbers(Upper As Integer, _
Optional Lower As Integer = 1, _
Optional HowMany As Integer = 1, _
Optional Unique As Boolean = True) As Variant
'*******************************************************
'This Function generates random array of
'Numbers between Lower & Upper
'In Addition parameters can include whether
'UNIQUE values are required

'Note the Result is INCLUSIVE of the Range

'Debug Example:
'x = RandomNumbers(49, 1, 7)
'For n = LBound(x) To UBound(x): Debug.Print x;: Next n
'WARNING HowMany MUST be greater than (Higher - Lower)
'******************************************************

On Error GoTo LocalError
If HowMany > ((Upper + 1) - (Lower - 1)) Then Exit Function
Dim x As Integer
Dim n As Integer
Dim arrNums() As Variant
Dim colNumbers As New Collection

ReDim arrNums(HowMany - 1)
With colNumbers
'First populate the collection
For x = Lower To Upper
.Add x
Next x
For x = 0 To HowMany - 1
n = RandomNumber(0, colNumbers.Count + 1)
arrNums(x) = colNumbers
If Unique Then
colNumbers.Remove n
End If
Next x
End With
Set colNumbers = Nothing
RandomNumbers = arrNums
Exit Function
LocalError:
'Justin (just in case)
RandomNumbers = ""
End Function

 

[tsh73]

I wonder if it errors then line
RandomNumbers = ""
(in error handler) is executed.
Check this out: put in your function
RandomNumbers = ""
exit function
immediately in the beginning, does it reproduce problem?

Another thing to check if it errors if line
If HowMany > ((Upper + 1) - (Lower - 1)) Then Exit Function
is executed. I guess in that case we do not set return value at all.

 

[JoeAtWork]

I would comment out:

On Error GoTo LocalError

That way, your code will break inside the RandomNumber function, and you will see exactly where the problem is. Right now, any error that occurs will jump down to your error handler, which returns a string. Since your calling procedure is expecting an array, you are getting the mismatch error.

In fact, this type of error handler is worse than not having any error handling at all. It's returning a random value (and not even a type of value that your calling routine is expecting).

An error handler should do the following:
1. Try to handle the error in an appropriate manner. At this stage you try to catch errors that might be expected to happen - and if possible fix them and resume where you left off. Telling it to return "any old value" just so you avoid seeing an error message is basically telling the function to lie to you.
2. If it can't handle the error, escalate it up (i.e. raise another error) - this gives the calling procedure a chance to handle the error in a graceful manner.

Another suggestion I would make is not to use Variant types when you know your variable should always be a particular type (an integer, for example). Using Variants is another way a bug can be hidden from you, making it harder to track down a logic error.

The principal here is that it is better to see an error message, or even have the program crash, rather than pass on "magical" values to your calling procedures. At least you will be aware there is a problem.

Sorry if I sound like I'm giving you a lecture. But I've been the one who's had to fix functions that were returning zeroes (incorrectly) because the original programmer dealt with (ignored) all errors with "On Error Resume Next". Meanwhile the company billing was wrong for months cause nobody knew a problem existed.

 

[momer123]

Thanks JoeAtWork, that was great advice. I will try commenting that line of code, and find where the problem is and let you know.