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
You might check over at Tek-Tips.com - a sister site where the computer geeks live.
RE: Help with VBA
Very good stuff there.
=====================================
(2B)+(2B)' ?
RE: Help with VBA
http://newtonexcelbach.wordpress.com/
RE: Help with VBA
http://newtonexcelbach.wordpress.com
RE: Help with VBA
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
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
Chinese prisoner wins Nobel Peace Prize
RE: Help with VBA
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
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
RE: Help with VBA
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
=====================================
(2B)+(2B)' ?
RE: Help with VBA
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://ne
and the following two posts
http:/
and the following posts
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Help with VBA
http://n
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
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
Here's a book you are looking for:
http:/
Judging by reviews it's not much worth though.
Yakpol
RE: Help with VBA
Regards,
![[pipe] pipe](https://www.tipmaster.com/images/pipe.gif)
Qshake
Eng-Tips Forums:Real Solutions for Real Problems Really Quick.
RE: Help with VBA
"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
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
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
I just posted "Learning Visual Basic" before I read this thread.
MintJulip says:
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
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 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
=====================================
(2B)+(2B)' ?
RE: Help with VBA
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
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:
(Doug and echoed by Electric Pete).
JMW
www.ViscoAnalyser.com
RE: Help with VBA
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
RE: Help with VBA
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)' ?