Shell Function "Compile Error: Expected procedure, not variable"

I have a VBA shell script that worked just fine on another computer, but now it won't run.

The code that won't execute is:

Please Login or Register  to view this content.

This gives the error message: "Compile Error: Expected procedure, not variable", which to me indicates that the Shell command is not recogniced.

Have also teste a bunch of other syntaxes (eg. using WScript). This returns other errors (eg. Variable not defined for WScript)

As the code worked before (on another computer) and none of the other shell syntaxes works, I suspect that the problem is related to a dependency to a library reference, or that there is some kind of a conflict between different libraries that uses Shell as a keyword. I have activated "Microsoft Shell Controls And Automatation" and some other libraries that i hoped would work, but so far without any luck.

Does anyone know how to solve this?

Hi and welcome to ExcelForum,

Thank you for using CODE TAGS.

Chances are there is a missing VBA reference to one of the basic VBA runtime libraries. Chip Pearson references 5 basic librararies, which are the only ones I have loaded on my Excel 2003 computer, and your code compiles and runs on my machine if I use
'c:\Windows\system32\Write.exe'.

For Chip's list of the libraries see: http://www.cpearson.com/excel/missingreferences.aspx

Lewis

Ty for a Quick Reply! It seems that I am missing one of the libraries. I will post an update when I have tried the solution.

Originally Posted by LJMetzger

Hi and welcome to ExcelForum,
Chip Pearson references 5 basic librararies, which are the only ones I have loaded on my Excel 2003 computer [...] http://www.cpearson.com/excel/missingreferences.aspx
Lewis

Turns out that the missing reference library (RegistryWorx) was not part of the 5 basic librararies, but an additional library that I could download from cpearson. I have now tried the "Detect And Repair option" that the article sugguested and also tried to reduce my references to only the 5 essential libraries without anny change in the results. As the macro worked on your computer with the same libraries I wonder If there are any security options that I'm missing? anny other sugguestions? (I have excel 2010 BTW)

Originally Posted by Solus Rankin

Perhaps your program and .txt file are not in the same location on both computers.

When I was missing one or both of the above items in Excel 2003, the code compiled fine. There was a problem at runtime because of the missing resource(s).

Lewis

It seems like there is something missing that does not recognize the 'Shell' command.

Just to clarify: Both files are on the same computer. The code is just an example of how the Shell syntax that worked on my work computer does not work on my home computer. LJMetzger have tested the same code and got it to work on his computer with the same libraries that I have activated on my homecomputer, indicating that its not a problem with the syntax in it self and that the correct libraries are activated.

Originally Posted by Solus Rankin

Perhaps your program and .txt file are not in the same location on both computers.

Do c:\Windows\Write.exe and c:\Test.txt exist on the computer the shell command is not working on?

Originally Posted by shg

Does VBA.Shell work?

This solved my problem! :D Thankyou very much!

The new code that work is:

Please Login or Register  to view this content.

Seems strange that I needed to use a different syntax on this computer than the others. Any thoughts on why?
My home computer have excel 2010 while the "call shell" syntax worked on my work computer with a newer version of excel and Excel 2003 (acording to LJMetzger).

Originally Posted by shg

Does VBA.Shell work?

Great job shg. Shell and VBA.Shell both work on my Vista 32 bit computer with Excel 2003.

Lewis