You receive the error because you are sending a value; i.e. your file name.
If the function requires a variable, then you need to send a variable that you declared in your function. When your function uses ByRef, then you need to send a variable. To get around the error and to use the function as is, just declare a variable and give it a value before sending it to the function. The function can then change the value of that variable, but it does not sound like it will. So, that function probably could have been more efficiently written by using ByVal. Unless the programmer's intent was to optionally change the value of the variable.
When you use the ShellExecute API you can just use "open", "print", or "explore" for the second parameter. In your case, you just want to use "open" for the operation. The operating system will use the program that is designated in the windows registry to open a file with that file's extension.
If you use the VB Shell function, you then must use the actual name of the program plus any parameters that may accompany the file. You need to use a space between the program name and the parameter; hence, chr$(32).
You have more options, if you use the ShellExecute API. But it's more complicated. The following example uses both the ShellExecute API and the VB Shell function. The VB Shell function just performs a limited set of the options of the ShellExecute API.
Option Explicit
Private Const SW_SHOWNORMAL = 1
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Sub Form_Load()
Dim lngReturn As Long
Dim dblReturn As Double
lngReturn = LoadUserFile(Me.hwnd, "C:\Documents\My.mdb")
If lngReturn < 33 Then
MsgBox "Error # " & CStr(lngReturn), vbCritical, "Error"
End If
dblReturn = LoadMyFile("NotePad", vbNullString)
End Sub
Private Function LoadUserFile(ByVal AppHandle As Long, ByVal MyFileName As String) As Long
LoadUserFile = ShellExecute(AppHandle, "open", MyFileName, 0, 0, SW_SHOWNORMAL)
End Function
Private Function LoadMyFile(ByVal MyProgramName As String, ByVal MyFileName As String) As Double
On Error GoTo LoadError
LoadMyFile = Shell(MyProgramName & Chr$(32) & MyFileName, vbNormalFocus)
Exit Function
LoadError:
LoadMyFile = Err.LastDllError
MsgBox Err.Description, vbCritical, "Error"
End Function
