# what to learn next - after Excel & Excel VBA?



## Fazza (Jul 23, 2008)

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


----------



## Joe4 (Jul 23, 2008)

Fazza,

I have a similar background.  Started in Excel, then Excel VBA, then Access, then Access VBA, then SQL (and a little Crystal Reports).  

One thing that I have started to dabble in that I see a lot of good uses for is Windows Scripting/VB Scripting.  Another big thing which keeps popping up that I think I will eventually need to learn is XML.

At least that is where it looks like technology is leading me...


----------



## Fazza (Jul 23, 2008)

Thanks, Joe. XML - as a name - sounds suitably cryptic. Enigmatic. Almost logical after VBA, ADO & SQL, eh?

Any idea about NET? Would that fit my 'rough road map' above?

(I am not familiar with XML or NET they are just names to me at this stage.)

regards, F


----------



## Joe4 (Jul 23, 2008)

Yeah, I was going to mention .NET also.  That seems to be a new "buzz" word too.  I just don't know much about it.

I think XML is becoming popular in creating Web pages, but can also be used for large mail merge type jobs (among other things, I am sure).  We basically created a template for our printers, then every month we send them a large data file in XML format that they merge with the template and print and mail.

Here is wikipedia's write-up on XML:
http://en.wikipedia.org/wiki/XML


----------



## Fazza (Jul 23, 2008)

Thanks, again. Joe. Wikipedia was a good idea, too.

XML doesn't, from a half minute of reading (!), look likely for me.

http://en.wikipedia.org/wiki/.NET_Framework looks like not what I want? Though maybe it is???


----------



## Joe4 (Jul 23, 2008)

Of the things I mentioned, I see the most Scripting as having the most immediate benefit to me.  It really helps in automating a lot of processes (and not just things like Microsoft Apps, but also FTP transfers, etc).


----------



## Noel Holland (Jul 23, 2008)

Have you thought of Visio and especially the merging of Visio and Excel data sets to form calculative graphics.

Visio is overshadowed by powerpoint and is often disregarded as just a drawing package. But that misses the essential difference between Visio and other graphics packages because at it's heart Visio is not a graphics package but a database package. Merge those capabilities with either Access or Excel and you can build very instinctive applications.

E.g.

Visio merged with personel database to form interogatable Organisation Charts.
Project Organisation Charts then used to graphically collate timeline and location data. The whole plan then exported to Excel to form a project management cost estimate. Advatange was that the project management team could play around with the organisation charts to their hearts content then within 30 mins we could have the whole set of charts re-costed and have a list of allocation discontinuities ready for handing back to the resource managers for resolution where individuals had clashes or were unallocated. In addition the report had to give detailed ratios of the sources of personnel to ensure we stayed on the right side of the contract conditions and local employment laws, plus the contract was a joint venture so we also had to track which of the contributing companies were providing each resource. Those last two options were aided by putting a little country flag in the top right corner of each persons org chart box and a little company logo in the top left corner.

Working together Excel and Visio allows you to reduce highly complex data into a much more manageable graphical form and using that form, permit manipulation of that data while still retaining the data linkages.


----------



## Richard Schollar (Jul 23, 2008)

Hello Noel

That's a very interesting suggestion.  For my part (and having had only limited exposure to Visio) I would not have a clue where to start in constructing an interogatebale Organization chart - any pointers (eg links) that you can give us for this?


----------



## Fazza (Jul 23, 2008)

Thanks, Noel. That does sound interesting. And, I have not thought of using Visio.

Your example sounds sophisticated and useful in the environment I work - engineering offices. We have well over 1000 people working just in the 'big city' office on design & project management. This one is a JV too.

Appreciate your suggestion and good explanation. It has some appeal also as I sometimes do tasks like this and I value graphical representation of data.

Is this stuff you've worked closely on? F


----------



## Fazza (Jul 23, 2008)

Also, Noel, can you suggest/recommend any particular web sites with examples?


----------



## Fazza (Jul 23, 2008)

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 (Jul 23, 2008)

Fazza said:


> 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 (Jul 23, 2008)

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


----------



## dk (Jul 25, 2008)

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 (Jul 25, 2008)

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 (Jul 26, 2008)

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 (Jul 27, 2008)

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 (Jul 27, 2008)

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 (Jul 27, 2008)

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 (Jul 28, 2008)

> 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 (Jul 28, 2008)

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...


----------



## Fazza (Jul 23, 2008)

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


----------



## DonkeyOte (Jul 28, 2008)

Can't think who you mean Fazza...


----------



## Fazza (Jul 28, 2008)

Definitely no one in particular, Luke.

Maybe it is the way I post - it is difficult in simple posts to really explain a lot and after plenty of posts that aren't of interest to OPs I don't take so much time to explain - and also that the approach is new to many people. And, it is not simple. Many people would rather not use a new approach that, for sure, appears as a bit complex.


----------



## cornflakegirl (Jul 28, 2008)

Fazza - please build a website - I'd love to learn more from you!


----------



## Fazza (Jul 28, 2008)

Thanks, Emma. Congratulations on the recent 1000 post milestone. 

Now how difficult is a website? I recall a few years ago reading that school children in the earliest years of school were being taught to set them up. So it must be very easy? I really am a Luddite on such matters. Just narrowly focusing on Excel and my few other interests...


----------



## cornflakegirl (Jul 28, 2008)

Websites are very easy - you don't even have to be able to spell or write in sentences to create them


----------



## Fazza (Jul 28, 2008)

Very easy, then!  Thanks


----------



## DonkeyOte (Jul 28, 2008)

Fazza, have I/we convinced you ???

Excellent....

Building static websites are very easy, building dynamic websites (ie with db) are not as easy but are still pretty straightforward.

My own approach:

-- sheet of A3 paper
-- compose a wish list of all the things I'd like the user to be able to do on my site 
-- sketch out requisite "page-schema" ("homepage" in the middle)
-- design db structure given above
-- start building with homepage first!

If you do opt for ASP.NET:

-- check out the 4GuysFromRolla.com site (they have a good intro section)

-- you will need to decide whether you will host the site yourself (from your home PC) or get someone to host it for you... if you do the latter you will of course be restricted to coding in whichever code they support... some hosts may only support .NET framework 1.1 whereas 2.0 Framework is now pretty standard (3.0 etc are essentially enhancements to 2.0 as opposed to new standards unlike 2.0 to 1.1 which was a big leap forward).

-- for testing purposes you will obviously need to install the appropriate .NET framework on your home PC and IIS... you may not have IIS on your home machine pending your OS which may cause problems... best to check first off.

-- if you use MySQL as db again check with host if you go that route to find which version they support -- 4.0 is different to 4.1 and 5.0 is different to both (ie 4.0 no subqueries, 4.1 subqueries but no stored procedures), 5.0 and 4.1 handle dates differently to 4.0 I think) ... each version will warrant slightly different SQL.  Most webhosts in the UK tend to offer MS Access, SQL Server and MySQL ... SQLSvr normally the most expensive ... Access & MySQL normally very cheap.

I guess obvious point being not to spend hours coding in test environment if live environment will be running different versions.

Jeez, I need to get a life... another long and largely dull post!!!

Sorry all... Emma wasn't aware you were also a web nerd ?


----------



## Fazza (Jul 28, 2008)

Not convinced yet ... and not looking to be convinced either. Just interested in learning of some of the options and if they interest me. Your enthusiasm, Luke, tells me this is of considerable interest to you. Thank you. F


----------



## cornflakegirl (Jul 28, 2008)

lasw10 said:


> Emma wasn't aware you were also a web nerd ?


 
Indeed! I have built two or three websites, to the approximate standard of a 7 year old


----------



## cornflakegirl (Jul 28, 2008)

Fazza said:


> Your enthusiasm, Luke, tells me this is of considerable interest to you.


----------



## Fazza (Jul 23, 2008)

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


----------



## Fazza (Jul 28, 2008)

cornflakegirl said:


> Indeed! I have built two or three websites, to the approximate standard of a 7 year old


Well done, Emma. Though that is only two or three more than me.


----------



## Fazza (Jul 28, 2008)

cornflakegirl said:


> Websites are very easy - you don't even have to be able to spell or write in sentences to create them


 
You mean, even Aussies can make websites?


----------



## DonkeyOte (Jul 28, 2008)

Bonza websites.


----------



## Fazza (Jul 28, 2008)

Enough for tonight. It is cold and late (here). Thanks for the comments, Luke & Emma. Ciao, F


----------



## RoryA (Jul 30, 2008)

Just to chip in: if you like, and plan to continue developing in, Excel and VBA then I would say learning VB.Net was a good idea, especially in conjunction with VSTO. Some of the stuff you can do is really cool (using Windows Forms controls in workbooks, more stable add-ins, custom task panes, NamedRange controls and data caches etc.) and I think the two are going to become more and more linked as versions of Office progress. And on the Office front, I think at least picking up a few bits and pieces on XML wouldn't do any harm (though I haven't really started that myself yet!)


----------



## Fazza (Jul 30, 2008)

Thanks, Rory.

And others, too. There have been some helpful suggestions. Thank you. F


----------



## arthurbr (Jul 30, 2008)

Bonsoir Rorya,
d'où tiens-tu ces deux vers?


----------



## RoryA (Jul 30, 2008)

Du livre "Mots d'heures, gousses, rames." Peu importe, l'importe! 
Tu connais?


----------



## arthurbr (Jul 31, 2008)

Non, pas du tout.
C'est surtout le dernier verbe qui me tarabuste


----------



## cornflakegirl (Jul 31, 2008)

Rory, you're enjoying all this far too much!


----------



## Fazza (Jul 23, 2008)

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


----------



## RoryA (Jul 31, 2008)

Il faut entendre les vers plutot que les comprendre...


----------



## RoryA (Jul 31, 2008)

cornflakegirl said:


> Rory, you're enjoying all this far too much!


Moi??


----------

