By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

Refresh message window/textbox during VBA code execution

P: 7
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
Sep 23 '08 #1
Share this Question
Share on Google+
12 Replies


ADezii
Expert 5K+
P: 8,628
  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?
Sep 23 '08 #2

NeoPa
Expert Mod 15k+
P: 31,470
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.
Sep 23 '08 #3

P: 7
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.
Sep 23 '08 #4

FishVal
Expert 2.5K+
P: 2,653
Hello, bnono.

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

Regards,
Fish
Sep 23 '08 #5

NeoPa
Expert Mod 15k+
P: 31,470
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.
Sep 23 '08 #6

FishVal
Expert 2.5K+
P: 2,653
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.
Sep 23 '08 #7

P: 7
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
Sep 23 '08 #8

NeoPa
Expert Mod 15k+
P: 31,470
Glad to see it's resolved Bruno :)

Welcome to Bytes!
Sep 23 '08 #9

ADezii
Expert 5K+
P: 8,628
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.
Expand|Select|Wrap|Line Numbers
  1. Dim lngCounter As Long
  2. Dim varVal As Variant
  3.  
  4. DoCmd.Hourglass True
  5.  
  6.   For lngCounter = 1 To 20000000
  7.     varVal = lngCounter \ 37.968
  8.     If lngCounter Mod 100000 = 0 Then
  9.       DoEvents
  10.     End If
  11.   Next
  12.  
  13. 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?
Sep 23 '08 #10

P: 7
@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
Sep 24 '08 #11

NeoPa
Expert Mod 15k+
P: 31,470
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.
Sep 24 '08 #12

ADezii
Expert 5K+
P: 8,628
@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.
Sep 24 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.