Recommended Excel References?

[bootlegend]

Everything I've learned in Excel has been on my own. I know enough to accomplish most of my own tasks, but I wonder what else I could accomplish if I had a better knowledge of Excel. Do you have any recommended references (online or physical) that would provide a thorough course for self learning. Most things I find are geared toward business and charts/graphs/presentations more than engineering. Pivot Tables are cool, but I don't think I'd need them often as a structural engineer. I imagine learning VBA as it applies to excel would be necessary too. I welcome any suggestions.

 

[Agent666]

Definitely learning VBA is a no-brainer if you don't already currently know it and want to lift your game. I taught myself by simply jumping in with a lot of trial and error involved. For VBA there are plenty of online forums that have code suggestions to do something similar to a given problem you might have that you can learn and tweak to your own needs. Simply search for what you want to achieve with 'VBA' in the search and you'll no doubt have many suggestions where people have already solved similar issues.

What do you want to do that you currently cannot do or are being held back from based on your current knowledge?

For me having a goal in terms of creation of a standardised spreadsheet tool/template to help me become more efficient was the goal to tipping me over the edge from an occasional dabbler in VBA to me learning VBA in a more through and lasting way. So I'd suggest you probably have to have a driving force or end result in mind, rather than a desire to simply learn it for the sake of learning, come up with something you require that can only be done with the help of VBA, etc. Use this task to teach yourself in the process.

I created an add-in tool that stores the inputs and outputs from a workbook within the worksheet, with the ability to recall any of the design cases as required. It was all run by a custom ribbon (learning XML as well in the process) in excel, and eventually went company wide. As opposed to having 15 copies of a single spreadsheet with different inputs, having one workbook with all 15 cases being able to be recalled into a single common sheet within a few seconds saved a lot of time for a lot of people. Tools in the ribbon aided in achieving a standardised formatting for inputs/outputs, and greatly increased the speed of development of new calculation templates based on this template and creation of a standardised environment for development.

I've never found books that useful, in the sense that I own a few but never really read that much of them as I could always find what I wanted online in forums and blog posts.

In terms of VBA for engineers/by engineers and in general check out these blogs:-
wellsr.com (check the tutorials)
newtonexcelbach.com (By member IDS on these forums)

For charting & some general VBA I've always found peltiertech.com to be a valuable resource as well.

Interfacing python with excel seems to be gaining popularity so that might also be a worthwhile to learn, I don't have any experience with this (yet).

 

[IRstuff]

You can learn a huge amount about the workings of VBA by simply using the Macro Recorder function that exists within Excel. Go to the developer tab; turn on the Macro Recorder and do a bunch of stuff; open the recorder macro and start figuring out the syntax. I find that to be the most useful, since you'll be recording operations that you already use and it'll be completely relevant to whatever you normally do.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[FreddyNurk]

The best VBA for Excel reference I ever found was the 'Professional Excel Development' book. However, it is somewhat outdated as there have been changes to the object model and inner workings of Excel since its release.

What it does outline well though, is the consideration of separation of data from the user interfaces and the code, which can be quite important when there are quite a few copies of spreadsheets with erroneous functions in use. Other similar issues can be things like hard coding the cell references, and having other users who aren't aware of the specific cell requirements enter data or attempt to use the code on incorrect cells.

Having said that, it depends on what you want to achieve with VBA. Developing your own code routines for your own use is one thing, but deploying spreadsheets for others to use can be problematic for quite a number of reasons (least of all the security warnings and the fact that macro enabled files now have a different extension).



EDMS Australia

 

[SkipVought]

Check out John Walkenbach’s books on Excel & VBA. I especially used his book, Excel Charts. It’s got lots of VBA too.

Become a member at

http://www.Tek-Tips.com,

Eng-Tips sister site (see link above-left). There are two forums: the former focues on application features, the latter on VBA.
Microsoft Office
VBA Visual Basic for Applications (Microsoft)

I am a member there, too, and post tips regularly.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JoelTXCive]

I have several books on Excel VBA, but honestly find the best results with youtube and help forums such as the ones mentioned above.

I have also purchased online courses from a company called Udemy (

https://www.udemy.com/

). They have a ton of online excel VBA courses. Some are good and some are not that great.....but they only cost ~$20 bucks so the downside to purchasing a poor online course is pretty low.

 

[MintJulep]

This web site by Eng-Tipster IDS is a good source.

 

[bootlegend]

Thanks for all the great feedback. I have plenty to keep me busy for a good while.