Bill Nelson
asked on
VBA Error 13 Type Mismatch help
When running a report, I am getting a type Mismatch Error 13.
Debug highlights varParse in the function fExtractLastFolder.
Here is the function:
If I select End instead of Debug, the report is open and perfect.
In Debug, varParse is shown to be "1" which is the last folder.
Any chance somebody can help me with this? It's urgent...
Select any username and password is Welcome...
Thank you very much!
Bill
Experts-Exchange-Help-v-1.zip
Debug highlights varParse in the function fExtractLastFolder.
Here is the function:
Option Compare Database
Option Explicit
Public Function fExtractLastFolder(FPath As String) As Long
Dim varParse As Variant
If Right(FPath, 1) = "\" Then
FPath = Left(FPath, Len(FPath) - 1)
Else
FPath = FPath
End If
varParse = Split(FPath, "\")
fExtractLastFolder = varParse(UBound(varParse))
End Function
If I select End instead of Debug, the report is open and perfect.
In Debug, varParse is shown to be "1" which is the last folder.
Any chance somebody can help me with this? It's urgent...
Select any username and password is Welcome...
Thank you very much!
Bill
Experts-Exchange-Help-v-1.zip
try this instead.
Option Explicit
Public Function fExtractLastFolder(FPath As String) As String
Dim varParse() As String
If Right(FPath, 1) = "\" Then
FPath = Left(FPath, Len(FPath) - 1)
Else
FPath = FPath
End If
varParse = Split(FPath, "\")
fExtractLastFolder = varParse(UBound(varParse))
End Function
Bill
Which line in the function is highlighted?
Which line in the function is highlighted?
Hi,
Your function must return a long.
But your expression "varParse(UBound(varParse) )" return a string, in theory, there is a type mismatch here.
In your particular case, I guess that the FPath parameter doesn't always end with an "\" followed by a number, thus implicit conversion fail.
Either make sur the FPath parameter is in a correct state before calling the function (regex can be handy for that), or write an error handler.
Your function must return a long.
But your expression "varParse(UBound(varParse)
In your particular case, I guess that the FPath parameter doesn't always end with an "\" followed by a number, thus implicit conversion fail.
Either make sur the FPath parameter is in a correct state before calling the function (regex can be handy for that), or write an error handler.
ASKER
Hi Ryan,
Thank you so much for answering my question.
If I change the variant to string, the report opens up with no errors, However, I cannot compile as I get the following:
(Compile error Expected Array) and UBound is highlighted.
If I just comment out the Dim VarParse by Variant I get the error:
Compile error, variable not defined
Thank you so much for answering my question.
If I change the variant to string, the report opens up with no errors, However, I cannot compile as I get the following:
(Compile error Expected Array) and UBound is highlighted.
If I just comment out the Dim VarParse by Variant I get the error:
Compile error, variable not defined
ASKER
HI Fabrice
Thank you so much for your suggestions and taking time to try and help me out. I am so new to VBA and do not really know anything about regex or writing error handling for this type of thing...would you be able to help me write it...I am just really on a short time frame :(
In the meantime I will Google it to see if I can get it to work....
Again thanks for helping me out !!!
Thank you so much for your suggestions and taking time to try and help me out. I am so new to VBA and do not really know anything about regex or writing error handling for this type of thing...would you be able to help me write it...I am just really on a short time frame :(
In the meantime I will Google it to see if I can get it to work....
Again thanks for helping me out !!!
ASKER
HI Norie,
Thanks for offering to help...that is really kind of you. I have my laptop here at home and of course I forgot to bring the power cord and my battery just died....so I cannot remember which line was highlighted... I am so sorry....it just figures when you are trying to get something done fast it always happens that you forget something so important!!!
Thanks for offering to help...that is really kind of you. I have my laptop here at home and of course I forgot to bring the power cord and my battery just died....so I cannot remember which line was highlighted... I am so sorry....it just figures when you are trying to get something done fast it always happens that you forget something so important!!!
ASKER
HI Norie. ...in my original post it says varParse is highlighted but I cannot remember which line...
ASKER
HI Fabrice,
So by regex do you mean regular expressions?
FPATH can end with a letter or number, so are you suggesting something like:
Dim strFPath As String
Dim lngFPath As Long
FPATH = strFPath
IIf([FPath]="Z", strFPath, lngFPath)
As you can probably tell, this is a lot like dressing up in daddie''s suit and shoes...I am trying but, we'll you can see quite clearly I bet that I really do not have a clue...
So by regex do you mean regular expressions?
FPATH can end with a letter or number, so are you suggesting something like:
Dim strFPath As String
Dim lngFPath As Long
FPATH = strFPath
IIf([FPath]="Z", strFPath, lngFPath)
As you can probably tell, this is a lot like dressing up in daddie''s suit and shoes...I am trying but, we'll you can see quite clearly I bet that I really do not have a clue...
From my understanding, your function recieve a string as parameter, wich is a folder path to be more precise.
It looks like you want the last folder in the path (let me know if I'm wrong).
In its simple form, your function should be :
Notes for best practices:
Never use variant data types, unless you have a good reason to do so.
It looks like you want the last folder in the path (let me know if I'm wrong).
In its simple form, your function should be :
3:Public Function fExtractLastFolder(FPath As String) As String
Dim parts() as String '// declare a dynamic array
parts = split(FPath, "\") '// fill up the array
fExtractLastFolder = parts(Ubound(parts))
End Function
Now, if the last folder is expected to be a number, you'll have to handle that after calling the function.Notes for best practices:
Never use variant data types, unless you have a good reason to do so.
ASKER
HI Fabric,
My problem is the folder will either be the letter Z or a number...so I am not sure how to write that, which is why I was writing an IIF statement....but obviously that must be the wrong way to do it...
So would I also Dim for a number?
My problem is the folder will either be the letter Z or a number...so I am not sure how to write that, which is why I was writing an IIF statement....but obviously that must be the wrong way to do it...
So would I also Dim for a number?
Make things simple:
A function or procedure or class, should have one and only one responsibility (this is also called SRP: Single Reponsibility Principle).
If your function extract the last folder and check that it is a number or a letter, that's two responsibilities, obviously one too many.
Ehence why, by respect to the SRP, the letter or number checkup should be done outside of the function.
A function or procedure or class, should have one and only one responsibility (this is also called SRP: Single Reponsibility Principle).
If your function extract the last folder and check that it is a number or a letter, that's two responsibilities, obviously one too many.
Ehence why, by respect to the SRP, the letter or number checkup should be done outside of the function.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Bill
my function works well before posting
my function works well before posting
ASKER
Just heading to work...I will respond asap....again thank you so much everyone!!!!
ASKER
This worked and compiled with no problem. Thank you so much Gustav !!!
You are welcome!
/gustav
/gustav
Dim varParse As Variant