How to : Program a MS Excel Form

If you are trying to make an excel form, chances are you lost at the many objects lying around on your form and are baffled as to how all the pieces fit together.

They key thing in writing a form is understanding how many objects are used and what minimum code must support each object and action. If you are lost and dont know what piece of code to write next, here are some simple guidelines I follow myself in programming my forms for performing engineering calculations.

1. Throw all objects you want on a form. Inputs and Outputs.

2. For an engineering calculation, if any input parameter changes, all outputs must vanish. Otherwise your form risks the possibility of displaying the incorrect value from a previous calculation before the current calculation is performed.

3. Initialize the form using built in “UserForm_Initialize()” function.

4. Initialisation of the form can be performed by a separate macro which pouplates the initial values to be displayed.

5. Use the most common three buttons as set in 6, 7 and 8

6. “Close” button unloads the form

7. “Clear” button can be used to invoke the initialization macro or clear the all input and output fields by making them blank.

8. “Calculate” button begins the calculation sequences.

9. When starting a calculation, first step is to check the inputs.

10. Check the inputs one by one. If inputs are not correct dont perform the calculation. If inputs are correct, read and interpret the inputs alongside.

11. Perform calculations. Use UDFs as necessary.

12. Estimate results. Diplay results as Label Captions.

Have Fun!

What does it take to make one A4 size paper?

We start off by doing a small calculation from a reputed text book. The text book is Shreve’s Chemical Process Industries, which is a textbook adopted in the chemical engineering curriculum of many reputed colleges.

An A4 size office paper of normal quality weighs 5 grams. It is easy for one to pro-rate the above table and estimate what it takes to make 5 grams of paper.

Based on 1982 data, to make an A4 paper you would need:

  • 22.5 g  Chemicals
  • 665 ml  Water
  • 3.5 ml  Oil or 5 grams of coal
  • 20 cc Wood
  • 23.76 kJ Power

Well, the story does not stop here. I know that the paper industry has adopted better manufacturing methods and today the specific consumption for making paper could be a little different. Today’s processes use significant amounts of recycle paper, use lesser water. Again, the amount of paper recycled and water conserved in different plants /countries is different, so it becomes difficult to establish an exact global average in a brief article.

Nevertheless, a few parallels can be drawn.

Every time you waste an A4 paper at office, conservatively, I can assume that, you also waste a large cup of water, a spoon full of coal (or oil), another spoonful of various chemicals, energy sufficient to keep a 40 W bulb glowing for 10 minutes and wood whose weight could be 2-4 times the weight of the paper, depending on the amount of recycle paper used.

Every time you throw a piece of paper into a dust bin, imagine yourself wasting all the other resources as well. I’m sure you’ll find it a lot easier to save paper.

VBA support in OpenOffice.Org Calc

For many years till yesterday, I knew about OpenOffice.Org Calc. Today, I’m excited about it.

I tried OpenOffice many years ago. It could do few things back then, but I immediately dismissed as inadequate for my needs. I have always been a fan of what VBA could do for Excel.

Last week, I completed the most complex piece of VBA application I’d ever written. I am a VBA newbie and it took me a week  to finish my code that could be printed on 28 A4 pages. My spreadsheet till date works flawlessly on XP/Office 2003 like it is supposed to, but I wrote most of my code in Vista/Office 2007.

When I upgraded to Debian Squeeze, 3.1.1 came with it. I checked out the new version and was impressed that one could write macros in Python, Javascript, BeanShell in addition to BASIC.

I tried to open my most complex piece of VBA code in and I was bombarded with hundreds of repetitive error messages that I had to kill the spreadsheet from the command line. I was convinced that VBA and OOoCalc are not compatible, till I discovered this site by accident when trying to learn to write Javascript Macros in OOo.

OK. All I needed to add was “Option VBA Support 1″. That didn’t sound too difficult, so I tried opening my spreadsheet  again and had to again kill everything from command line.

Some thing was not right and I wasn’t being able to run VBA despite the promised compatibility.

So I decided to go slow. No matter how many times, I couldn’t run the macros when I opened the native excel file. So I decided to open the excel file with macros disabled. That let me open the spreadsheet, but nothing was working. I found that OOoCalc automatically had added “Option VBA Support 1″ to my modules.

I saved the file as .ODS and proceeded to debug by enabling macros again. This time, errors didn’t come in a flood.They came one at a time and it was easier to debug.

The first set of errors related to variables which did not have an explicit Dim statement. VBA in Excel seemed more forgiving in handling varaibles without proper Dim statements. But OOo Calc didn’t like it. So I ended up adding a few Dim statements for some variables.

The next set of errors was with Excel UDF names. OOo Calc likes to see Functions being invoked with uppercase letters. So if you’d used lower case letters in Excel, they would show up as errors. This could be easily fixed by use of find and replace feature.

With just these two things, fixed my new .ODS file worked perfectly. I then saved it as .xls and re-opened the .xls and everything was still working. Though OOo Calc  documentation still says that not all VBA features are supported, VBA runs well on OOoCalc for all practical engineering calculations. So now is the time for me to make the switch.

Now if you have difficulties, in getting  your VBA code to work  in OOo Calc, dont give up soon.

Educational objectives

There are said to be six major areas in the cognitive development process that includes: knowledge, comprehension, application, analysis, synthesis, and evaluation.

1. Knowledge is remembering previously learned material, from specific facts to complete theories.
2. Comprehension is the ability to grasp the meaning of material.
3. Application is the ability to use learned material in and concrete situations. This may include the application of such things as rules, methods, and concepts.
4. Analysis is defined as the ability to break down a problem into component parts so that its organization is understood.
5. Synthesis is defined as the ability to put parts together to form a new whole view or aspect
6. Evaluation is defined as the ability to judge the value of materials for a given purpose

Black Web, bright future

Anand Patil, my college mate, sent us all an email.

When your screen is white, being it an empty word page, or the Google page, your computer consumes 74 watts, and when its black it consumes only 59 watts.

Mark Ontkush wrote an article about the energy saving that would be achieved if Google had a black screen, taking in account the huge number of page views, according to his calculations, 750 mega watts / hour per year would be saved.

In a response to this article Google created a black version of its search engine, called Blackle, with the exact same functions as the white version, but with a lower energy consumption check

for more info …

We can shut down a few power stations and pollute less without significant loss of fucntionality only if all our web pages are black.

Please spread the word.

Online teaching – IITs

The IITs have taken up an initiative of starting online teaching and thus have started offering course materials online for every engineering stream. Many professors from all the IITs have provided course materials for each chapter and each subject.

One has to register at the link provided below and can access the course material. Every Chapter has been described with diagrams and charts. Please spread this message to everyone, as many can benefit from this program taken up by the government and IIT.

This is just a trial period going on and hence I request everyone to register at the link given.

1] Go to http://nptel.
2] Click on Courses
3] Sign up as a NEW USER
4] And one can access any course material.

Please spread the word, so that this initiative benefits as many students as possible.

Marvelous piece of engineering

This bridge is between Sweden and Denmark.

This bridge takes in vehicles from the surface, leads them on to a underwater tunnel, then brings them back to surface on the other side. The purpose of the underwater portion is to let ships sail across the bridge.













Eiffel tower

The Eiffel Tower is 990 feet tall. It is constructed of wrought iron, was built in 1887-89, and was designed by the highly respected engineer Gustave Eiffel. The original design concept for E.T. came from Maurice Koechlin and Emile Nougire, two junior engineers who performed the preliminary calculations.

The tower was almost twice as tall as any other man-made structure that preceeded it. The tallest man-made structures pre-dating E.T. were the Great Pyramids (482 feet), and the Washington Monument (554 feet). The structure was designed to resist lateral wind pressures of 82 PSF near its top and 69 PSF near its base. To put this in perspective, most American and English engineers were designing for lateral wind pressures of 25 to 30 PSF for most of the tallest building and bridge structures constructed during the same era. Many bridge failures occurred in the 1800s due to a complete lack of consideration of any lateral load whatsoever.

  • The structure weighs approximately 9547 tons (19,000,000 pounds).
  • Consists of more than 18,000 pieces and 2.5 million rivets.
  • It took 100 fabricators and 130 erectors 15 months to build.
  • The fabrication details consisted of more than 5,300 drawings.
  • 45 tons of zinc-rich paint are applied to to the tower as rust inhibitor every 7 years.
  • There are 1,792 steps to the top of the tower.
  • Summer temperature increases the height of the structure by about 7 inches.
  • The ornamental arch below the first platform level serves no structural purpose and was added to the tower after the first platform level had been erected.

It was built in celebration of the 100th anniversary of the French Revolution. Just as in current times, E.T. had its detractors. Opponents of the tower's construction referred to Eiffel Tower as a "tragic lamppost", inverted torch-holder", and the "Grand Suppositaire". When first proposed by Eiffel, E.T. was denounced to as an eyesore; "A dishonour to Paris and a ridiculously dizzy tower like some gigantic and sombre factory chimney".

Eiffel tower was constructed at a cost of 7,799,401.31 French francs — about 1 million more than Eiffel's estimate.During the first five months following its completion in 1889, 1.9 million visitors were charged 5 francs for a trip to the top of the tower. Lesser fees were charged for trips to the lower platform levels. Seventy-five percent (75%) of the cost of the tower was recovered in the first year following its completion.The 1889 visitor number stood as a record until tourism increased in the 1960s. In 1988 (the centenary of its construction) a total of 4.5 million people visited tower.

The tower was considered temporary and its demolition was initially planned for in the year 1909. Luckily, wiser minds prevailed and the structure is still standing today. It is one of the great engineering and artisitc feats of mankind. This is especially true when one considers that G. Eiffel was a ground breaker in that very little was known of the lateral force applied to trussed towers by action of the wind.

  • Eiffel's full name is Alexandre Gustave Eiffel.
  • He was educated as a chemical engineer.
  • He was born in Dijon, France on 15 December 1832.
  • Eiffel worked for a railway equipment manufacturer in France after graduating as a chemical engineer in France.
  • Eiffel gave up chemistry for civil engineering at the age of 25 when he was put in charge of the construction of the Garonne River Bridge construction at Bordeaux.
  • His successful completion of the Garonne River bridge (on-time and on-budget), one of the largest structures of its day, helped establish Eiffel as one of the pre-emminent engineers of his time.
  • One of Eiffel's less well known structures is the Garabit Bridge in France.
  • Eiffel completed its design and construction in 1884.
  • This structure stands second only to Eiffel's great tower as a demonstration of his skill and ingenuity as an engineer.
  • Upon its completion, the viaduct was the highest arched bridge in the world at a height of 400 feet above the Truyere River. The arch spans 541 feet and supports a railway deck 1,850 feet in length. One can almost think of the bridge structure as two 925-foot tall towers laid on their sides. Iron and steel towers and bridges are similar in design with respect to the application of live loads that are resisted by forces within truss and cable systems that have the benefit of very little structural redundancy.
  • Gustave Eiffel died at the age of 91 on 27 December 1923.