# Beyond formulas



## arkusM (Apr 13, 2007)

Hello all,

This has been a great place to learn some things, as well a Bill's Podcasts, have got lots of great tips that I use regularly. I have developed/merged/butchered many formulas, but find them limiting. 

Therefore, I am looking to go beyond the formulas and get into some VBA. I have successfully made some UDF's and even have tried using the "record macro" function but find it is limited. 

I was hoping that some folks would be able to point me in the direction of some VBA tutorials/ basics, or even some books that can get me started down this path. I know that this site has some books, but I am not sure which one would be good to start with and go beyond.

Any help, direction, advise would be greatly appreciated. Cheers All.


----------



## Lewiy (Apr 13, 2007)

As a starting point, possibly the best tip I could give would be don't use the macro recorder to achieve tasks but rather to obtain sections of code to use elsewhere.


----------



## PaddyD (Apr 13, 2007)

as good a place as any:

http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm


----------



## RSUK63 (Apr 18, 2007)

WOW, what luck, I've only just registered, this was the first thing I read and is one of the most helpful bits of info I've had for a long time, Thanks


----------



## erik.van.geit (Apr 18, 2007)

Hello, RSUK63
Welcome to the Board !!!!!

what an enthousiasm !
ENJOY 

greetings from Belgium
Erik


----------



## arkusM (Apr 18, 2007)

PaddyD,
I have been slow to respond. But thanks for the link, I went through it yesterday and it has some good bits in it.
 VB seems a bit like Excel, a deep dark cavarnous abyss, that holds endless treasures that must be discovered slowly.

Thanks!


----------



## arkusM (Apr 18, 2007)

Lewiy,

Do you have a place that you use? The reason I posted here was that I don't yet have a specific question or problem to solve that requires VB.
In reference to my last post, I am only entering the mouth of the cave, and don't even know what, I don't know. I was trying to find somewhere to get me feet wet so to speak.

Thanks for the tip about the the recorder! Cheers.


----------



## TinaP (Apr 18, 2007)

> As a starting point, possibly the best tip I could give would be don't use the macro recorder to achieve tasks but rather to obtain sections of code to use elsewhere.



On the contrary, I think it is a good place to learn as long as you understand that recorded code is NOT the ideal way to write macros.  If you use a combination of recorded code, some good web sites and a good book (most by John Walkenbach and certainly the books written by Mr. Excel, et al), you will quickly learn the concepts and skills required in VBA.


----------



## ExcelChampion (Apr 18, 2007)

> > As a starting point, possibly the best tip I could give would be don't use the macro recorder to achieve tasks but rather to obtain sections of code to use elsewhere.
> 
> 
> 
> On the contrary, I think it is a good place to learn as long as you understand that recorded code is NOT the ideal way to write macros.  If you use a combination of recorded code, some good web sites and a good book (most by John Walkenbach and certainly the books written by Mr. Excel, et al), you will quickly learn the concepts and skills required in VBA.



My thougths exactly.

If you want to learn VBA, I would suggest turning on the macro recorder, perform some functions, and then go look at the code that was recorded.

As well, learn about variables and loops (you can't do these with the macro recorder.)

You will then be well on your way.


----------



## arkusM (Apr 18, 2007)

Shiny. More resources. I'll pick up some of the books. 
I am hoping someday that  I can trim my WB calc time down from 1+ minute!! There are tens of thousands of formulas in some of my WBs, but that is the only way I knew how to get things done. Looking forward to the learning curve.  Thanks all for the info!


----------



## Lewiy (Apr 18, 2007)

Most of my background knowledge came from doing a bit of Pascal/Delphi programming in college.  It was more the "theory" behind it that was useful, things like knowing how looping and sorting work were invaluable.  It meant that I knew the sorts of things that I should be able to do which makes it easier as a starting point.

Here are a couple of interesting Wiki pages I found which may help with some theory/pseudo code:

Looping:
http://en.wikipedia.org/wiki/Conditional_loop

Sorting:
http://en.wikipedia.org/wiki/Sorting_algorithm

The thing that always stumped me at college was reading and writing data to text files for storage.  Luckily, this is where Excel VBA comes into it's own beacuse it's as easy as assigning values to cells!

The link which PaddyD posted is actually a very useful starting point to understanding how to amend the Macro Recorder code to eliminate all the unnecessary bits which slow things down.  One of the things I did when I started learning VBA was to analyse each line of code seperately (with the aid of the help files) to understand what it was doing and why and whether there were alternative solutions/methods (believe it or not, quite often, help file examples will actually provide you with relevant code in my experience!).  I still do this now when people here provide me with code solutions rather than just using it because it means you stand a chance of being able to debug/edit the code when necessary.

I'm going on a bit now so I'll leave it at that for the time being and wish you the best of luck!


----------



## arkusM (Apr 18, 2007)

Lewiy,
Shiny, I have no programming backgroud, so gaining this type of theroy will be helpful. Thanks for the tip on working at understanding each line of code. 
Thanks for the post.


----------



## brian.wethington (Apr 20, 2007)

This is all some very good advice, I would just love to add a small tidbit.  I am also somewhat new to VBA.  I have picked it up fairly quickly and run with it.  Probably the most helpful thing to me was to gain my knowledge by doing one small task at a time via Macro Recorder.  Once I had recorded it I would look at the code.  I realized there were probably some better ways to do things very quickly.  For example, there is no need to activate/select sheets or ranges in most circumstances. I would work with that small piece of code until I felt I had that small procedure to the quickest, simplest route I could get it to be.  I would say with each line of code I would be using previous ideas and the help files.  Also, if I could not figure something out completely I would have one very small question that I could ask here. This way I would not get an extensive code from someone that I would have to question exactly what all is going on at what point.  Therefore, I knew exactly what each line of code was doing and how when I received it, then I would disect that line of code and determine if it could apply to anything I have done previously. Lastly, once you have developed a firm grasp of a particular aspect, try to explain it to someone. I can't tell you how many times I have explained something to someone and determined a simpler, more elegant approach.  That is why this site is so helpful.  I can help others with questions I once had. I can explain certain things, then the final important thing, people ask questions about certian things you don't sometimes think about and you can find a solution for it.  Problem solving is a very important part to all of this, and the more you practice that, the better you will find your VBA solutions more quickly.

This is a long explanation to a short meaning.  Use VBA Helpfiles, Macro Recorder and ask questions to get up to speed.  Then start using what you have learned as much as possible.


----------



## arkusM (May 15, 2007)

Thanks everybody for your contributions! (not sure if anyone is still "watching" this topic) but if you are thanks.

I have started down the road, I am reaching the knows-enough-to-be-dangerous stage, as least enough to begin to be able to ask how-to questions.

Thanks All!


----------



## Hatcheda (May 17, 2007)

Also, after you borrow code . . .
Open the VB editor and place breaks in the code by clicking the grey area to the left of the code. You should see a red dot. (A Break) Press F8 to continue

If you run the code using this, you can see how it works, step, by step!
Of course this works best with two monitors!  I use three at work! Rocks!


----------



## arkusM (May 17, 2007)

Three!! Wow, I have two, which is VERY nice to code stuff in. I got used to two monitors in my graphic design days and when i started in my current job it was not long before I requested two monitors. It makes pretty much everything easier. I cannot stand helping people out on their machine when they have the resolution set low (1024x768) and only one screen!! It is so easy to be spoiled.....

I have accidently turned "breaks" on but was unsure what they did. Thanks for letting me know!


----------



## Hatcheda (May 17, 2007)

Yeah, I just switched departments. We are using laptops because of travel but I have two 17's on the docking station. Everyone had their laptop closed and off to the side or under one of the monitors setting on the docking stand.  I just changed the bios and booted up with three! Laptop in middle, and monitor on each side! I will pm you tomorrow with a program you must have! Its called ultra monitor -really takes advantage of the dual monitor thing!


----------



## Hatcheda (May 17, 2007)

For the breaks to work you will need excel on screen and vb editor on the other (If you have that option)
Activate Excel - Run the macro (apply breaks first)
The macro will stop at the break
Activate the vb screen and hit F8 again and again to proceed to the next break.  -what changes with each press.  The change is the result of the previous press of f8

If you have code that wont funtion correctly, this can help alot
If you have code that is slow - use this to find what to change!

Also
Always consider adding 
Application.screenupdating = false
at the beginning
and 
Application.screenupdating = true 
at the end

This will cost you the majority of the run time

lastly, avoid 'select' -it slows you down.
Range("A1") = Range("A2")
Is much better than the copy select garbage the recorder writes!

Hope this helps


----------

