Is There a Dinosaur Sitting on Your Excel File?

I am handing few big excel data files lately and these files are loaded with vlookups, sumifs,sumproduct and truckload of loopy VBA code.

Running the VBA code was pain, excel drags as if a dinosaur is sitting on it. The files were so muddled that even the excel in-built functions to convert a range of cells to number takes 5 mins.

Trying to optimise the vba application in the excel file, I did the usual Application.ScreenUpdating = False and Application.Calculation = xlManual in the VBA to speed up all macros. This helped but not entirely.

The key to increasing speed of calculation and improving performance of your excel worksheet and VBA code lies in understanding how Excel calculates and re-calculates each cell and resolves dependencies. Excel recalculate’s whenever an event occurs (for example a cell is changed, a VBA code calls for re calculation or anything else). For each change the entire calculation process is carried out, so the trick to gain major benefit came from looking at all the formulas and optimising them.

It’s not easy but there are many fantastic resources that can help. Here are some of my favourite ones.

75 Excel Speeding Tips
Its a long List, many repeated but worth a visit.

Optimize VBA
Excellent tips to optimize vba code. You should do this before you move onto anything.

How to speed up calculation and Improve performance of Excel and VBA
These are excellent tips and must visit to know more about the calculations steps followed by Excel.

Advertisement

Pareto Chart in Excel

Creating pareto chart in excel requires multiple steps as there is no direct method to accomplish this in excel. Here’s a vba a script that creates a pareto chart in excel.

How to use the script?
Select the data and run the ParetoChart sub. That’s it. The scripts always assumes the first row is headers. Don’t forget that.

And if you need Boxplot in excel, this is where you should go.

Here’s the code….

Slowing Down is the Key…..

Slowing down is the key to increased speed.

Past couple of months I was dabbing with fortran GUI and trying pgplot graphics library. I have produced gui’s in c, vb and then integrated them with fortran, but creating GUIs from fortran was new to me.

As the exploration began I took the fire aim adjust approach!! Dived deep into the tutorials and anything that I could lay my hands on.

Quickly from tutorials I graduated to actually creating my own little programs. This went on for a couple of months.

In the beginning I was sprinting as hard as possible. Learning, doing, getting stuck, reading and then doing again. The pace was fast.

But as I become comfortable, my approach shifted. I slowed.

I wrote a program and pondered how and what am I actually doing. This slowing down and pondering doubled my learning. It felt like I was learning at greater pace with this slowdown.

So the technique I want to advocate to anyone learning a new programming language, a new analysis tool or cad software, is to sprint in the first few weeks. Race and learn as much as you are able to handle. Dive deep and continue the pace as long as you are able to.

When exhaustion, sense of acheivement begins to creep in, slow down. Become deliberate in what you do? Question why and what you are doing?

I hope applying this method will help you as much as it has helped me.

What are your views, do let it out in the comments.

Secret Love Letter

Well,  this is the program i used to write emails to my wife when we got engaged. Written for the sole purpose of keeping the prying eyes of my colleagues in NAL.

Simple VB program, using two different fonts to achieve the effect. Simplest method of saving myself from the friendly teasing.

As you see, aptly named “No Tension” 🙂 Unfortunately, don’t have the source code but found the tiny program.  See the link below to download.

 

 

 

Download the program (20kb)  or as zipped file

Fortran DLL and Excel -a 4 step video tutorial

Ever since I had used the screen-o-matic screen-cast, I was itching to use it again to create something.

Today morning finding some time, I decided to go ahead, but then stumbled on these awesome four step video tutorials on mixing fortran and excel.

This is what I was going to screen-cast, but this videos do an excellent job.

So please enjoy and learn.

Step 1

Step 2

Step 3

Step 4

A habit and International Standard Atmosphere

Now it’s a habit.

If you are into programming then you might be all too familiar with the first hello world program that we all start our learning with.

This was true for me till I got into aerospace, now my first program is always the program to calculate temperature at an altitude for international standard atmosphere.

This has become a habit. I do this for all the new programming languages that I learnt. Perl, python, VBA, vb, java, etc you name it and my first program in all this language was ISA.

The other day I was browsing my old desktop and found this long forgotten visual basic excel program that I wrote 6+ years back. It’s a small program that calculates and gives temperature and pressure at different altitude according to international standard atmosphere.

Download the excel file here. ISA is coded as UDF in excel.