VBA: Procedure runs fine except when invoked by its shortcut

[spectrallypure]

Hi all! There is a problem that I have had for *years* and which
drives me crazy every time I come across it. I really look forward to
find out the reason behind it and, if possible, to figure out a
solution for good.

The problem is as follows. Certain VBA procedures don't work well when
I call them using the associated keyboard shortcuts, but run perfectly
fine when I execute them using the Excel macro dialog box or from the
Visual Basic interface (by either using the F5 (run) or F8 (debug step
into)). I have noticed that the problem arises in procedures that have
to do with external files, and mostly what happens is that the
procedure suddenly stops without any warning/error, in a moment in
which it was processing the external file.

To help clarify the situation, I have prepared a sample workbook
(Excel 2007) with a macro caller "TestIllProcedure" that suffers from
this behavior. The procedure is supposed to open several external text
documents and use the contents in those to fill the columns of a
table. The sample files involved can be downloaded from:

http://rapidshare.com/files/84585217/test.zip.html

To run this sample book, unzip the contents of the attached file to
any directory and follow the instructions. As pointed out before, the
macro fails to run when invoked by its shortcut, but runs perfectly
when using the excel macro menu or from the Visual Basic interface.

....What could be the problem? I have had this problem with several
procedures since Excel 2000!

Thanks in advance for any ideas!

Regards,

Jorge Luis.

 

[spectrallypure]

Anybody? ...please!!!!!!!

 

[Dave Peterson]

I know I won't open files. I would expect lots of people won't even take the
time to download your file.

But (just a guess)...

My guess is that you're using a shortcut key to start your macro. And that
shortcut key includes a shift key.

Remove the shift key from that shortcut combination and try it again.

Holding the shiftkey when you open a workbook stops the open macros from
running. It also confuses excel/vba into thinking it should stop.

 

[spectrallypure]

Dave, I guess I cannot thank you enough for your advice... you were
totally right! it was the darn SHIFT key that I customarily use for my
VBA procedures!!!! I changed the shortcut to be any keystroke without
including SHIFT and it runs like a breeze!

Thanks again for your help... you really took a big share of stress
and frustration (accumulated for years) out of my head!

Regards,

Jorge Luis.

 

[ilia]

I'd download it, but not from a site that requires registration to do
so.

 

[spectrallypure]

I'd download it, but not from a site that requires registration to do

so.

Hum... it's so strange: it perfectly allows me to download the file
without asking for any registration!!! are you sure you're grabbing
the link correctly? I have always used Rapidshare both to upload &
download stuff, and it never forced me to register in order to do
those things...

Well I guess it doesn't matter anymore since the problem was already
solved. Thanks anyway for your interest!

Regards,

Jorge Luis.