run-time error '91': Object variable or With block variable not se

[Guest]

i thought these two sub's were identical in calling the function below, but
TestLaunchFileSelector() cases run-time error '91': Object variable or With
block variable not set, whereas TestLaunchFileSelector2() runs fine.

WHY?

Sub TestLaunchFileSelector()
Dim DatabaseFolder As String
DatabaseFolder = "D:\Documents\Ben's Documents\powerpoints\Prayers"
MsgBox (LaunchFileSelector(DatabaseFolder, True))
End Sub

Sub TestLaunchFileSelector2()
MsgBox (LaunchFileSelector("D:\Documents\Ben's
Documents\powerpoints\Prayers", True))
End Sub

Function LaunchFileSelector(Folder, SortMethod As Boolean) As String
'Returns selected filename or "" if none selected, or if cancelled
'SortMethod = True Sort by Title, SortMethod = False Sort By FileName
Dim fs, a, f
Dim s As Integer
Dim FileNamesList As Variant
Dim FileTitlesList() As String
Load FileSelector
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists(Folder) Then
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.NameSpace(Folder)
s = 0
For Each FileName In objFolder.Items
If InStr(1, FileName, ".ppt") Or InStr(1, FileName, ".pps") Then
FileSelector.FilesList.AddItem FileName
FileSelector.FilesList.List(s, 1) =
objFolder.GetDetailsOf(FileName, 10)
s = s + 1
End If
Next FileName
End If

If SortMethod Then
FileNamesList = FileSelector.FilesList.List
FileNamesList = SortFilesList(FileNamesList, True)
FileSelector.FilesList.Clear
FileSelector.FilesList.List = FileNamesList
FileSelector.Sorting.Caption = "Sort by FileName"
Else
FileSelector.Sorting.Caption = "Sort by Title"
End If

FileSelector.Show

'test if an item has been selected
If FileSelector.Tag = "Selected" Then
LaunchFileSelector = FileSelector.FilesList.Value
Else
LaunchFileSelector = ""
End If
FileSelector.Tag = "Not Selected"
Unload FileSelector
End Function

 

[Andy]

Ben -
When you don't declare a variable's type, VBA assigns it as a variant
type. Likewise, when you don't tell VBA the way a parameter is passed,
it is passed by reference (ByRef). Your code will work if Folder is
passed by value (ByVal) because passing ByVal creates a copy of the
parameter in memory and ByRef creates a pointer to where the variable is
in memory. I can not answer why a literal string is passed differently
than a string variable, but the ByRef passing confuses the code for
shell.application. It is expecting a string. The variant "Folder"
which is a variant, can change to whatever is being sent. I suspect the
literal string is being passed as some kind of character array whereas
your string DatabaseFolder is being passed as a string. Both are
printed and typed as strings, but when the shell.application runs, it is
probably looking for an actual string (difference between C and VBA).

Either way, I personally recommend declaring all your variables
explicitly such as strFolder as string and and always passing
parameters ByRef for a reason. It will alleviate these types of issues.

To specifically answer your question, change your function header to
this:
Function LaunchFileSelector(ByVal Folder, SortMethod As Boolean) As
String

Hope that made sense. If you are completely confused by this whole
thread, post back and I'll try to explain ByRef and ByVal better with an
example.

Andy