I have a couple of functions designed to take some variables and return an array, for example:
This is called from the main Sub as below:
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
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