Help with VBA 3

[normm]

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?

 

[jmw]

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

MintJulip says:

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

http://www.ViscoAnalyser.com

 

[electricpete]

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)' ?

 

[MintJulep]

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

 

[electricpete]

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)' ?

 

[IDS]

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/

 

[jmw]

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

IRStuff

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:

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

http://www.ViscoAnalyser.com

 

[MintJulep]

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.

 

[MintJulep]

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.

 

[electricpete]

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)' ?