Passing Workbook as Optional Parameter for Function

CMIIandExcel · Mar 31, 2011

All

[FONT=&quot]I am trying to pass a workbook as an optional Parameter in a vba function, the code below does not work (I am miss using the 'NOTHING' statement).

I left it in as it was where i got to trying to resolve the problem myself.

For convenience i have included a small test routine at the bottom of the code.[/FONT]

Code:

Public Function PathFind(ByRef strPath As String, Optional wbBook As Workbook = Nothing) As Boolean
Dim strValue
Dim i As Long
Dim wb As Workbook
If wbBook = Nothing Then
Set wb = ThisWorkbook
Else
Set wb = wbBook
End If
PathFind = False
strValue = Application.wb.FullName
If InStr(1, strValue, "\", vbTextCompare) = 0 Then
PathFind = False
Exit Function
Else
For i = Len(strValue) To 1 Step -1
If Mid(strValue, i, 1) = "\" Then
strPath = Left(strValue, i)
PathFind = True
Exit Function
End If
Next i
End If
End Function

Sub Test()
Dim str As String

If PathFind(str) Then
MsgBox "Path Found is : " & str
Else
MsgBox "Path Not Found : " & str
End If
End Sub

Any help would be warmly welcome

Regards

Michael

RichardMGreen · Mar 31, 2011

From memory, you don't need the "= Nothing" bit after wbook in your parameter list.
Try removing it and see what happens. IIRC, if you don't pass the parameter it ends up as Nothing anyway.

CMIIandExcel · Mar 31, 2011

Richard

Thank you for your reply, it made me have another try and i stumbled on the right method.

I had to change the = to and IS in the first if statement.
I also had to remove the application reference where i set the strValue to the FullName

For others i have included the working code below

Thanks again for the reply

Regards

Michael

Code:

Public Function PathFind(ByRef strPath As String, Optional wbBook As Workbook = Nothing) As Boolean
Dim strValue
Dim i As Long
Dim wb As Workbook
If wbBook Is Nothing Then
   Set wb = ThisWorkbook
Else
   Set wb = wbBook
End If
PathFind = False
strValue = wb.FullName
If InStr(1, strValue, "\", vbTextCompare) = 0 Then
   PathFind = False
   Exit Function
Else
   For i = Len(strValue) To 1 Step -1
      If Mid(strValue, i, 1) = "\" Then
         strPath = Left(strValue, i)
         PathFind = True
         Exit Function
      End If
   Next i
End If
End Function

RoryA · Mar 31, 2011

Or:

Code:

Public Function PathFind(ByRef strPath As String, Optional wbBook As Workbook) As Boolean
   Dim strValue
   Dim i As Long
   Dim wb As Workbook
   If wbBook Is Nothing Then
      Set wb = ThisWorkbook
   Else
      Set wb = wbBook
   End If
   strPath = wb.Path
   PathFind = strPath <> ""
   If PathFind Then strPath = strPath & "\"
End Function

CMIIandExcel · Mar 31, 2011

Rory

Much more efficent, thanks for that

Regards

Michael

Passing Workbook as Optional Parameter for Function

CMIIandExcel

Board Regular

Excel Facts

RichardMGreen

Well-known Member

CMIIandExcel

Board Regular

RoryA

MrExcel MVP, Moderator

CMIIandExcel

Board Regular

Similar threads

Forum statistics

Share this page

Passing Workbook as Optional Parameter for Function

CMIIandExcel

Board Regular

Excel Facts

RichardMGreen

Well-known Member

CMIIandExcel

Board Regular

RoryA

MrExcel MVP, Moderator

CMIIandExcel

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock