I try to design a programm in VBA (used within a simulation software) which is able to call Python for some calculations and receive the result to proceed. The VBA-Python call will happen many times.
My first idea is based on a text file communication, e.g. something like this:
in VBA:
do something
write text file 'calc_todo.txt' in specific directory
while not exists 'calc_finished.txt':
wait 1 second
read 'calc_finished.txt'
delete'calc_finished.txt'
delete'calc_todo.txt'
do something
write text file 'calc_todo.txt' in specific directory
... repeat
in Python:
do something
while not exists 'calc_todo.txt':
wait 1 second
read 'calc_todo.txt'
do calculations based on 'calc_todo.txt'
write 'calc_finished.txt'
delete'calc_todo.txt'
do something
while not exists 'calc_todo.txt':
wait 1 second
... repeat
I have done something similar in past and unfortunately there are a lot of things I do not like:
- fixed waiting time of e.g. 1 second might slow down performance
- if something breaks VBA and/or Python will get stuck in a while loop or run in an error
- to fix the second issue, error handling with initialisation can be implemented but last time it was a mess
What would be a more professional way on how to handle such communication?
