? re: VBA, writing code, etc. (Long)

[Kenright]

Hi all !!! I came across this board last week when my HR admin. needed a way to calculate age from an employee's birthdate. And you guys that helped me out, I thank you !!! I am the Reports Analyst/Officer at a small size title clearing company. I have worked with Excel and Access at my previous job (Night Auditor), but never to this degree. Formulas, equations, VB, etc. I never knew that one could accomplish so much with a "spreadsheet program". My career goal was to become MCSE, now I am leaning towards the programming and software side of it.

Anyways, I am learning a lot from the members here, so much that my head hurts everyday when I leave work; spending way too much time on this site !!! I have started to learn about macros, and writing them. I have used the "record" function of excel to create the few that I have needed. My question is this: Where does one write the codes for various functions?? Do I need the Analysis Tool-Pak or Analysis Tool-Pak-VBA?? Is that how one writes scripts and formulas to modify worksheets?? See below for example:

Code:

Sub Macro1()

Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(Now() + 1, "MM-DD-YY")

End Sub

Anyone that can point me in the right direction, I would be extremely grateful. I have found the archives from this site and am pouring over the how-to's and help files. My MIS told me that a good place to start is to learn VB.net. However I am having a hard time finding a book that covers it from a total beginner's point of view.

Thanks, and sorry for the long read.

-Ken

 

[Smitty]

The macro recorder is probably the best place to start, especially recording doing something that you do everyday. Then follow the generated code. Note that the recorder generates a ton of erroneous code that can almost always be cleaned up. I.E. getting rid of Select statements (you can always post it here for clean-up or explanation).

As for books, Bill Jelen (aka Mr. Excel) has a macro book offered through the site. I also like John Walkenbach's Power Programming with Excel VBA books.

My MIS told me that a good place to start is to learn VB.net

If you want to write your own programs sure. If you want to stick with Office, I'd recommend learning VBA. They're both "pretty" similar, but have different scopes. Knowing VBA will take you a long way toward understanding .NET if you ever do decide to go in that direction.

Hope that helps,

Smitty

 

[Zack Barresse]

Knowing VBA will take you a long way toward understanding .NET if you ever do decide to go in that direction.

I'm not even sure I can agree with that one. .NET is really it's own animal. Now if we were talking about just VB, then yeah, you could use your VBA skills and have little trouble making your way through it. This is definitely not the case with VB.NET unfortunately.

 

[Smitty]

I see your point Zack, I meant from a fundamental point of view, where a lot of the language and structure is the same. It is certainly its own animal and I didn't mean to advertise or suggest differently.

I was speaking from my experience, in which knowing the smattering of VBA I do has been a help to getting a grasp on the .NET environment, VB.NET that is. I haven't even started with the other parts of it.

Smitty

 

[Zack Barresse]

Gotcha. I think it'd be easier, for someone starting with Office VBA, to migrate to VB and then to VB.NET. It seems that the 2005 .NET is much better than the 2003 in it's stability, and even Office integration. I'm pretty sure VB is still the de facto standard for anything (involving) Office .. unless you're talking with MSFT. hehe

Sometimes I get such a headache working with VB.NET..