Short sweet and to the point – click here.

Some tips

• understand scope in VBA – this is NOT VB.net
• perhaps use global variables and use _CAPS so they are readily identified
• put them in their own VBA module

With VBA, it is generally a solution for one’s self or a small group of individuals. Most of my apps if an exception happens, a yellow line is highlighted in the VBA editor, you investigate the variables LIVE (on thy fly – love that about VBA) and adjust or fix the situation. If the code is for others, then some error handling is added and the world is stable until the next time.

Recently, I was asked to make the error handling cascade like c# – so now it is time to fully understand the error handling of VBA.

Here is SUPER resource – it is for MS Access, but will apply equally for all MS Office and other VBA apps. There are few new tricks I will try on some hornery AutoCAD tools. In Excel the “Do Events” usuallly fixes many of the super odd errors (VBA is going faster than Excel and.. Do Events helps to sync the two into stability.

So there is an application that requires a best guess – but still human interaction to choose which item when there is more than one result returned.

So one can make a web service to pull back a pull down and make a form for the user to make a choice allowing quicker spreadsheets to be made. Why not web services and in my case, ASP.NET can be used.

Here is a REALLY old article, but it has all the pieces. http://msdn.microsoft.com/en-us/magazine/cc163837.aspx#S1

http://stackoverflow.com/questions/8249706/run-stored-procedure-and-return-values-from-vba

http://stackoverflow.com/questions/1120674/accessing-sql-database-in-excel-vba

http://www.excelforum.com/excel-general/758998-data-validation-drop-down-menu-from-a-database.html

http://www.erlandsendata.no/english/index.php?t=envbadac

OK – so I am reading stuff that seems a little too geeky, but after asking “what is this late vs. early binding” stuff – I had to know what it was. Here is a simple explanation – even I can understand. It really does (did) not matter – but the main point is summarized nicely in this article “… as long as the programmer uses it responsibly (late binding) “.

http://stackoverflow.com/questions/10580/what-is-early-and-late-binding

What is this about? I want to use Dictionaries in VBA … now I have finally snapped and found out. Here is THAT article …

http://stackoverflow.com/a/23675963 and the ref to the msdn to it is here.

http://www.brianweidenbaum.com/projects/vba-arraylist/