Anyone here write VBA for Excel?

[Phil_S]

I just spent 2 days teaching this to myself. It's been on my list of things to do for about 3 decades, but I just didn't have the right project or enough time, at the same time. I reduced what is monthly about 1.5 days work to mere minutes. Maybe I can sell this to someone? Not here...

[brentm]

I've had some fun in VBA and visual basic!

I take it you're pulling data from multiple sources, crunching it, and returning results? If you're pulling data from SQL. I like to build the SQL connection in VBA instead of relying on ODBC data sources (and their names). It makes it much more "portable".

You may not be able to sell the code to anyone, but your problem solving skills and business intelligence skills are valuable! Every office needs someone like you!

Now time to do the same thing in Powershell It'll be 1/4 the lines of code and 100x the headache.

[Phil_S]

How I wish I could have done this with SQL and ODBC! SQL is a snap compared to VBA. I am pulling data from QuickBooks Point of Sale and porting it to Abila MIP Fund Accounting. MIP runs on MSSQL, and requires a basic flat file structure to read in externally generated data. QB is most definitely not a SQL compliant database. In fact, it's data structure is about as odd as it gets. There is a product called QODBC for QB, but it costs about $500 and the nonprofit organization I did this for can't afford it. The file from QB POS is a mess, with mixed data in more than one column due to a multi-row record format -- essentially one header record (the Cash or debit entry), and one or more detail records (Revenue or the credit entry; actually equal to Qty * Total). Then, to make matters worse, Tax, Shipping, and Discount each has its own column, requiring 3 rows to be inserted and moving each amount to the debit or credit column as appropriate.

I wasn't thinking about selling this particular set of macro procedures. Rather was wondering about the market for people who can code in VBA.

What I wrote is not particularly elegant. I'm sure I could clean it up if I had the time. It does, however, work flawlessly and turns a POS (piece of sh*t) into something I can use. More importantly, it provides real infrastructure to the work, so the organization now has a consistent and reliable conduit for the accounting data. You should see the mess I found in the way they were dealing with this! This is an existing client and ordinarily I'd have charged something for the work. In this case, I determined that it would not be billable because I initiated without a proper work order and I undertook a project I didn't know for sure I could execute with success. So, I consider it a good trade. I learned a useful skill. They got a work product, and I can stop bitching about the problem of the missing conduit being a productivity problem.

[sliberty]

Once upon a time, when I still worked for a software consultancy, we would see a call for VBA folks every now and then. As most application development has moved to the web, there is less and less of this however. It would mostly come up in situations just like yours I suspect - moving accounting / financial data around.

I did some VBA back in the late 1990's to take basic financial data in spreadsheets and turn them into financial statements for inclusion in prettier Word documents. A fun project actually, and it solved tonms of manual manipulation time for the accountants. But as a VB programmer at the time, I found VBA to be a bit limiting, and also a bit challenging since it is really all about the object model (cells, rows, columns, etc.) which I was learning as I went along.

[Ron Worley]

You doods are way smarter than me, I have no idea what language y'all are speaking!

[NickC]

In my organization we pull Quickbooks data from about 150 reporting units into Qvinci, and prepare a variety of consolidated financial statements, and management reports, from there. It used to all be done manually: paper reports ----> keyed to excel ----> summarized data keyed to Access DB. It took months each year, and even relatively simple consolidations took days or weeks. Leveraging machines to do the lions share of the work is much better.