Error handling in User-defined functions

ccordner

Active Member
Joined
Apr 28, 2010
Messages
355
I have a couple of functions designed to take some variables and return an array, for example:

VBA Code:
Function getDiagrams(strRawData As String)

'Initialise the error boolean
booFnError = False

'Error handler
On Error GoTo InvalidData

'Split the raw data into an array, one line for each diagram
arrRawData() = Split(strRawData, vbCrLf)

'Redimension the diagrams array
ReDim arrDiagrams(8, UBound(arrRawData()))

'Transpose each line of raw data to a line in the diagrams array
For lngArrCount = 0 To UBound(arrRawData())
        
    'Check there is something to copy
    If Len(arrRawData(lngArrCount)) > 0 Then

        'Create a one dimensional array to hold the data
        arrLine = Split(arrRawData(lngArrCount), vbTab)
        
        'Get and format string variables
        strLocName = arrLine(1)
        strPosInd = arrLine(4)
        lngVehicles = Right(strPosInd, Len(strPosInd) - InStr(strPosInd, "/"))
        strPosInd = Left(strPosInd, InStr(strPosInd, "/") - 1)
        If Left(strPosInd, 1) = 1 And Right(strPosInd, Len(strPosInd) - InStr(strPosInd, "-")) = lngVehicles Then
            strPosInd = ""
        End If
                
        'Transpose the data itself
        arrDiagrams(0, lngArrCount) = strLocName
        arrDiagrams(1, lngArrCount) = arrLine(2) 
        arrDiagrams(2, lngArrCount) = arrLine(3) 
        arrDiagrams(4, lngArrCount) = arrLine(5) 
        arrDiagrams(5, lngArrCount) = arrLine(6) 
        'arrDiagrams(6, lngArrCount) = arrLine(6)
        arrDiagrams(7, lngArrCount) = arrLine(7) 
        arrDiagrams(8, lngArrCount) = arrLine(8) 
    
    End If

Next

'Return the diagrams array
getDiagrams = arrDiagrams()
Exit Function

'If invalid data is provided
InvalidData:
MsgBox ("It looks like the data you are pasting hasn't been correctly formatted. Please try again.")
booFnError = True

End Function

This is called from the main Sub as below:
VBA Code:
'Use the raw data to get a list of Diagrams
arrDiagrams = getDiagrams(strRawData)

If booFnError = True Then
    'Exit the routine if invalid data has been provided.
    txtDiagrams.Text = ""
    Exit Sub
End If

I'm struggling with the error handling. If it doesn't work (the wrong data is pasted), then it gives me the error message, but then gives a type mismatch on "arrDiagrams = getDiagrams(strRawData)". Any ideas what I'm doing wrong?

Thanks
Chris
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
One problem is the scope of your variable booFnError - its scope is procedure level. Once a procedure has finished, all variables are dumped, so to speak. You could move the boolean to the module level to see if that helps, but you didn't say which line was raising your error message so I'm not sure if that is the answer. Likely though, because booFnError in the calling sub will always be False by default. Probably a good idea for you to research "vba variable scope" - it's a bit too involved to cover in a post.

IMO you should always declare the return value of a function, if for no other reason than to remove ambiguity.
 
Upvote 0
Solution
Hi, thanks for that - I don't think it's the variable scope - I declared it as a public - but I think you're right- just create a blank array or something to return it will avoid the error.

Thanks
Chris
 
Upvote 0
I declared it as a public
Declaring a public variable inside of a procedure does nothing different. The scope of a procedure level variable is only to that procedure. When proc1 terminates, so does your variable. In proc2 a variable with the same name is false because that is the default value of a boolean. So your test for true in proc2 will never be true. Putting your variable at the module level would be the simple fix.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top