Fluorescent Dyes:
Introduction - Quantifying calcium - Definitions&References - Protocols&Examples

Protocols & Examples

Excel macros - Basic

Life is easier if we do not have to repeat the same thing over and over again. For this reason macros were invented.

One of the most popular software packages in the market is Microsoft Office®, which includes Excel for manipulating data. This program can be used to arrange and represent quantification data saved in text format, so some tips on how to make macros for Excel are detailed here. Software for acquiring and processing digital images usually also includes a self-built macro-recorder, but each trademark has its particular system.

Though writing VisualBasic macros for Microsoft Excel might look very complicated; using the self-built macro-recorder makes it quite easy. Just open Excel, click on Record macro icon (activate Visual Basic Applications, VBA, tool bar or choose Tools > Macro > Record), give a name to your new macro, choose a location to store it (your personal book is a good place) and make the sequence of commands that you want to record. Once finished, click stop recording. Playing the macro again is as easy as clicking "play macro".

To visualize the code, just click on Visual Basic Editor. Each macro start by a Sub statement plus the macro name and finishes with End Sub. Comments start with a ' sign (they are shown in green).

A macro or Sub statement called "MoveColumn" that cut the content of column G and H, paste it in column L and M and then eliminate the empty G and H column looks like this:

Sub MoveColumn()   Indicates were the macro starts and its name


' MoveColumn Macro

' Macro grabada el 11/06/2004 por Montse


  Comments about macro (additional comments can be added after a line just writing a ' sign)
Selection.Delete Shift:=xlToLeft
  Commands to execute: select the columns, cut them, select the destination columns, paste the previous ones, select the empty columns and delete them shifting the rest of the sheet to the left. This was written directly by the Macro recorder!
End Sub   Indicates were the macro finishes

Though it does not look very useful at first glance, if you have a lot of files with column that need to be ordered, you will love a macro like this one (adapted to your needs, of course). Just record the macro while you order the first file and then apply it to the other files.

Unfortunately, not all macros are so easy to build. Modifications in the code allow introducing variables, loops, message box, etc. Tips on how to write code can be found in Excel help. Some useful tips and code for opening text files and ordering data are included here.

Be careful! different Excel version and/or different languages, can render different results (e.g. dots and comma are sometimes misunderstood depending on the "Regional Settings"). We use the Spanish version of Microsoft® Excel 2002 and Spanish settings.

If you are interested in learning a bit more, you can visit, for example, VBATutor for Office97.


Next >>


Fluorescent Proteins


Calcium Tutorial Home