data gathering within VBA macro

kolevn · #1 (**permalink**) 02-16-04, 04:49

Hello,

I have written VBA code that automates some data gathering and computation routines. The problem I have within VBA is that I do not know how to allow sufficient time for the data to be fed into the spreadsheets.

To be more precise, my program does the following:
1. Data is fed into the spreadsheet;
2. A computation is performed; and
3. The above two steps are repeated several times for different data.

I have tried two things: a timer and a Do Until (no #N/A appears within the data set). However, Excel appears to compute only sequentially, and everything comes to a halt.

I would truly appreciate your insight.

Thanks,

Nick

Xl-Dennis · #2 (**permalink**) 02-16-04, 21:22

Hi,

Do You make any call to an external program in the VBA-procedure?
If so then You need to use Win API with the following APIs:

Code:

Private Declare Function OpenProcess Lib "kernel32" ( _ 
      ByVal dwDesiredAccess As Long, _ 
      ByVal bInheritHandle As Long, _ 
      ByVal dwProcessId As Long) As Long 
 

Private Declare Function GetExitCodeProcess Lib "kernel32" ( _ 
      ByVal lnghProcess As Long, _ 
      lpExitCode As Long) As Long  

Private Const PROCESS_ALL_ACCESS = &H1F0FFF

BTW, Excel can only work with one process at the time.

Kind regards,
Dennis

kolevn · #3 (**permalink**) 02-17-04, 03:12

Hi Dennis,

Many thanks!

I shall try this out right away.

All the best,

Nick

Quote:

Originally posted by Xl-Dennis
Hi,

Do You make any call to an external program in the VBA-procedure?
If so then You need to use Win API with the following APIs:

Code:

Private Declare Function OpenProcess Lib "kernel32" ( _ 
      ByVal dwDesiredAccess As Long, _ 
      ByVal bInheritHandle As Long, _ 
      ByVal dwProcessId As Long) As Long 
 

Private Declare Function GetExitCodeProcess Lib "kernel32" ( _ 
      ByVal lnghProcess As Long, _ 
      lpExitCode As Long) As Long  

Private Const PROCESS_ALL_ACCESS = &H1F0FFF

BTW, Excel can only work with one process at the time.

Kind regards,
Dennis

Internet Services

Web Development

Digital Marketing

Consumer Tech