×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help with VBA
3

Help with VBA

Help with VBA

(OP)
I have been using Excel for over 8 years and consider myself proficient in using mathematical functions, formulas, graphs,Vlookup etc. But now I am trying to grow more into User defined funtions, Macro, VBA etc. I am looking for some refernces or books where these are discussed in Civil / Structural engineering context. I have seen such books in Accountancy / marketing background. But I am looking specifically Civil / Structural engineering examples. Can anyone help please?

RE: Help with VBA

The theory is still the same - you are just playing with numbers.

You might check over at Tek-Tips.com - a sister site where the computer geeks live.

RE: Help with VBA

Find the website for Doug (IDS).
Very good stuff there.

=====================================
(2B)+(2B)'  ?

RE: Help with VBA

Thanks for the mentions everyone.

Normm - I don't know of any books specifically related to civil/structural applications, although there are several for general science/engineering. If you find anything good please let us know.

If you have any questions or suggestions for the blog, please leave a comment.

 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Help with VBA

(OP)
Thanks everyone for your valuable comments and suggestions.

My present intention is not just to download some Excel VBA spreadsheets from a source and merely use it, but to build up some experience myself so that I can apply the skill to do my own spreadsheets to suit any situation I come across. From that point, the references that electricpete and avscorreia gave does not help me a grat deal at present. I also found the language used in presentation in that website is not very straightforward but somewhat (unnecessarily) arcane. But it could be just me, as I have to climb a few steps first.

IDS, the reason for me looking for Civil / Structural examples is that I can relate to the problems more quickly. Although I appreciate the methods must be similar in other disciplines. So if you could mention specifically which references in science and engineering, it woill be a great help.

Thanks again, all of you. I am still looking.

RE: Help with VBA

I doubt that you'll find VBA books tailored towards structural or civil engineering problems.  The addressable market is simply too small to make it worthwhile to publish.

You might find some starting points at http://yakpol.net/index.html.

The best way to learn is to do.  Identify a need and set off to fill it.  Ask questions here or at tek-tips.com.

RE: Help with VBA

Learn the fundamentals from a generic source as mentioned above, then get into recording macros and examine the code.  You can learn a ton of good stuff and speed up programming by orders of magnitude.

RE: Help with VBA

The bottom line is that you should either be able to use an existing spreadsheet, and simply automate, or, you'll find that VBA is not fast enough and you need to write a C-coded math module to do a specific calculation.

Just doing VBA for the sake of VBA is not going to benefit you or any user.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize

RE: Help with VBA

To each his own.  vba fills a much wider role in my arsonal than suggested above.

=====================================
(2B)+(2B)'  ?

RE: Help with VBA

Quote:

The bottom line is that you should either be able to use an existing spreadsheet, and simply automate, or, you'll find that VBA is not fast enough and you need to write a C-coded math module to do a specific calculation.

That's just wrong.

VBA is plenty fast enough for 99% of what you would want to do on a spreadsheet, and it is useful for a great range of problems.  Building your own spreadsheets from scratch is also a great way to learn/refresh the basics.  I really don't know why people are so negative about it.

normm - I have Excel for Scientists and Engineers by E. Joseph Billo, which is pretty good.  It focusses on numerical methods and examples are science related, but there is plenty in there which is useful in any field of engineering.

I'd appreciate it if you could give some more specifics about things on the blog that were hard to follow.  I'd like to make it as useful as possible, so I'm happy to have constructive criticism.  Some series of posts that you might find useful are:

http://newtonexcelbach.wordpress.com/2008/02/21/hello-world-in-a-udf/
and the following two posts

http://newtonexcelbach.wordpress.com/2008/03/04/ranges-and-arrays/
and the following posts

  

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Help with VBA

... Continued, I pressed submit acidentally:

http://newtonexcelbach.wordpress.com/2009/01/31/frame-analysis-with-excel-1-single-beam/
and the linked posts.

Posts forming a series should be linked through hyperlinks either in the text or in the comments.  If you can't find a link try searching on part of the post title in the blog search box.

I would be grateful for any feedback.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Help with VBA

(OP)
Thanks IDS.
I will spend a little longer time on this site before making comments. It looks there could be some very useful stuff there.

RE: Help with VBA

yakpol - I have that book and am glad I didn't pay 67 dollars for it.  It's not worth much truthfully.

Regards,
Qshake
pipe
Eng-Tips Forums:Real Solutions for Real Problems Really Quick.
 

RE: Help with VBA

I just happened to come across something I wrote a couple of years ago, which is relevent here:

Quote:

The basics are covered here:
 http://newtonexcelbach.wordpress.com/2008/02/21/hello-world-in-a-udf/
 and following posts cover a lot of the refinements. If you search this blog for UDF and/or VBA you should find a lot of useful information.
 
Books I can recommend are Excel Power Programming by John Walkenbach and Excel for Engineers and Scientists by Joseph Bilo.
 
Both are very readable, and don't assume a huge amount of programming experience.
 
If I had to pick one web site for reference it would be Chip Pearson's site:
 http://www.cpearson.com/excel/mainpage.aspx.
 
In summary I'd say the best way to start was:
 
Pick up the basics of how to get data from the spreadsheet into VBA and back again.
 
Learn the basics of Basic; data types, control statements, and arrays etc.
 
Start experimenting with some UDFs and recorded macros.
 
For general questions you'll get a better response at one of the discussion groups, but for any questions on anything posted here, feel free to ask here.

"Here" in the quote means my blog, but it equally applies to here Eng-Tips.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Help with VBA

excel 7.0 permitted calls to external dll's that was nifty!

to accomplish that same trick in modern versions, you must call the dll from a visual basic module. That step makes it available for the spread sheet use.

 

RE: Help with VBA

I'm sorry, I haven't read the entire thread so if someone else has made my suggestion, kudos.

If you're on Google or Yahoo, you can get into the Excel with Macros / VBA group pretty easily. I've been reading their daily digest emails for a while now, very informative and helpful. To find them, just do a standard search on their groups pages and type in something resembling "Excel Maros VBA."

 

RE: Help with VBA

Oops!
I just posted "Learning Visual Basic" before I read this thread.

MintJulip says:

Quote:

The best way to learn is to do.  Identify a need and set off to fill it.  Ask questions here or at tek-tips.com.

That is absolutely right - for if you only have an occasional need.
Back in the days of Windows 3.1 I was told to start using Excel.
Over the years I have been attempting ever  more ambitious tasks confident that for any particular need I would be able to find a solution via Google or Eng-tips (Tek-Tips.... I can't seem to log in anymore) and I have learned how to make IF statements do lots of things.
The problem is, I probably use the IF statement where much quicker and more elegant methods are available.
And, of course, as soon as you exhibit any kind of ability people want you to do more and more.

I think there comes a point where "monkey see Monkey do" has a limit and I echo Normms ambition to actually be self sufficient, to know and understand a great deal more than I do.
I don't want to have to download examples and good as Google can be, it is frustrating to have to search through some of those "help forum threads" where sometimes it is the blind leading te blind.

As an example (form my thread on outputting the result of a calculation to an open cell, I wasted much of my time and the valuable time of members here because the original statement I borrowed was faulty.

It took a while to discover that "worksheet_calculation" was the wrong solution (it probably worked as it was originally written) and it should have been "Worksheet_SelectionChange(ByVal Target As Range)".

It is good to know there are safety nets here and on some other websites, but if it was me giving the advice, I soon tire of having to explain some very basic things. Hence I too nee some good autodidactic aids.
 
 

JMW
www.ViscoAnalyser.com

 

RE: Help with VBA

I have some comments – possibly repetitive.

As others said, the best way to learn is by doing.   Try tackling some problems: first small, then bigger.  Recording macro for doing task in excel and then inspecting code can help as well.

If you have a book as a reference, that can be a security blanket.  A few years back when I learned, the books I tended to lean on were:

Excel for Scientists and Engineers / Numerical Methods by Billo (NOT the other Excel for Engineers book!) –  Lots of math examples solving problems we all face (interpolation, etc, etc). Actually does a pretty good job coveing excel capabilities as well.

Excel 2002 Power Programming with VBA by J Walkenbach – good overview reference, almost like and encyclopedia.

I'm sure there are a lot of other good ones people can recommend, and certainly some more current ones.

If you are really interested in learning, I think it is important early on to get very familiar with the tools available in the interactive dispay editor.  They will help you learn the language and work through the tasks you're trying to figure out how to do.  Some examples:
1 – immediate window – try out a command to see what it does.  Good debugging tool. Put a stop in your code and then examine variables and try things out when you get there.
2 – local variables window.  Good way to inspect your variables, their values, and their types.
3 – Make sure you have your options set up to provide list of values.  So for examply when you type "myrange."  after typing the period it tells you what kind of methods and properties are available.
4 – Object browser (I actually don't use this so much)


The reason I really decided to type a response was to urge you NOT to overlook tek-tips.
1 – Their FAQ's are fairly extensive.
2 – Just read through the day to day problems that people try solve and you will probably see some problems you want to solve.
3 – **** It is INTERACTIVE.   If you have a question you can ask. You can't always get that from google or your books.
 

=====================================
(2B)+(2B)'  ?

RE: Help with VBA

The problem with most "Teach yourself" type books is that they will systematically lead you through a process of developing a simple application that has absolutely no relevance to the things you need or want to do.

The alternate book approach is the comprehensive reference - that assumes you know how to program, and just need to know the language and rules of a particular tool.

There seems to be very little in the middle.

There is a wealth of information available on Tek-Tips in the VBA forum.  Take a look through the FAQs there.

If you are above a "certain age" then you probably learned programming in the days of Fortran, basic Basic, Pascal, etc.  Back in the day programs were written as a single chunk of code, with prodigious use of "flow control" statements like IF...THEN...ELSE, GOTO, BRANCH, etc.

You need to break out of that mindset.  Break your code down into the smallest bits you can and set that into a Procedure or Function.  Input ---> Output ----> Input ....

   

RE: Help with VBA

I forgot to mention. Use option explicit  in every module.  It will force you to be more concious of the variable data types.

=====================================
(2B)+(2B)'  ?

RE: Help with VBA

electricpete has already said most of what I was going to say.  I have the two books he suggested (Billo and Walkenbach) and would also recommend them.

My only other suggestion is that it often works better to set up the spreadsheet to suite the spreadsheet way of working, rather than trying to force it to work your way.  For instance the problem in the other thread was eventually solved with some not very obvious changes to the code, but it would have been easier to set it up so that the data entry cells and the cells the VBA writes to were separate.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Help with VBA

Quote:

The best way to learn is to do.  Identify a need and set off to fill it.
IRStuff

Quote:

Try tackling some problems: first small, then bigger.
Great advice guys and great help for my problems when I post.

The only fly in the ointment is a tendency to stick with what you know.
I started out a long while back using IF statements to do what I needed.

But now I am the IF king.
No matter what the problem, I break my calculations down into blocks and then whatever I need to do, I write it using IF statements where possible.
I can nest them the legal 7 deep and I have routines to extend that to any reasonable number of IF statements.

But I suspect that some problems are better solved using a different approach.

I found the proof of this just today.
A couple of months back I needed one of my old spreadsheets, written a couple of years back, but now lost in the hard drive of a dead computer.

I decided to rewrite it and so I did, mainly using IF statements.

Though it worked I nearly went mad writing it; not just because it was nightmare to write, but because as I was writing it I couldn't help remembering it went much quicker, simpler and better the first time. But how?

Today I found a copy buried in this laptop.
I opened it up and discovered alien code, all neatly organised and written as if it had all gone in one flowing sequence.

So evidently, the IF statement isn't the only way to do things.

Now I must devote some time to figuring out what the heck I did(Probably with help from here).

So I guess I now have to move on to the next good advice here:

Quote:

Pick up the basics of how to get data from the spreadsheet into VBA and back again.
 
Learn the basics of Basic; data types, control statements, and arrays etc.
 
Start experimenting with some UDFs and recorded macros.
(Doug and echoed by Electric Pete).


 

JMW
www.ViscoAnalyser.com

 

RE: Help with VBA

If your if statements are of the form of

if OneThing = 1 then AnotherThing = "A"
else
  if OneThing = 2 then AnotherThing = "B"
   else
     if OneThing = 3 then AnotherThing = "C"

etc.

Then

think about using a datatable and a lookup.

 

RE: Help with VBA

Post some of your many level if statements, and what you are trying to accomplish with them.  Maybe the collective can offer some alternate methods to accomplish whatever.

RE: Help with VBA

Quote:

Try tackling some problems: first small, then bigger.
....The only fly in the ointment is a tendency to stick with what you know...

..But I suspect that some problems are better solved using a different approach.

There is some logic to your comments.  I  ended up using a combination of reading and doing. The whole discussion leads me to one more book to discuss that I leaned on heavily when I was learning:

"MS Excel VBA Programming for the Absolute Beginner" by Birnbaum.

First thing to mention, the title does not in the least describe the book... I don't think anyone  could pick up the book by itself (without other books and references) and learn VBA.    

The interesting/useful part of the book imo is that he alternates between showing you the code to solve a problem, and then assigning you to solve  a similar (perhaps slightly harder) problem.   And all of the problems involve creating various types of computer games... which had a special interest for me because I had young kids at the time and that was a chance to engage them in what I was doing.    At the time I remember I was very frustrated with that book, but I ended up trudging through a few of those assignments (with the reward that my kids could play the game each time I finished one) and there is no doubt that it was a very useful excercize for me.
 

=====================================
(2B)+(2B)'  ?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close