Tutorial by Topics: excel



Several Excel formulas deal with rounding and precision of non-integer numbers. This is separate from using cell formatting that affects the display of numeric data. In some cases just using cell formatting is sufficient, but in complex calculations, strict rules for rounding and precision are required to obtain consistent and correct results.

These examples introduce the most used commands of Perl to manipulate Excel via Win32::OLE module.

Examples on how to save the created Excel sheet to the Disk or send it to the Browser.
How to style Cells with font types, background color, border styles etc.

Sometimes the returned search results in a Mass Update isn't the same as the results in a standard search, this is due to some limitations in a Mass Update Search. An example of this is Rev Rec Journal entries. Therefore, the workaround for this was to get the data from the standard saved search and use a script to read the excel data and update, as opposed to using the mass update feature.

A more versatile alternative to VLOOKUP. An Index Match packs the power of a Vlookup and Hlookup in one formula. You also do not need to know which number column/row the information is. Due to this, deleting columns/rows will not mess up the formula.

This topic is about working with tables in VBA, and assumes knowledge of Excel Tables. In VBA, or rather the Excel Object Model, tables are known as ListObjects. The most frequently used properties of a ListObject are ListRow(s), ListColumn(s), DataBodyRange, Range and HeaderRowRange.

Excel-VBA Optimization refers also to coding better error handling by documentation and additional details. This is shown here.

Data validation allows user to create a drop-down list and restrict values in the cell to these entries. Due to limitation Excel can't bind more than 256 characters programmatically. To bind more than 256 characters one can follow explained approach.


Page 1 of 2