ByRef Argument Type Mismatch in Path Creation Function

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
Hi all,

I have two functions that I routinely use to check for the existence of paths to files ... I am attempting to debug another sub, and so was trying to compile my entire project in the hope of finding flaws in my code. In the block below, I am receiving a warning "Compile Error: ByRef argument type mismatch".

I am confident the cause of the issue is the calls to the date and time functions, but these functions usually work? Also, how can I rewrite the strings/function so that I can address this issue? Any help appreciated?

Here is the code that is breaking: (on "Call CheckForPaths(archivePath1,...)")

Code:
Dim archivePath1, archivePath2, archivePath3, archivePath4, archivePath5 As String
archivePath1 = "C:\\Users\Alex\DB\Extracts\" & Year(Now) & "\"
archivePath2 = archivePath1 & MonthName(Month(Now)) & "\"
archivePath3 = archivePath2 & "Extract_File " & Format(Now(), "yyyymmdd.hhnnss") & ".xlsx"
   Call CheckForPaths(archivePath1, archivePath2)
   Call CheckForPath(archivePath3)

And here are the functions:

Code:
Public Sub CheckForPaths(sPath1 As String, sPath2 As String)
    If Len(Dir(sPath1, vbDirectory)) = 0 Then
        MkDir (sPath1)
    End If
                        
    If Len(Dir(sPath2, vbDirectory)) = 0 Then
        MkDir (sPath2)
    End If
End Sub
Public Sub CheckOnePath(sPath3 As String)
    If Len(Dir(sPath3, vbDirectory)) = 0 Then
        MkDir (sPath3)
    End If
                        
End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,184
believe it or not
this
Dim archivePath1, archivePath2, archivePath3, archivePath4, archivePath5 As String
gives you one string and 4 variants

you've defined your subs to take strings as arguments
but you're passing variants
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
901
You need to define EACH variable type
Rich (BB code):
Dim archivePath1 As String, archivePath2 As String, archivePath3 As String, archivePath4 As String, archivePath5 As String 
 

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
203
believe it or not
this
Dim archivePath1, archivePath2, archivePath3, archivePath4, archivePath5 As String
gives you one string and 4 variants

you've defined your subs to take strings as arguments
but you're passing variants

That's interesting ...

Thanks! Everything's good to go!
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
For future reference, you could also consider an array of strings:
Code:
Dim archivePaths(1 to 5) As String
archivePaths(1) = "C:\\Users\Alex\DB\Extracts\" & Year(Now) & "\"
archivePaths(2) = archivePaths(1) & MonthName(Month(Now)) & "\"
archivePaths(3) = archivePaths(2) & "Extract_File " & Format(Now(), "yyyymmdd.hhnnss") & ".xlsx"
   Call CheckForPaths(archivePaths(1), archivePaths(2))
   Call CheckForPath(archivePaths(3))
 

Forum statistics

Threads
1,140,924
Messages
5,703,182
Members
421,280
Latest member
Jaycee01

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
Top