Ed
Mon Nov 26 02:14:17 PST 2007
Russ,
Actually it turned out to be several things in the program that 'speed'
seemed to affect. All involved sub-routines and screen redrawings. And,
despite the fact that VBA should inherently wait while calling its own
sub-rountines, I can prove conclusively that it does not (or at least it
does not wait for the 'consequence' of the the sub-routine to fully
occur--in this case, a screen refresh with new text inserted or text
removed).
I do a lot of string functions manipulating the on-screen text. My
hypothesis is that while the sub-routine attempts to change the value of
certain on-screen text, that value is not actually resolved on screen by
the time the next steps are taken.
Without the pause (I will call it a speed bump) that I have built in,
the routine appears to proceed with the 'original' (pre-subroutine)
on-screen text. (I say 'appears' because it really isn't possible for me to
test for this. If I 'step' through the process, there is plenty of time for
the proper text to be present on the screen by the time the next step is
invoked.
So I have stuck a speed-bump at the end of each subroutine which
involves an on-screen value change. (i.e., something that involved changing
range or selection text (as opposed to a variable value change). And that
has cured the problem.
There is a slight delay for the speed-bump, but the computer is so fast,
it isn't noticeable. When I take out even one speedbump, the program reverts
to its old behavior.
Strange, but true. Do you have any other suggestions for testing the
hypothesis? (Actually, I just thought of one as I was writing this tome. Put
in a screen fresh command! I will try that.)
(By the way, I do recognize that the CFOD issue does not involve string
manipulation, but I do change the file open directory via a sub-routine and
the speed bump cured that problem as well. There may be something different
going on there, but I am accepting the fix without questioning it.)
Ed (in Virginia)
"Russ" <drsN0SPAMmikle@hotmailD0Tcom.INVALID> wrote in message
news:C36FA3D9.1F7CC%drsN0SPAMmikle@hotmailD0Tcom.INVALID...
> Ed,
> I'm unclear on what originally caused your problem. Was it the time needed
> to build up and store the path string into the newpath variable or the
> ChangeFileOpenDirectory syntax command line?
> You said you put a delay before the command, so I'm guessing it was the
> method you used to build up the newpath path.
>
> VBA should inherently wait when calling one it's own subroutines. But if
> you
> go outside of VBA by using a Windows shell or script routine, VBA will
> happily initiate the shell/script stuff, but won't wait for any return
> values.
>
> Here is some information from a past message thread on how to make VBA
> wait
> while going outside of VBA:
> =======Quote
> There are other ways to move files using other scripting languages, too.
> In a dos batch file, you could use xxcopy ( a variation of xcopy ).
> <
http://www.xxcopy.com/xxcopy17.htm>
>
> You could call a dos batch file from VBA.
> Helmut Weber mentioned this:
> <
http://vb.mvps.org/samples/project.asp?id=Shell32>
>
>
>
> Or this xShell code works in Word97, too:
>
> Put this in Declarations section at the top of your VBA code module so
> that
> all subroutines can take advantage of the 'wait for shell' code.
>
> Private Declare Function CloseHandle Lib "kernel32" ( _
> ByVal hObject As Long) As Long
> Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
> ByVal hProcess As Long, lpExitCode As Long) As Long
> Private Declare Function OpenProcess Lib "kernel32" ( _
> ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
> ByVal dwProcessId As Long) As Long
>
> 'Add this code as one of the regular subroutines.
>
> Public Function ShellX( _
> ByVal PathName As String, _
> Optional ByVal WindowStyle As Integer = vbMinimizedFocus, _
> Optional ByVal Events As Boolean = True _
> ) As Long
>
> 'Declarations:
> Const STILL_ACTIVE = &H103&
> Const PROCESS_QUERY_INFORMATION = &H400&
> Dim ProcId As Long
> Dim ProcHnd As Long
>
> 'Get process-handle:
> ProcId = Shell(PathName, WindowStyle)
> ProcHnd = OpenProcess(PROCESS_QUERY_INFORMATION, True, ProcId)
>
> 'wait for process end:
> Do
> If Events Then DoEvents
> GetExitCodeProcess ProcHnd, ShellX
> Loop While ShellX = STILL_ACTIVE
>
> 'clean up:
> CloseHandle ProcHnd
> End Function
> ++++++++++++++++
> 'And call it like this:
>
> Dim x As Long
> Dim strDosBatchFullPath As String
> strDosBatchFullPath = ³C:\...myDosBatchFile.bat²
> System.Cursor = wdCursorWait
> x = ShellX(Chr(34) & strDosBatchFullPath & Chr(34))
> ============UnQuote
>
>
>
>> Russ,
>>
>> You have hit the nail on the head. There was not enough time for the
>> recalcitrant computer to resolve itself from the call to the sub-routine
>> (module). This happened in several other situations where sub-routines
>> were
>> called. I found myself having to put 1/10th second counters in a half
>> dozen
>> (so far) locations to insure proper processing. Any idea why this is
>> allowed
>> to happen in a program such as VBA??
>>
>> Ed (in Virginia)
>>
>> "Russ" <drsN0SPAMmikle@hotmailD0Tcom.INVALID> wrote in message
>> news:C36A1363.1F5D6%drsN0SPAMmikle@hotmailD0Tcom.INVALID...
>>> Hey Ed,
>>> Helmut's suggestion seems sufficient.
>>> However, I'm curious.
>>> If you put the line:
>>> MsgBox "<<" & newpath & ">>"
>>> Just before the ChangeFileOpenDirectory line in the recalcitrant
>>> computer,
>>> does that message popup show the path string you expect between the
>>> chevrons
>>> and/or is that enough time to allow the path to resolve itself before
>>> the
>>> FileOpen dialog appears?
>>>
>>>> Hi Ed,
>>>>
>>>> this one is working for me today,
>>>> but who knows about tomorrow?
>>>>
>>>> Sub OpenFolder()
>>>> Dim f1 As String
>>>> Dim f2 As String
>>>> Dim f3 As String
>>>> f1 = "c:\test\word1\"
>>>> f2 = "c:\test\word2\"
>>>> f3 = "c:\test\word3\"
>>>> ChangeFileOpenDirectory f3
>>>> With Dialogs(wdDialogFileOpen)
>>>> .Name = f3 & "*.doc" ' ! Path & name
>>>> .Show
>>>> End With
>>>> End Sub
>>>>
>>>>
>>>>
>>>> --
>>>>
>>>> Greetings from Bavaria, Germany
>>>>
>>>> Helmut Weber, MVP WordVBA
>>>>
>>>> Vista Small Business, Office XP
>>>>> I have installations of the identical VBA program on 20 different
>>>>> computers.
>>>>> I use the following command with some frequency.
>>>>>
>>>>> ChangeFileOpenDirectory newpath
>>>>> With Dialogs(wdDialogFileOpen)
>>>>> .name = "*.doc"
>>>>> CloseNum = .Show
>>>>> End With
>>>>>
>>>>> The command works perfectly on 19. One the 20th it refuses to work.
>>>>> The
>>>>> MyDocuments folder consistently opens when the section of code is
>>>>> encountered.
>>>>>
>>>>> I have doubled the "ChangeFileOpenDirectory newpath" command (I found
>>>>> that
>>>>> suggestion on a board) but that doesn't work either.
>>>>>
>>>>> When I step through (debugging) the vba code it works fine, so I know
>>>>> that
>>>>> the connections are intact.
>>>>>
>>>>> Any thoughts?
>>>>>
>>>>> Ed (in Virginia)
>>>
>>> --
>>> Russ
>>>
>>> drsmN0SPAMikleAThotmailD0Tcom.INVALID
>>>
>>
>>
>
> --
> Russ
>
> drsmN0SPAMikleAThotmailD0Tcom.INVALID
>