How does one REALLY make it in VBA?

[jujutsu101]

Part time, over the years I have picked up here and there some VBA. I really want to feel at least that I can make some decent small business database programs. I am MOUS Access 2000 specialist (core exam.

I have a lot of Access VBA books. Usually I get about 1/3rd through them and it feels like the writer looses me.

I wonder how have others REALLY achieved a decent (not advanced) level of VBA Access programming?

What REALLY is important to know and what is not?

I appreciate anyone who can shed some light for me on this area.

Much thanks,

Fred

 

[phishman00]

Jujutsu,

I have the same problem as you when it comes to reading tech books on any language. They start very slow and ramp up to super speed and loose me somewhere in between.

The best advice I can give you is to develop an Access app that fits a small personal or business need. That way you have an eventual goal in mind that you begin working toward. My first program was a customized expense program. Although there are a myriad of products out there that can do it for you, I wanted some customization to fit my living situation (4 roomates and no one can remember who payed the bills last month).

I knew what I wanted and started bit by bit. As I ran into problems, I bounced between this forum searching by keyword for what I knew had to be common questions and the MSDN site (I also hit the F1 Help key quite a bit in the early days).

The point is that when you have an eventual goal in mind (IE you want a certain message to appear after a certain event), you learn as you solve the problem rather than memorizing a bunch of facts about VBA without a practical application.

I would also help my friends by creating databases to automate the things they were doing manually or repeatedly (IE excel workbooks with 20+ worksheets because they didn't know how to store the information in tables and query off of them).

I'm sure everyone learns differently, but every programming language I took in college was worthless ("hello world" is not a very practical program) until I began supporting existing applications in my career and had to understand the "why" of the code as well as the "how".

If you want to code small business apps, find someone you know who has a business need but is not in a time crunch situation, and take on the task pro-bono.

Good luck.

 

[danvlas]

"What REALLY is important to know and what is not?"

REALLY important to know is the solution to the problem.

Everything else is in between and becomes important when the time is right.

Once you know the basics (collections, objects, methods, properties, functions, subs), the rest of it comes naturally while you work on an actual application.
But note that 'naturally' does not mean 'easy'.

I agree with phishman00 until the "hello world" part. I found that extremely helpful, but I had changed the message to the more encouraging "Hey dude, you made it!"




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TonyJollans]

Hi Fred,

What's REALLY important to know is how to find out the solution to your problem of the moment. You cannot hope to know it all, it's not even worth trying.

Everybody is different and learns differently. What is best for me might not work for you. For me, once I've learnt the basics of any particular language or environment I bring my general IT knowledge to the situation and look up what I need when I need it (it's a bit like life really). I got to be competent by spending decades in computers not by working with Access or VBA.

One of the nice things about modern day computing is that you can get instant feedback. It is SO EASY to try things out to see what happens. If you have questions you can search all over the world and ask in places like this.

So, to answer your question, probably just learn a bit each day - from books if you want, but I don't think that's the best way myself. And, yes, do have a purpose in mind – try solving other people’s problems here if you haven’t something specific to do yourself, I learn a lot that way. Lastly, enjoy it – if you don’t you’ll probably never make it.

As for "Hello World" and the like – I don’t want to stir up controversy but it's one of the things that annoys me about books and general teaching methods - how to take up a lot of space and time telling you how really easy it is when, of course, we all know it isn't.

Enjoy,
Tony

 

[Norris68]

One of the best ways of learning VBA is the help files (and maybe a copy of MSDN library if you can get hold of one - but it's all on the web anyway, see msdn.microsoft.com). Click on the See Also links - they will usually list related functions, code examples, etc. Play. Explore. If you write a routine, time it, then write it again - but faster, better & more efficient. (Obviously depends how much spare time you have)

Do that for several years and you'll be a VBA expert!

 

[jrbarnett]

Hi Fred,

I agree with TonyJollans. I started out with Access doing practical work for a project on the 2nd year of my degree, then used it a little during my placement year for a computer equipment inventory system (3rd year). In the 4th year I used it for two separate projects.
By this point, my Access experience was just v2. I never used v1.x.

After graduation I then got a job as a programmer, not using Access, but the company was given a contract to develop a system in Access 97, which I was asked to do.
I did everything myself, including testing, documenting and bug fixing. It was a huge learning curve over v2.

I still recall the elation I got when I figured out how to create my own class modules, and DAO code to produce a generic search window for bound forms, which were easy in Powerbuilder (that other software was developed in), and my documenter addin that writes out pure hand coded HTML code.

That is how to learn: By doing real work in it to solve problems that you know about. What I found was useful was to write down in simple terms how to do something. In some of these cases I have turned these into Tek-Tips FAQ's, but others (such as how to create class modules) are far too complex for that.

John

 

[jujutsu101]

Thank you all for your views. It certainly helps. I don't feel "by myself in the huge ocean of VBA" as much now - which is sort of calming.

For some reason I really want to (as my goal for now) be a good small business programmer. I want to do the basic Small Carpet Cleaning Programming/Small Business Mail Order Programming/ etc.

Am I correct that some programmers favor more of using classes of their own; while other programmers heavily use SQL; while others favor more DAO/ADO? I think what is confusing a bit is there seems to be more than one approach to the same problems in programming.

MAIN QUESTION
If I were learning under you folks and YOU were teaching me to do small business type programming - what would you (and what wouldn't you)have me learn VERY well?

In ending I thank you ALL for your guidance! I should have asked this question to this board a long time ago!

Respectfully,

Fred

 

[uscitizen]

Are online courses a waste of time? I've thought about looking into that medium. I find picking up a book and wading through the examples (which might be on an accompanying CD stuck on the inside of the back jacket cover) a feat of eye hand co-ordination I'd like to try to do without.

 

[vbajock]

Personally, I think the best approach is to take a couple of college courses, Intro to VB, next Object Oriented Programming, finally Intro to Database Programming. There is also a user-interface oriented Access intro offered at most colleges. I did this when it was VB4 and Access 95. I am fixing to go back an do it again for VB.Net, XML, and Office XP. I think an in-your-face sit down in the classroom course is better than an online course, because it forces you to do coding that you really would rather not do, and the prof is there to hand-hold during those times when the text book gets to be too murky.

 

[mssngr]

Hi jujutsu101,
I am really a beginner in VBA but in general I think that what to learn is what best suits the problem at hand.
I am working ono a single app (for a project in my wrokplace) which I am evolving as a training exercise for myself.
I have started by 'just getting it working'. The form design is bad and the code is too specific and lacks alot in places. Many versions on I have refined the form, now much more user friendly and functional, but find I have done many things the hard way. I am seeing great value in using classes. I think if had started this way I might have saved myself a bit of grief.

cheers,
mssngr.

 

[VBslammer]

Everyone should really start out with C++ and once they get comfortable with that the books won't lose them anymore

VBSlammer

Unemployed in Houston, Texas

 

[PassingBy]

As with many others I am self taught but have also been on various courses 'on the way', some books are OK some are not, life experience counts for a lot.

Most of my stuff has been replacing/automating paper systems, this makes it easier because you have a model to work from.

The 'REALLY' most useful functions I have found within A97 are:
Partition - for banding values
Environ ("Username&quot - for idetifying who did what in multiuser db

The most 'REALLY' common constructs I use are
If then else Endif
Docmd

The REALLY most attempted (and failed 100%) aspect that I have tried is setting up the MS db User Security, especially with multiple frontend/backend apps using the System mdw file.

I believe I am about half proficient at what I am doing with A97 (after 5 years) I think the test of proficiency must be if you could earn a living as a freelance programmer rather than as a retained employee. I know I could not at this stage but then this is only 40% of my overall job requirement.

PassingBy

 

[BobJacksonNCI]

Should I change my name? 2centsworth sounds right!

I believe the first thing anyone should do is assess their logic capability. If a person is weak there, programming will likely be a frustrating experience.

After that, good characteristics to have include attention to detail and good basic math skills. I've seen programmers get along without either/both, but their work is usually average and they won't excel in technical positions. Of course it someone with great abstract skills starts as a programmer, he could well turn out to be an excellent technical manager, if he can get that promotion.
(No offense intended to female readers - "he" - as some of
the BEST I've worked with are women!)

Why would you listen to me? (Outstanding question!)
Experience. I worked with mainframes for 25 years, and got downsized/outsourced/retired/poor.

The frustrating thing for me is knowing that tasks CAN be done but fighting with syntax/properties/events until finding the winning combination. I've said this before and it may NOT be the best for everyone, but I think the best way to grasp the "Access way" for newcomers is to pick the "create new database" wizard that most closely fits your needs and build on it. That allows you to "make something happen" more quickly that applies to your project.

Regarding my frustration over the myriad ways to sort of do the same thing, let me give an example: My current project (ADP) involves data entry with validation. I think the best way in plain Access is to put absolutes in the table. If a date cannot ever be greater than today when entered, put that absolute validation in the table. Then, if being equal to today is unusual, add a before update event to the form control bound to that field and use msgbox to get confirmation from the user. Do that in an Access Project? Ha! The table is on an SQL server and in the version of Access I'm using (2000) the SQL error message doesn't make it through the layers to Access. O.K., just put the absolute in the form. Fine - except you can't put a title on the window that opens to report the error. So, now I've got it all in VBA. Since I began programming with assembler (a few years ago), it feels like I wasted a great deal of time messing with built-in automation just to end up the way I would've done it decades ago.

Hmmm, one needs to have the ability to deal with frustration...

Having said all of that (vent switch off), you CAN create great and wonderful databases using Microsoft's tools. Try to avoid perfection as there are always several ways to do things. But DO try to make it bullet-proof, because your users will appreciate it more if it doesn't break. And document to the best of your ability, because you want the new guy/gal to handle maintenance so you're not interrupted when you're busy feeling melancholy over the Chicago Cubs.


HTH,
Bob
Your mileage may vary, but following the guidelines in faq181-2886 will help you reach your goal.