Refresh message window/textbox during VBA code execution

[bnono]

Hi all,

I developed some VBA code in MS Access 2003, and during execution I print messages indicating the overall progress in a textbox of the active form (and also in the bottom status bar). To be precise I use a Fortran DLL which passes its messages through a VBA callback function, but I don't think it's important for the problem.

The problem is that once I focus on another application, MS Access freezes itself. I mean, when I come back to Access, the cursor turns into a clock and there is no refreshing until the end of code execution, hence spoiling all the efforts I made to give the user some info on execution.

Is there a way to handle that problem ?

Thanks

Bruno

[ADezii]

1. How about Posting your VBA code?
2. How are you 'Focusing' on the External Application, Automation?
3. It sounds like the .DLL routine may be executing synchronously, could this be the case?
4. Have you tried using DoEvents in the code block to periodically relinquish control to the Windows Environment?

[NeoPa]

It seems this is triggered by the calling of the external code rather than your manually switching to another appliocation as stated in your question.

It would help if you indicated how the external code is invoked, but I'm certain it is a synchronous call. This runs code as part of the same process. Access will wait until control is returned to it before it responds further. As far as Access is concerned it is Busy.

[bnono]

Well the code is quite long but to describe it shortly I use a Declare statement to declare the dll:

Declare Sub HFprediction Lib "Z:\GlobalHF\HF\GLOBHEAT\f90HFprediction\HFpredict ion.dll" ( ..., ByVal pCallbackFunc As Long )

with the last argument corresponding to the callback function. Then I simply call the dll when clicking on a OK button:

call HFprediction(..., AddressOf Callback)

Callback being a simple VBA subroutine to display messages:

sub Callback(string)
debug.print string
Application.Forms("HF_prediction").TxtMessages = string
SysCmd(acSysCmdSetStatus, string)
end sub

I think that what I do is a synchronous call (though I don't really know what that means), and I guess that MS Access considers itself busy as long as the DLL runs, which prevents it from refreshing itself and showing the messages sent by the DLL through the callback. I thought that there was a way to tell Access to refresh itself once in a while, for example each time the callback function is called, but I couldn't find how.

[FishVal]

Hello, bnono.

Does the same happen if the callback function contains no code ?

Regards,
Fish

[NeoPa]

This is just as in-line or synchronous as calling a VBA function (like Format()).

However, if you get callbacks from within the routine where you update your screen, you need to revisit ADezii's point #4 and use DoEvents in your update code.

[FishVal]

I'm almost sure the problem is not with callback itself.
I've just now tested WinAPI timer with callback function that modifies Textbox content on a form. It works fine when Access window is inactive as well as when it is minimized.

Check whether your callback doesn't return to calling DLL or maybe reentered.

[bnono]

ok that works! I simply added a DoEvents() at the end of the Callback function, as suggested. I did not know that function, I was rather looking for sth like Application.Refresh

Anyway I guess that's it's a quick'n'dirty way to handle the problem (a Timer control may be a better choice), but that's enough in my simple case.

Thanks again for your advices

Bruno

[NeoPa]

Glad to see it's resolved Bruno :)

Welcome to Bytes!

[ADezii]

ok that works! I simply added a DoEvents() at the end of the Callback function, as suggested. I did not know that function, I was rather looking for sth like Application.Refresh

Anyway I guess that's it's a quick'n'dirty way to handle the problem (a Timer control may be a better choice), but that's enough in my simple case.

Thanks again for your advices

Bruno

Since we have no visual cue as to what your code is doing, one precautionary note on DoEvents() which may/may not be applicable here. Avoid using DoEvents for each and every Iteration of an intensive Loop since it will gobble up resources. In the example below, the code releases control to the Windows Environment for every 100,000 iterations of the Loop. As previously stated, this may not be applicable, and you may also have to tweek how many time you use the DoEvents() Function.

Dim lngCounter As Long
Dim varVal As Variant
 
DoCmd.Hourglass True
 
  For lngCounter = 1 To 20000000
    varVal = lngCounter \ 37.968
    If lngCounter Mod 100000 = 0 Then
      DoEvents
    End If
  Next
 
DoCmd.Hourglass False

P.S. - Just out of curiosity, bnono. Using a Fortran .DLL to pass Messages via a VBA Callback Function, then display some Text in a Text Box, in order to indicate the relative completion percentage of some process, seems a little extreme. There are many other, much simpler, means to accomplish this. Why this approach?

[bnono]

@ADezii:

in fact i'm a geophysicist and as such I use Access to manipulate geophysical data. Also you may know that we scientists still like to use good old Fortran for data processing, because it's fast and efficient (faster than vba, at least) and also because it's an old habit. This explains I use a lot this combination of VBA (for GUI) and Fortran DLL (for data processing). Then the problem is that my DLL can run for hours, so I want to have some feedback once in a while, hence the use of a callback function... In fact I don't simply use it to display a simple progress bar, but various messages concerning the state of the program. Until now I was simply doing that by writing the messages to a file and then sometimes checking that file, but I found that printing the messages directly to my Access User form was more friendly. Since I call the callback function from my DLL only once in a while (say, every 10 s), it's ok to use DoEvent, which is not encapsulated into a wasting-resources loop.

Hope this sounds reasonable to you... That being said I know Fortran better than VBA, and if you have suggestions of simpler way to achieve what I want to do, please tell me !

Now I've got another question... : is there simple a way to unload the DLL after it has been called, other than reinitializing the VBA project? That's only a detail, but I constantly have to modify and recompile the DLL, and it's a bit annoying to always reinitialize the VBA project to be able to do so.

Regards,

Bruno

[NeoPa]

Hi Bruno.

That explanation seems to be quite sensible. My brother's a geophysicist, and he's recently got into Access too, to handle some of the enormous datasets he has to deal with (although he mainly still works in Excel - his comfort zone ;)).

Anyway, I suspect there's no way of unloading a module once it's been initialised. As it's code outside the control of Access, it's quite reasonable for Access to require a reset when it is changed. Even adding/removing multiline code or constants in VBA requires the resetting of the running code I'm afraid.

[ADezii]

@ADezii:

in fact i'm a geophysicist and as such I use Access to manipulate geophysical data. Also you may know that we scientists still like to use good old Fortran for data processing, because it's fast and efficient (faster than vba, at least) and also because it's an old habit. This explains I use a lot this combination of VBA (for GUI) and Fortran DLL (for data processing). Then the problem is that my DLL can run for hours, so I want to have some feedback once in a while, hence the use of a callback function... In fact I don't simply use it to display a simple progress bar, but various messages concerning the state of the program. Until now I was simply doing that by writing the messages to a file and then sometimes checking that file, but I found that printing the messages directly to my Access User form was more friendly. Since I call the callback function from my DLL only once in a while (say, every 10 s), it's ok to use DoEvent, which is not encapsulated into a wasting-resources loop.

Hope this sounds reasonable to you... That being said I know Fortran better than VBA, and if you have suggestions of simpler way to achieve what I want to do, please tell me !

Now I've got another question... : is there simple a way to unload the DLL after it has been called, other than reinitializing the VBA project? That's only a detail, but I constantly have to modify and recompile the DLL, and it's a bit annoying to always reinitialize the VBA project to be able to do so.

Regards,

Bruno

NeoPa alreadly answered the question for you:

VBA dynamically loads code into memory as needed on a module level. If a function is called or a variable is used, the entire module containing that function or variable is loaded into memory. As you are developing your application, you keep loading code into memory. Visual Basic for Applications does not support dynamic unloading of these modules.