I’m not using NebulaXLite? Traitor!

Please don’t beat me. I confess. I’ve been doing extensive Excel development without NebulaXLite. But… there’s a good reason.

The “Lite” in NebulaXLite is there quite intentionally. We fully acknowledge that this product fits for a wide range of projects but it has specific limitations compared to something more “heavy”. I try to use NebulaXLite wherever possible, but I know that there are also times when it should not be used. Think of it like a carpenter opening his box of tools and selecting a specific hammer. To a guy like me, all hammers bash stuff down, so they’re all alike, right? Well, the carpenter can tell you reasons why specific hammers are good for specific tasks – and that’s how I see software tools, including NebulaXLite.

The focus of NebulaXLite is making simple CSV reports more attractive. More specifically, I think we’re just putting another nail in the MV coffin if management asks for Excel and someone gives them a CSV export. When people say they want Excel, they want something that is both visually appealing and that makes use of the Excel product. Excel has been in extensive development and use for over a decade – it does more than put data into columns and rows. We had columns and rows before Excel. That’s not what more sophisticated knowledge-workers want when they say they want “Excel”.

With NebulaXLite you can add formulas to a report and make it very dynamic. Sure, you can do this in CSV too by prefixing your text with an “=” sign. But most people who deliver CSV aren’t familiar with the wide range of functions in Excel, cell ranges, named ranges, array formulas or other concepts that are more familiar to people who use the software on a daily basis. If you need help making use of this functionality with NebulaXLite, we’ll be happy to help.

But most people like NebulaXLite because it makes reports look great, with variable width columns, variable height rows, colors, borders, fonts and text styles. If you want to impress management, this is a great way to do it.

So we think NebulaXLite satisfies over 90% of the requirements people express for “Excel”. And remember that NebulaXLite produces documents which are equally compatible with Google Docs and Open Office with absolutely no additional effort required!

But what about other reports? The “not so Lite stuff”?

I was recently asked to create financial statements for one of our clients. Specifically a Balance Statement, Income Statement, and Cash Flow Statement. It would be easy to use NebulaXLite to produce a workbook that had a separate worksheet for each fiscal period. But we wanted to do this with just one operational worksheet (the attractive one that people look at) and one worksheet that has raw data.

How? Excel allows you to create dropdown list selectors where the value of the dropdown is the value in the cell. All other cells can then use that value to determine which cells to use in the data worksheet. But doing this requires much more than some sophisticated formulas. We realized up-front that creating the operational worksheet itself required complex macros that used the data worksheet as a source. These macros create the operational worksheet using the data, insert formulas to make it dynamic, and insert the dropdown list to make the fiscal period selectable. When delivered, the workbook has an operational worksheet, the data worksheet, and a lot of macros that execute to dynamically manipulate the data when the period is changed.

Considerations

Why not use VSTO or similar development tools to craft the Excel workbook? This would require installing an executable at a client site and I wanted to avoid this. The current solution consists of nothing more than an Excel workbook.

What about macro protection and other security issues? Macro security isn’t intended to completely prohibit macro usage, but to prevent execution of unknown/bad macros. Since this is business software we’ve created documentation that explains that macros are required and what to do if macro security warns of its presence.

Macros don’t work with Open Office. That’s not a concern here as the request was for Excel. Macros also don’t work with Mac after v2004. We can’t let that stop us from presenting a solution.

But it’s not NebulaXLite!

Yeah, I know. The whole point of this blog entry is to confirm that NebulaXLite isn’t the right tool for all possible tasks. Recognizing that is important and I’ll be happy to help people figure out when it’s not the right tool. But remember that for around 90% of the tasks where people want a spreadsheet, NebulaXLite really is perfect. If you do find a task that requires more sophistication, as we’ve done for our other client where, we can help to create a solution that matches the requirements, or at least to help point you to the right options. For example, NebulaXLite (which generates spreadsheets from the MV BASIC tier) does nothing to help you to reach back into the MV DBMS to keep your spreadsheets updated, or to push data from the spreadsheet back into MV, but that’s why we’re creating NebulaXChange, which is an Excel-based component that connects into MV apps.

It may take a while but I’ll see if I can post some screenshots of the reports that have been created so that you can get a better idea for what’s possible with Excel – and remember that we’re using MV data with these worksheets. I just need to remove the company name and tweak some of the data before publishing. _😉