none
"There was a problem sending the command to the program" error message appears after people started using my macros RRS feed

All replies

  • Avalyn3 -

    Not much of a description to go on, so the list is the classic one:

    1) Permissions: Read/Write/Modify, etc.

    2) Permissions to execute either the external app or the called member

    3) Users are standard users on their machine & the app requires Admin privs.

    4) 32 bit vs 64 bit

    5) Revisions / versioning are earlier/later than that of the dev

    6) References: don't go backwards very well when one vba app references an  additional Office App. e.g. Your app references both Excel and Word.  (Once I found that Microsoft Scripting referenced a different assembly entirely)

    7) Paths on user machine differs from the path on the Dev machine (e.g. there isn't an 'R:' drive)

    8) Use of deprecated syntax

    9) Data Types differ (e.g. you sent it a variant & the source program has no clue for conversion)

    ... and the list could go on ...

    Many of these can be avoided by simply declaring 'Option Explicit' at the top of all code sheets and using precise declarations as to variable type - rather than "Dim oThisThingy as Object"  - so that you have access to the Intellisense and you don't get confused as to what the variable actually represents. For example, that alone will tell you that there exists a 'Word.Range" object as well as an 'Excel.Range' object.

    -J


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Friday, March 17, 2017 12:49 PM
  • Another one:

    10) Poor error handling. For example, On Error Resume Next is used when in truth, If not (oThisThingy is nothing) then <go ahead with the procedure> will expose problems with the external object instantiation.


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Friday, March 17, 2017 12:52 PM
  • Hi avalyn3,

    Could you provide your code where/after people start your macro?

    Friday, March 17, 2017 1:20 PM
  • Option Explicit
    
    Sub Main()
    
    Dim screenUpdateState, calcState, eventsState
    
    'Get current state of various Excel settings; put this at the beginning of your code
    screenUpdateState = Application.ScreenUpdating
    calcState = Application.Calculation
    eventsState = Application.EnableEvents
    
    'turn off some Excel functionality so your code runs faster
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.IgnoreRemoteRequests = True
    ThisWorkbook.DoNotPromptForConvert = True
    
    'Handle access
    
    Select Case Application.Caller
     
    'my code
    
    End Select
                         
    'After your code runs, restore state; put this at the end of your code
    Application.ScreenUpdating = screenUpdateState
    Application.Calculation = calcState
    Application.EnableEvents = eventsState
    Application.IgnoreRemoteRequests = False
    ThisWorkbook.DoNotPromptForConvert = False
    
    Exit Sub
    
    ErrorHandler:
    
    MsgBox "An error has occured, please contact..."
    
    End Sub
    thanks in advance
    Friday, March 17, 2017 2:22 PM
  • could the fact that I am using forms that I hide rather than unload (i.e. they are kept in memory) cause this?

    Friday, March 17, 2017 2:54 PM
  • What line is it giving you the error & where / how are you invoking ErrorHandler?

    (On Error goto ErrorHandler)

    You might consider a structure like the following to get a better idea for what is happening:

    Public Sub Main() '<Your Dim statement> On Error goto ErrorHandler '<The Rest of your code to this statement> ThisWorkbook.DoNotPromptForConvert = False '<Then change the end to the following:> Exit_Main: Exit Sub ErrorHandler: MsgBox "The error " & err.number & ":" & vbcrlf & err.description &" has occurred. Please contact..." Resume next ' brings you to the statement that follows the error. 'If it turns out that some errors are OK, then you can use the error number

    'to Resume Exit_Main instead of showing the error message End Sub



    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Friday, March 17, 2017 3:37 PM
  • When you say 'Handle Access

    Is that Microsoft Access or permissions of some sort?


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    Friday, March 17, 2017 3:38 PM
  • Hi avalyn3,

    Thank you for providing code.
    I suppose it would be unnecessary to get
    /set current state of Excel settings
    You should set state as you want at opening and set state as default before closing. 

    Regards,
    Ashidacchi
    Friday, March 17, 2017 10:47 PM
  • Hi avalyn3,

    I suppose ".Hide" or "Unload" has no affect.
    But it cannot be possible to make it certain, unless you explain it.
    Regards,
    Ashidacchi
    Friday, March 17, 2017 10:52 PM