Learn how to use Visual Basic to Automate both Microsoft Excel and Microsoft Word. Enhance your capabilities, increase your efficiency and boost the quality and consistency of your work.

If you’ve ever had to work with spreadsheets that you inherited from other people, chances are they were riddled with obsolete range names that do not point to valid addresses anymore. Getting rid of those one by one by pressing the delete button is tedious and you may accidentally delete a name too many in the process. Tracking range names down however is not too difficult using a bit of visual basic code. Read the rest of this entry »

In planning and manhour forecasting you will repeatedly have to count the number of workdays within a certain date interval. In case the dates are all fridays or mondays this is easy of course, but if they are not, you cannot just count weeks and multiply by 5. Counting workdays gets even more complex if you want to account for public holidays as well.

Counting workdays by user defined function in VBA

In MS-Excel you can easily automate the process of counting workdays by using Visual Basic. And because it runs so fast, the algorithm can be quite ‘dumb’ : just run by each date in the interval, decide if it is a workday and if it is, count it:
Read the rest of this entry »

In an earlier post I have drawn attention to the VBA addin for steam properties as written by Bernhard Spang from Hamburg in Germany. Based on the popularity of that post I have decided to make another spreadheet available that is in turn based on this VBA code.

Steam Calcs V1.0 is a spreadsheet that allows the calculation of three quite common steam operations:

  • Adiabatic letdown (through a valve)
  • Isentropic letdown (through an expander)
  • Desuperheating with condensate
Download Steam Calcs V1.0

Read the rest of this entry »

When developing applications in Excel using Visual Basic (VBA), it is important to produce neat code that is efficient, reliable, verifyable and maintainable. As a result you will need to adopt a systematic approach to variable declaration and naming, as well as for structuring your code and commenting it. 

As it is seldom advantageous to reinvent the wheel, it is suggested you use conventions that have been developed and validated by others in the VBA scene. 
Read the rest of this entry »

In case you’re doing some heavy duty VBA coding projects, you’ll find that your files may start to bloat. In that case you’ll be interested in the VBA Code Cleaner addin on Bob Bovey’s Appspro site.

Read the rest of this entry »

Categories

    Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 72 bytes) in /home/pzvn313114/domains/falchemist.com/public_html/wordpress/wp-content/themes/template_v007a/functions.php on line 958