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
Bill NelsonITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
quick try, try to remove the line:

Dim varParse As Variant
0
Ryan ChongCommented:
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

0
NorieVBA ExpertCommented:
Bill

Which line in the function is highlighted?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Fabrice LambertFabrice LambertCommented:
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.
0
Bill NelsonITAuthor Commented:
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
0
Bill NelsonITAuthor Commented:
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 !!!
0
Bill NelsonITAuthor Commented:
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!!!
0
Bill NelsonITAuthor Commented:
HI Norie. ...in my original post it says varParse is highlighted but I cannot remember which line...
0
Bill NelsonITAuthor Commented:
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...
0
Fabrice LambertFabrice LambertCommented:
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.
0
Bill NelsonITAuthor Commented:
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?
0
Fabrice LambertFabrice LambertCommented:
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.
0
Gustav BrockCIOCommented:
Your function is correct, except that it tries to return a Long and not a String.
Also, it can be reduced a little, thus:

Public Function fExtractLastFolder(FPath As String) As String

    Dim varParse As Variant
  
    If Right(FPath, 1) = "\" Then
        FPath = Left(FPath, Len(FPath) - 1)
    End If
    
    varParse = Split(FPath, "\")
      
    fExtractLastFolder = varParse(UBound(varParse))
 
End Function

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongCommented:
@Bill

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

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.