We'll I've had some successes with the CreateThread APIs natively in VBA. It's not crashing excel, although there are some anomalies that need to be worked on.
Highlights:
Problems/Issues:
I'm most concerned with passing a value to the new thread without resorting to globals. Any insights?
Here's some sample code
(note: i left out the var in the function declaration, as well as some other testing lines)
Highlights:
- 1MM concurrently running asynchronous threads--success!
Problems/Issues:
- I can't seem to pass a value/pointer to the function. CreateThread will accept lpParameter, but the function doesn’t seem to receive it. As long as I don’t attempt to read the variable/pointer in the function, it’s fine. But I so much as put it in a msgbox, instant crash. As far as I can tell, the passed variable just doesn’t exist at runtime.
Still validating the resources situation. If I run too many concurrent threads, excel will sometimes behavior strangely after the fact (and all threads are closed). Everything seems to work ok, but for example: if I click save, sometimes I get the error message “Microsoft Excel cannot accesss the file … . There are several possible reasons (1) The file name or path does not exist. (2) The file is being used by anthor program (3) the workbook you are tyring to save has the same name as a currently open workbook." I am concerned that some memory might be overwritten somehow.
ExitThread does not work, which might be why the reason for some of the anomalies. Using TerminateThread instead, which may or may not be freeing up the reasources. Memory usage in task manager appears "normal"
Don't write to the same cell simultaneously--crash.
I'm most concerned with passing a value to the new thread without resorting to globals. Any insights?
Here's some sample code
(note: i left out the var in the function declaration, as well as some other testing lines)
Code:
Function AsyncThread()
On Error Resume Next
' Do Stuff
' Clean up Thread
AsyncThread = True
Dim lpExitCode
GetExitCodeThread GetCurrentThread, lpExitCode
' ExitThread lpExitCode ' Crashes Excel
TerminateThread GetCurrentThread, lpExitCode
End Function
Sub a_test()
On Error Resume Next
Dim hThread
For i = 1 To 100000
hThread = CreateThread(ByVal 0&, ByVal 0&, AddressOf AsyncThread, 0&, ByVal 0&, 0&)
CloseHandle hThread
Next i
MsgBox "Done"
End Sub
