Link to home
Start Free TrialLog in
Avatar of Bill Nelson
Bill NelsonFlag for Canada

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:
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

Open in new window


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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

quick try, try to remove the line:

Dim varParse As Variant
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

Open in new window

Avatar of Norie
Norie

Bill

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.
Avatar of Bill Nelson

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
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 !!!
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!!!
HI Norie. ...in my original post it says varParse is highlighted but I cannot remember which line...
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...
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 :
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

Open in new window

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.
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?
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.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Bill

my function works well before posting
Just heading to work...I will respond asap....again thank you so much everyone!!!!
This worked and compiled with no problem. Thank you so much Gustav !!!
You are welcome!

/gustav