distributed vba project - multiple thread / concurrent file access

[chris_culley]

Hi,

I know that vba isn't really written to allow this, but it's the only
tool I've got available.

My monte carlo model takes four hours to run on a single machine. I
thought it would be a good idea to split the processing over several
machines.

I've set the model up so the number of simulations is split up, and
each machine takes a set of these and runs with them (each machine
takes a 'job'). So far so good.

To allocate which machine takes which job I've written the job names
in an excel file. The machines poll on this file until it is
available. Once they get it and open it they take a job (and set a
flag to say they've taken it), close the file and run with the job.

I use the following code for the polling (isFileOpen is a function I
fonud on the MS support site - similar to John Walkenbachs
fileIsOpen()) :

***

While (IsFileOpen(wbname))
' wait a short (random) time (up to ten seconds)
waitTime = Rnd() * 10
Debug.Print Now()
Application.Wait (Now + TimeValue("0:00:" & waitTime & ""))
DoEvents
Wend

' ok open the file, take a job etc....
workbook(wbname).open ......
***

Now, I'm really concerned about concurrency issues - since three
machines could theoretically all hit the isFileOpen at the same time
and attempt to open it (or set the same flags). This would cause any
number of difficult to solve problems.

Anyone got any ideas? is there a quicker (i.e. atomic) way to check
and open a file? can anyone think of someway of using locks on the
file? Any advice from someone who's tried similar?

Many thanks,

Chris

 

[Tom Ogilvy]

I would expect the operating system to handle this.

 

[chris_culley]

Yeah - I imagine it should do with making sure that only one file can
open another at the same time... the problem I'm thinking off is the
following:

- imagine index.xls is not open (and held on a network drive
somewhere).
- two machines test at the same time that it's not open (unlikely).
- both machines break out of the while loop.
- one machine is able to open the file (pbly not both, as you
suggest).
- the other machine is left in limbo - can't open the file, can't get
back on it's poll

Not sure if this could happen, though if it could it would be rare?

 

[Tom Ogilvy]

Dim bk as Workbook
Do
if not isfileopen( abc) then

set bk = nothing
On error resume next
set bk = workbooks.open("index.xls")
On Error goto 0
Else
'pause
end if

loop while bk is nothing

I haven't tried it, but I assume if you can't open the book it will raise an
error.

 

[NickHK]

Chris,
I have seen a product that enables Excel to spread the workload over
available machines, co-ordinating the processing and results. I can't
remember its name now, but a Google search on suitable terms may bring it
up. No idea on cost or applicability to you.

Have you considered using ADO to query/update the WB. Not sure if it would
be better, but you would not have the IsOpen problem.

NickHK