If you are writing VBA code on a regular basis – this would be the best investment of your time!Īt the very least, understand data storage, structures and structural design of a program. With that in mind, let’s examine my “top 6” for writing efficient, performant VBA code. In my mind, this is the number one reason for the huge performance gap you can achieve with VBA in two different implementations, for the same task. ![]() Unfortunately, most Excel VBA courses out there were planned and delivered by people who became very good in VBA, but never really learned, and gained experience, as computer programmers. This means understanding data structures, data types, data storage, functional programming (for functional languages such as VBA), code design, error handling, and other aspects of computer programming. People miss out on the fact that VBA is a computer programming language, and not a scripting add-on to move data about Excel Worksheets.Īs a programming language, one needs to be a computer programmer in order to write efficient code. However, VBA can turn into a source of frustration for most of them, as they are not taught how to be computer programmers. VBA is a blessing for its ability to unleash endless automation solutions in the hands of almost every advanced Excel user. I believe this can be explained by the way people perceive VBA and how they were taught VBA. Isn’t it amazing that the same tool, VBA, can produce a 5-seconds processing time by one programmer, and 5-minutes processing time by another programmer – for the same task? They both “know” VBA, don’t they? How is it that huge performance gaps are observed with VBA? When they run a loop over 400,000 cells – they are frustrated and ask: “why is my VBA slow?”. They learned looping over a range of cells in an online YouTube example over a range of 50 cells, and they now believe that this is how “VBA” updates cells with data. ![]() They know how to reference Worksheet cells, how to run a loop and maybe manage multiple Workbooks and Worksheets. Most of the VBA code out there is a result of advanced Excel users who learned some VBA statements. ![]() ![]() This week, we’ll improve your VBA code! A story of frustration with slow VBA Last week I offered some advice on improving the performance of your Excel Workbook.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |