Beyond formulas

[Lewiy]

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]

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]

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]

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]

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]

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]

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]

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