Excel VBA - Expected end of statement

Question

Dear All,

I have developed a little tool by Excel VBA which uses some userforms.

This application works perfectly on my PC but in different other PCs (with same Excel version and updates) it has one issue with this error message:

This is the code which is working on my PC

Public Function Popup(ByVal Prompt As String, Optional ByVal SecondsToWait As Integer = 0, Optional ByVal Title As String = "", Optional ByVal Buttons As VbMsgBoxStyle = vbOK) As VbMsgBoxResult 

    Dim Fso As Object 

    Dim Wss As Object 

    Dim TempFile As String 

    Prompt = Replace$(Prompt, vbCrLf, """ & vbCrLf & """) 

    On Error GoTo ExitPoint 

    Set Fso = CreateObject("Scripting.FileSystemObject") 

    Set Wss = CreateObject("WScript.Shell") 

    With Fso 

        TempFile = .BuildPath(.GetSpecialFolder(2).Path, .GetTempName & ".vbs") 

        With .CreateTextFile(TempFile) 

            .WriteLine "Set wss = CreateObject(""WScript.Shell"")" & vbCrLf & _ 

                "i = wss.Popup(""" & Prompt & """," & SecondsToWait & ",""" & Title & _ 

                """," & Buttons & ")" & "" & vbCrLf & _ 

                "WScript.Quit i" 

            .Close 

        End With 

    End With 

    Popup = Wss.Run(TempFile, 1, True) 

    Fso.DeleteFile TempFile, True 

ExitPoint: 

End Function

I don't understand what's happening but, anyway, I was able to replicate the error by removing vbCrLf on the above code.

In order to make it working again I have replaced vbCrLf with Chr(13) as below code and it is still working on my PC but I don't know If it is solve the issue on the others.

Tomorrow I'll check to the other PC which has the issue with the original code (with vbCrLf)

Public Function Popup(ByVal Prompt As String, Optional ByVal SecondsToWait As Integer = 0, Optional ByVal Title As String = "", Optional ByVal Buttons As VbMsgBoxStyle = vbOK) As VbMsgBoxResult 

  'Show a popup and wait some seconds, returns -1 if the user has not made a selection. 

  'Solve the problem that wss.Popup doesn't close some times when called directly from VBA 

    Dim Fso As Object 'FileSystemObject 

    Dim Wss As Object 'WshShell 

    Dim TempFile As String 

    Prompt = Replace$(Prompt, vbCrLf, """ & vbCrLf & """) 

    On Error GoTo ExitPoint 

    Set Fso = CreateObject("Scripting.FileSystemObject") 

    Set Wss = CreateObject("WScript.Shell") 

    With Fso 

        TempFile = .BuildPath(.GetSpecialFolder(2).Path, .GetTempName & ".vbs") 

        With .CreateTextFile(TempFile) 

            .WriteLine "Set wss = CreateObject(""WScript.Shell"")" & Chr(13) & _ 

                "i = wss.Popup(""" & Prompt & """," & SecondsToWait & ",""" & Title & _ 

                """," & Buttons & ")" & "" & Chr(13) & _ 

                "WScript.Quit i" 

            .Close 

        End With 

    End With 

    Popup = Wss.Run(TempFile, 1, True) 

    Fso.DeleteFile TempFile, True 

ExitPoint: 

End Function

Do you have any explanation?

What'is the issue?

Answer 1

Hi,

I'm Ajibola, an Independent Consultant here and a Microsoft user like you. I don't work for Microsoft.

The issue you are encountering could be related to the way different PCs handle line breaks and special characters in VBA code. The vbCrLf constant represents the carriage return and line feed characters (ASCII characters 13 and 10, respectively), which are used to create a new line in a text string.

Some PCs or environments might handle line breaks differently, causing issues when running the code. By replacing vbCrLf with Chr(13) (which represents only the carriage return character), you might have resolved the issue for the affected PCs, as it explicitly specifies the carriage return character without the line feed.

To further ensure cross-compatibility and consistent behaviour, you can modify the code to use only vbLf (line feed) instead of vbCrLf, as it represents the new line character without the carriage return. The modified code would look like this:

Public Function Popup(ByVal Prompt As String, Optional ByVal SecondsToWait As Integer = 0, Optional ByVal Title As String = "", Optional ByVal Buttons As VbMsgBoxStyle = vbOK) As VbMsgBoxResult

'Show a popup and wait some seconds, returns -1 if the user has not made a selection.
    'Solve the problem that wss. Popup doesn't close sometimes when called directly from VBA

Dim Fso As Object 'FileSystemObject
    Dim Wss As Object 'WshShell
    Dim TempFile As String

Prompt = Replace$(Prompt, vbLf, """ & vbLf & """) ' Use vbLf instead of vbCrLf

On Error GoTo ExitPoint

Set Fso = CreateObject("Scripting.FileSystemObject")
    Set Wss = CreateObject("WScript.Shell")

With Fso
        TempFile = . BuildPath(. GetSpecialFolder(2). Path, . GetTempName & ".vbs")
        With . CreateTextFile(TempFile)
            . WriteLine "Set wss = CreateObject(""WScript.Shell"")" & vbLf & \_
                       "i = wss. Popup(""" & Prompt & """," & SecondsToWait & ",""" & Title & \_
                       """," & Buttons & ")" & "" & vbLf & \_
                       "WScript.Quit i"
            . Close
        End With
    End With

Popup = Wss.Run(TempFile, 1, True)
    Fso.DeleteFile TempFile, True

ExitPoint:

End Function

By using vbLf, you should have a more consistent behavior across different environments, and the issue with the error message may be resolved on other PCs as well.
References https://stackoverflow.com/questions/33813162/excel-vba-could-not-load-an-object-because-it-is-not-available-on-this-machine https://support.microsoft.com/en-us/topic/error-message-when-using-the-excel-add-in-for-microsoft-forecaster-6-7-could-not-load-an-object-because-it-is-not-available-on-this-machine-93d2fdba-60af-2705-6ca2-c17234ffba5a https://www.mrexcel.com/board/threads/error-could-not-load-some-object-because-they-are-not-available-in-this-machine.310385/

I hope this helps! Let me know if you have any other questions.

Kind regards Ajibola

Answer 2

Thanks for your clear explanation.

I don't understand why PCs having the same Operating System and Office (same version and same updates) cannot have that different behavior.

I'll check and I'll update you

Best Regards

Answer 3

I look forward to your update

Answer 4

I look forward to your update

You suggestion solved the issue but, believe me, I really don't understand why It happened