I posted a question to your multi-thread forum topic a few days ago and was going back to try to see if you had replied, but cannot find a link to your forum on your site any longer. I did locate a hyperlink in one of your blog posts (https://www.analystcave.com/forums/forum/analystcave-tools/vba-multithreading-tool/) but it just takes me to your home page. I’m sure the issue is between the chair and the keyboard on my end, but any guidance would be appreciated.
Hi James, sorry if you question got lost in the transition. I was disappointed in the bbPress plugin and decided to move something that fits in more to my Blog.
If you didn’t notice the answer before I took down the forum…I found your original question:
Looking thru your information about your multi-threading tool for VBA, it is exactly what I need. I wasn’t, however, able to discern exactly how to use it when calling a custom function built in VBA from Excel.
In my Excel spreadsheet, I am using the winHTTP service and Yahoo’s finance API to obtain real-time stock quotes for a list of stock symbols. I have about 100 cells or so that perform this function call: ‘=RequestStockInfoFrYahooFinance(,”l1″)’. And code for the simple function is this:Visual Basic
12345678910111213 Function RequestStockInfoFrYahooFinance(Symbol As String, Tag As String) As StringDim URL As String: URL = "http://finance.yahoo.com/d/quotes.csv?s=" & Symbol & "&f=" & TagDim X As New WinHttpRequestX.Open "GET", URL, FalseX.sendDim ReturnedText As String: ReturnedText = Replace(X.ResponseText, Chr(34), "")RequestStockInfoFrYahooFinance = ReturnedTextEnd Function
So looking for some guidance on exactly where the parallelClass logic should go and how/if it changes the syntax of how I call the function from the cells in Excel. Does it go in the function?
Do I need to create a new function that has the code and then that calls my existing function? Your guidance would be appreciated.
I would like to have all the threads join and “go away” after they all are executed as well, but didn’t quite follow how I’d accomplish that either. So if it doesn’t become apparent based on your guidance on the above, any direction to join the threads would be appreciated as well.
And my answer to it was:
You can’t use the Multithreading Tool with an UDF it is too heavy weight. For the purpose of your exercise you need to:
– either replace the UDFs altogether with a single VBA macro that can be multithreaded using my Tool
– create UDF in XLL instead using VB.NET or C#.NET (parallel class included)
– create a COM visible DLL in VB.NET or C#.NET (similar as point above)
Thoughts on Multithreading VBA UDFs in General
Personally, as I am comfortable with VBA and .NET I go for XLLs (create new C# Library, install XLL Nuget , implement functions and build – double click on XLL file to install. Read more on Excel-DNA.
Otherwise if I need quickwins – I give my VBA Compiler a try. I just extract the computations into a separate function that you can export to DLL and run the DLL function from a Excel VBA UDF.
I think Daniel Ferry created a VBA Swarm that does exactly what you need.
Otherwise you can go for replacing the UDFs altogether with a separate macro and use my Multithreading Tool.
Or try the XLL functions as they should be executed in parallel – Excel functions are executed in parallel threads and so should be XLL functions.