what to learn next - after Excel & Excel VBA?

[Fazza]

Hi,

Having obtained a good skill level in Excel & Excel VBA, and adequate ADO and SQL, I'm wondering what would be a useful next step.

I can imagine getting into MS Access or other databases - but don't know which. Though I am not targeting that route, I can see it as a likely possibility - it does not particularly excite me though. Whereas using Excel & Excel VBA is enjoyable and likely to remain fundamental to my computer usage for some time yet.

My main interest is in keeping up with technology - such as if there is a new direction in programming - and developing my skills for working with medium to large amounts of data. At one level I can imagine working more with Excel & Access together to solve tasks but I'm thinking this would be not too new or interesting and that I'd want to take a bigger step.

Would appreciate comments. Regards, Fazza

 

[Noel Holland]

Is this stuff you've worked closely on? F

Yes but I can't give too detailed an example since it was done for my employers as part of a study for a client.

The work I did was under Visio 2003 which had rather clunky interfaces with Excel. As I understand it the interfaces between visio and external data sources (specifically excel and access) were greatly improved but I moved onto other projects and never got a chance to improve my understanding of the systems.

Straight out of the box Visio can take an Excel spreadsheet with personnel data and form an organisation chart using an inbuilt wizard. The crux is to add an extra column after the key identifier which details the key identifier of the person that individual reports to.

From there it's not hard to adapt the standard organisation chart system to include custom properties that relate to other information stored in the excel spread. E.g. you can have the persons office location stored which allows you to have Visio redraw the org chart showing only personnel working at a specific office, or the departmental tag can allow you to redraft to show just the sales organisation.

However you need to resist the urge to build too much flexibility into such a system. Things that appear easy to do on a powerpoint org chart are a devil to show on an adaptive visio chart. The worst area is dual reporting lines or matrix working. I got out of that one by adding a second reporting column and building a switch into excel to swap out each reporting structure depending on what form I wanted the org chart to show.

The real crux of getting Visio and Excel to work well together is the same as it is for getting access and excel to work together - work out clearly in advance the data structure you want to pass back and forth between the systems.

One area I never quite got around to doing was to take the org chart and excel spread and link them to an MS project model. I modelled the resourcing timelines inside excel which gave me the results I wanted but I'd always planed to expand the solution to export the resource timeline data to Project.

Now if you REALLY want an idea to blow your socks off why not consider a conceptual design and costing tool using visio and excel. I've seen an example visio template which was designed for breadboarding electronics. The designer lays out the diagram of the circuit he wants to design from a template of parts. Each part has a set of custom properties already allocated. At the end of the design process the visio database is exported to an excel spread and the excel spread totals up the cost of the parts, and works out the manhours and additional costs to build the circuit. I know of at least one major engineering design company that is developing a similar tool but instead of template of electronics it's a template of major process plant equipment. Layout the process flow diagram in visio, then export to a prebuilt excel estimating spread and it churns out a conceptual estimate for the total installed cost of the plant. The customer wants to find out the cost of running the same plant but using smaller multiple trains? Just re-layout the process flow diagram in visio and a new TIC gets spat out by excel.

You can get a rough idea of how such a system works by looking at the MS website for Visio.
http://office.microsoft.com/en-us/visio/HA010774611033.aspx

 

[Fazza]

Thanks very much, Noel. Some great ideas there. Regards, Fazza

 

[dk]

There are some interesting ideas here, particularly about getting Visio and Excel to work together (I'm also one of those people who use it just to draw flowcharts).

I think you can't go wrong learning at least the basics of how databases work including database design, query development, maintenance, tuning, etc. You'll gain much more credibility if you can get onto one of the big-boy database systems such as SQL Server or Oracle.
These are such massively powerful and sophisticated systems that you could spend years playing around and learning new things. They also both have their own programming languages so if whipping up code is your thing then you can still do this.

Having said that though, Access is perfect for learning and still suitable for small scale applications (with < 10 users for examples).

One of the things I've enjoyed doing over the last few years is combining Excel with Oracle to create some amazingly quick solutions. For example, when I joined my last company they were creating a pivot table report that was linked to Access, which in turn was linked via ODBC to an Oracle database - it tooks 45 minutes to refresh. Initially I changed the system so that the pivot table refreshed straight out of Oracle and this dropped it to 2 minutes. I then created a view (Oracle query) so that more of the data crunching was done on the server. That view, combined with some judicious index tuning meant that the 45 minute was dropped to less than 30 seconds by the time i finished. This sort of stuff is extremely satisfying and if data crunching is your thing ( as you mentioned in your original post) then I don't think you'll go far wrong buying a database book or two.

By the way, what's your line of work?

 

[Fazza]

Thanks, dk.

I can relate to your comments regarding the satisfaction from implementing good solutions. And you've got some great results there.

I'm OK on the basics of databases and do a reasonable amount of SQL. I am always impressed how powerful it can be and do find that enjoyable. However I am by nature more of a numerical person and less interested in databases.

My work is in engineering offices - my training was originally in engineering. I have good Excel skills and for a few years now have worked with cost estimating data. There are grey haired old estimators who know what they want to do but don't have the data handling skills - I manipulate / work the data to do what needs to be done. Some of these fellows are very good at their job and appreciate the value I bring to the team; so together we do some really first class work. There are all number of spreadsheets all the time for various things along the way and always seems to be one database that houses the final estimate. Most work is done outside the database and mostly the database is little more than a reporting tool for the cost estimate, anyway. The projects are largish. The one we just put together is of the order of 10 billion AUD; so there is a reasonable amount of data. The role is really suited to someone from an engineering background & there are not too many people around with that background and the database / Excel skills too. So I am well paid. I am not seeking a career though; did retire a few years ago though that didn't last long. The current boom in engineering work in Perth sees anyone that can breath being employed - anyone really good is in very high demand; I was soon coaxed back to work. I wasn't prepared (hadn't adequately planned) for retirement and work is enjoyable for me, and so I can dabble in this sort of thing for a couple of decades yet if I want. There is plenty of scope for using VBA in my role; we obtain lots of data in all sorts of formats - it is amazing how poorly structured some work practices/outputs are - and there are the regular major spreadsheets built with plenty of code, and opportunities to set up some 'magical' spreadsheets with some ADO to do wonders.

I seem inexorably to be drifting closer to getting serious about some work in MS Access or a more serious database; at heart though, I prefer working with numbers... And, I certainly enjoy programming with Excel VBA.

Thanks for your interest and comments. Regards, Fazza

 

[DonkeyOte]

Not trying to hijack Visio idea etc ... but just to add another idea to the mix... how about combining both db idea AND .net by building an asp.net web app ?

Building web apps in say ASP.NET would allow you to combine the .NET side of things with a db backend be it .mdb, SQLSvr, MySQL, Oracle etc... I opted for ASP.NET as opposed to C.NET because of my knowledge of VBA -- made the entire learning process pretty easy (IMO) ... and building custom web apps is pretty interesting. I'm no expert but I can build pretty decent apps.

In my last job I basically built a "suite" of systems for my last company in ASP.NET (old 1.1 Framework) with MySQL 4.0 Svr -- apps ranging initially from the purely Financial: "Real Time" Cost Management & Forecasting, Project Forecasting, Sales Forecasting, T&E systems etc... to non-Finance apps for other areas of the organisation (HR System for ex.)... [I am a Finance bod]

Beauty now is of course the 1.1 framework is outdated as is MySQL 4.0 -- latter versions of MySQL db offering stored procedures etc making it more attractive than before -- people are also now increasingly using AJAX with .NET stuff as it's really simple to implement (apparently).

I myself am looking to start a new project in ASP.NET upgrading the apps I built into a cohesive "product" via some sort of portal... I still like VBA as I can use it to build tools in conjuction with the web app to allow more customised reporting (PTs etc..) whereas I like the web client approach for data entry as security is so much better and it's much easier/quicker to control access to confidential info and control data input etc...

 

[Fazza]

Thank you for your comments, Luke. I don't even understand what you are proposing! It will take me some time to understand your suggestion. As a dedicated Excel user, I'm good with Excel. I am not familiar with building web apps, ASP.NET, .NET, SQLSvr, MySQL, Oracle, C.NET, etc, etc. Regards, Fazza

 

[DonkeyOte]

Ha, sorry... what I meant was a few years ago my boss (a very good VBA-er himself) basically said to me "... this is all very good but what we'd really like is having all this stuff online...can you do it?" ... I said "...well I could maybe do it in Access which we could distribute" to which he replied "No... licencing costs make that cost-ineffective...use MySQL as your DB as it's free and put it on the web that's free too!!" (my company was a bit tight donkey'd). Suffice to say at this point I basically cra**ed myself as I had no idea where to start and didn't want to look bad!

I decided as foolish as this may have been at the start to give myself the task of building a web forum in my own time that my old college friends and I could use to keep in touch... this would I felt (at the time) help me get into the swing of how to design and build out a database given requirements of the forum whilst simultaneously learning the basics of asp WITHOUT the end result being of any real importance unlike the stuff I would have to build at work -- I knew then that once I got to grips with it I could then start doing the same thing for work but with better knowledge and less mistakes.

Long and short of it was this... I found learning ASP (and subsequently ASP.NET) relatively straightforward given it's similarity to VBA... and I found that learning SQL was pretty easy too as it's so logical... the designing of the DB itself was interesting and to this day I still change things as I think of better ways to do it... I'm NOT a DBA (sadly).

I guess what I'm saying is I personally found learning how to use a DB (MySQL & subsequently SQLSvr) that much easier as I was using it in conjunction with something else (ie my first web application) that in turn had a very strong influence on how I need to design it in the first instance. I just felt this was a good way of doing it rather than trying to learn the nuances of the DB in a self contained manner.

I also was trying to make the point that you mentioned in one of your earlier posts the idea of learning .NET... obviously ASP.NET is not the equivalent of .NET as VBA is not the equivalent of VB BUT it does expose you to a lot of objects and things and the power of what this stuff can do... it really is quite cool at times.... so I just thought that if you were so inclined you could tell yourself I'm going to build my first ASP.NET website and to do that I am going to use a DB (MS Access would be fine here also)... this way I learn 2 things at the same time.

Another long post... I'm quite into all this stuff as you can see which is pretty tragic at 8am on a Sunday morning.



If you do decide to look into there are obviously lots of resources online for ASP.NET --> my favourit resource was:

http://aspnet.4guysfromrolla.com/default.aspx

For MySQL:

http://www.mysql.com/

Not to "brown nose" too much but your XL skills far exceed mine and I found the above pretty darn easy to pick up at a basic level and as such I'm sure you would have a similar experience to me if not moreso.

 

[Fazza]

That I can understand, thanks, Luke.

Well if MySQL is free, maybe I should get a copy for home [I'm tight donkey'd too] - IT dept at work I think would not permit anything to be added to system - and practice using that. Bound to learn something useful. Building something for a web site would be new to me but I can imagine uses. Even just publishing some Excel stuff so that I could refer to that when answering forum posts; save on repeatedly posting about queries & pivot tables using external data and parameterised queries, ADO, etc. Something for me to think about, thank you.

 

[DonkeyOte]

Even just publishing some Excel stuff so that I could refer to that when answering forum posts; save on repeatedly posting about queries & pivot tables using external data and parameterised queries, ADO, etc.

Fazza, I think that's a resource all of us here would appreciate to have on tap ! An excellent idea...

 

[Fazza]

Yes, there are a few sites with that information but some more would be good. Then again, many times when I describe these approaches in forum threads there isn't much interest. Too many times people would rather use lots of array formulas - and then they return later wondering why the file takes ages to calculate...