VBA Tutorial

[jaad]

Does anyone have any insight about what would be some of the best literature on the web today on how to construct syntax in VBA from the start?

What I am looking for is "learn how to tie your shoes" basic literature. The most basic of the basic of the basic.

Most of what I am getting from anywhere is "how to use functions" and how to write them in VBA.

Here is an example:

  Option Compare Database
 
 
Sub Form_current()
Dim strParentDocName As String
 
On Error Resume Next
strParentDocName = Me.Parent.Name
 
If Err <> 0 Then
GoTo Form_current_exit
 
Else
On Error GoTo Form_Current_err
If Me.Parent.Name = "Work_Order_Details_SubForm" Then
Me.Parent![Tasks_Form].Requery
End If
End If
 
Form_current_exit:
Exit Sub
 
Form_Current_err:
MsgBox Err.Description
Resume Form_current_exit
 
End Sub 

This doesn't teach me anything. I may have a working function but I have no clue why it work or understand the reasons why I must quote " " around Work_Order_Detail_Subform

 If Me.Parent.Name = "Work_Order_Details_SubForm" 

because sometime you don't need to and other time you have to put that line in [ ] but if it is on a page then I must first put Form! but other time maybe not because of a reason or another..... For me this is like a 50 years + of experience contractor would get started at showing a 5 year old on how to build a house without showing the kid why he should use a saw and not a shovel to cut wood to start with.

I really don't want to learn what different function do and where they can be used, I want to learn why I would use a function instead of another and why do I have to bracket some in a syntax and why sometime I must put them in "" instead.

AH! Eureka - I am looking for the "CONVENTION" on how to write VBA

Forgive me English is not my first language, I probably do not make much sense to a lot of people.

[Guido Geurs]

dear,

search on the web for " free ebook pdf vba dummy".
I have found several sites where you can download these books for free !!


br,

[Dököll]

You actually came to the right place, do stay tuned... also good job posting an example. Do tell us what you are after, what you hope to achieve with much effort, and we'll try to steer you in the right direction.

I would also search the VBA forum intensively, just to see what's there.

L8ter!

[MMcCarthy]

You will find a couple of articles in our Insights section which may help. Here are a couple to get you started.

VBA Event Driven Programming

Access Built in Functions

Macro's or VBA

Also if you have any experience with macros you can create some macros and then convert them to VBA. This will allow you to see how the code translates.

If you have any specific questions please feel free to ask them. That's why we're here.

Mary

[MMcCarthy]

To get you started with some basic conventions in VBA.

All strings (Lines of text) have to be surrounded by double quotes. However, you should note that any substrings have single quotes. By this I mean if you had a query like:

SELECT CustomerName FROM Customers WHERE City= "New York"

and you had to put that in a string in VBA to pass it to a function then you would have to write it as follows:

"SELECT CustomerName FROM Customers WHERE City= 'New York' "

I hope this makes sense.

Another convention you should be aware of is the fact that all dates must be surrounded by hash marks as follows:

#04/09/2009#

Mary

[NeoPa]

Someone posted Microsoft Access Tutorial recently. I haven't been through it in detail, but I had a quick look and it seemed pretty good. I decided to save it for re-use whenever anyone (like yourself) asked for something like this.

A couple of links that clarify what Mary was talking about (Quotes (') and Double-Quotes (") - Where and When to use them & Literal DateTimes and Their Delimiters (#)). Technically (not necessary, but is good practice if you ever want to move on to other RDBMSs) any string literal used by SQL should be enclosed in single-quotes (').

Good luck with your learning.

[Delerna]

And just to answer the two specific questions that I see in your post.

 If Me.Parent.Name = "Work_Order_Details_SubForm"  
 

here you are comparing the content of an objects property (Me.Parent.Name) against a string value, to control whether a piece of code should get executed or not.

The object is (Me.Parent) and the property is (Name).
The name property is a string therefore what you are comparing it with
must also be a string.

Speaking in the context of your question
In VBA, text enclosed within "" is a string
Text not enclosed within "" is a variable

So your code can also be written like this

 Dim Work_Order_Details_SubForm as string
 Work_Order_Details_SubForm ="Work_Order_Details_SubForm"
 If Me.Parent.Name =   Work_Order_Details_SubForm THEN
 

Here Work_Order_Details_SubForm is a variable for storing strings.

Notice there is no "". It is comparing the content of one object against the content of another object, whereas your code is comparing the content of an object directly against an actual string and therefore needs the "" for the VBA interpretor to recognise it as such.

Without the "" the VBA interpretor would think it was a variable

[Delerna]

Now for the square brackets.
This one is a bit more difficult as there are variations to the rule.

It is generally used in conjuction with the name of an object.
For example, the name of a field in an sql query

   SELECT FieldName FROM TheTable
 

Notice there are no square brackets around anything.
If however my personal naming convention separated words with spaces instead of capitalized first characters.

   SELECT Field Name FROM The Table
 

This would not work because Field and Name would be considered as two fields and there is no comma between them. The query would also look for a table called The.

Table would be considered as erroneous....incomprehensible nonsense.
You must write the query then, like this

   SELECT [Field Name] FROM [The Table]
 

The square brackets forces the two words, Field Name, to be read as one field.
Get it?
As mentioned, there are variations to the use of [] but they all exist for similar reasons.

Incidentally, this

   SELECT [FieldName] FROM [TheTable]
 

is not wrong, just unnecessary to include the [] here and therefore, that is the reason they are sometimes there and sometimes not.



I hope that helps a little. Spelling all this out in a tutorial can make for incredibly boring reading and in reality is probably better gained through experience and asking (specific) questions. It is a long road but the journey is very satisfying even if sometimes frustrating.
At least... it is for me.


EDIT
My anti tutorial comment is directed more towards things like
when to use [] and when not to.
It is not intended as a slurr against "VBA Basics" tutorials