Basic Stuff.

[Sam]

Hi ...

I am, after several years of half-hearted attempts, making a serious
effort to learn Excel VBA. Why is it so hard to do just simple stuff?? I
just get so frustrated. The help files are of little value. I almost never
find what I'm looking for, and when I do I am seldom intelligent enough to
adapt the example to my own code.

I was an amateur programmed back in the days of DOS QuickBasic, and a
pretty good one, even if I say so myself.

But I just cannot catch on to Excel VBA. I have a book, an old one.
"Using Visual Basic For Applications Excel Edition." Written by Jeff Webb,
published 1994. Outdated of course, but I couldn't make sense of this book
when I bought it back in 1994.

Why is it so hard to do simple, basic stuff?? Tonight I've been working
on a very easy problem, but, after an hour, I still have no clue. All I want
to do is assign the data in a text cell to a variable, change it (split it
into two names) then put it back in the cell and the adjacent cell. It
should take 30 seconds to write the code, but I can't figure out how to even
get the line assigned to the variable.

There are so many capricious, esoteric ways of addressing a certain part
of the worksheet. When do you use cell?? When do you use cells?? How can you
make a variable refer to a range?? What is a Collection??

I'm sure that there is logic and sense to the way Excel VBA is
constructed, but I just can't see it. To me it seems completely random and
arbitrary. I understand the theory, I think. I understand what methods are
and properties, but there seems to be no concrete advice on how to use them
in actual practice

I just opened my Using VBA book at random. Here's what I see:
range.Borders([Index]) Returns one or all borders in a range. If a range
does not have a border, the returned border object has Linestyle xlNone.

You guys no doubt know what he's talking about, but to me it might as
well be written in Swahili.

Why can't there just be a basic reference that tells a programmer the
basic info he needs to know?? How to assign a cell to a variable. How to
write a variable to a cell. How to use variables to refernce cells. (I don't
believe I have ever - I mean ever - seen an example that does not use this
structure: Range("C12:N12") or else R1C1 notation.) Instead we have page
after page of methods with no practical example of how to use them.

I don't care about linestyle or windowdisplayhorizontalscrollbars. I
just want to make simple applications and not fight the nonsense. The
language should be working with the programmer, not against him.

Thanks for letting me vent.

Sam

A man who had lately declared
That property ought to be shared,
Thought it going too far
When they called for his car,
And a list of exceptions prepared.

Thomas Thorneley,
From The Penguin
Book Of Limericks

 

[Kevin Stecyk]

Hi Sam,

I feel for you.

Learning any language, whether it is a computer language or a foreign
language, is difficult. At first, learning comes slowly and then with time
it improves. The trick is to not get defeated.

<< How to assign a cell to a variable.>>

dVar1 = Sheets("Sheet1").Range("A1").Value

<< How to use variables to refernce cells>>

Not sure, did you mean the opposite of the first example?

Sheets("Sheet1").Range("A2").Value = dVar1

<< (I don't believe I have ever - I mean ever - seen an example that does
not use this
structure: Range("C12:N12") or else R1C1 notation.)>>

Range will have to include some argument. You could have a variable as an
argument.

sImportantRange="B11"

Sheets("Sheet1").Range(sImportantRange).value=dVar1


Here is a simple little subroutine to demonstrate the above.

Sub Test1()
Dim dVar1 As Double
Dim sImportantRange as String

'\ Silly example to demonstrate some basic concepts

'\ "A1" on Sheet1 has the numerical value 5


sImportantRange = "B11"

dVar1 = Sheets("Sheet1").Range("A1").Value

Sheets("Sheet1").Range("A2").Value = dVar1

Sheets("Sheet1").Range(sImportantRange).Value = dVar1


End Sub




Check out these 3 books. The crowd here generally favors the first book as
the best. I found the first two books to be very helpful, although the
second book is more advanced. That said, I found that it presented some
material that was covered in the first book in a more straightforward
manner. But that is just one person's opinion. If you are like me, having
more than one source is helpful. I think you will really like John's book
(1st one) because he uses lots and lots of examples. If you work through
them, then by osmosis, it will begin to make sense. Again, the trick is not
giving up.


Excel 2003 Power Programming with VBA ; John Walkenbach; John Wiley & Sons;
ISBN: 0-764540726

Excel 2002 VBA Programmers Reference; John Green, Stephen Bullen, Rob Bovey,
Robert Rosenberg; 993 pages; Wrox Press Inc; ISBN 1861005709

Excel 2000 Programming For Dummies; John Walkenbach; 408 pages; Hungry
Minds, Inc; ISBN: 0764505661

I hope this message is of some benefit.

Good luck, and come back and let us know where you are having difficulty.

Regards,
Kevin

 

[Brian Tozer]

Kevin Stecyk wrote:

(Snip)

Here is a simple little subroutine to demonstrate the above.

Sub Test1()
Dim dVar1 As Double
Dim sImportantRange as String

'\ Silly example to demonstrate some basic concepts

'\ "A1" on Sheet1 has the numerical value 5


sImportantRange = "B11"

dVar1 = Sheets("Sheet1").Range("A1").Value

Sheets("Sheet1").Range("A2").Value = dVar1

Sheets("Sheet1").Range(sImportantRange).Value = dVar1


End Sub

Good luck, and come back and let us know where you are having
difficulty.

Lack of totally explicit instructions on how to implement the above example
in my Excel 2002.

Brian Tozer

 

[Kevin Stecyk]

Hi Brian,

It's good to see you again.

I had assumed (always dangerous) that if a person was asking about
programming questions, he or she has at least gotten to the VBE editor.

But maybe that was too big of a leap?

I am using XL 2000, but I think the instructions will apply to your version
as well.

Here's what you do:

1) Start Excel
2) Go to Sheet1
3) Go to Cell A1 and insert numerical 5
4) Alt-F11 (or Tools | Macro | Visual Basic Editor....takes you to the
Visual Basic Editor)
5) Insert Module
6) Copy and paste code (I will provide code again at the end).
7) Alt-Q (or File | Return and Close to Microsoft Excel)
8) Alt-F8 (or Tools | Macro | Macros)
9) You can choose to Step Into or Run at this point

10) If you choose to Step Into....you will be taken back to VBE. In VBE you
can press F8 or use the toolbar to execute each line of code.
10 A) As you move through each line of code, have a look the spreadsheet to
see the changes.

You are done.

This is a very, very simple subroutine. It merely demonstrates....

a) Assigning a value from a cell to a visual basic variable
b) Assigning a value from a visual basic variable to a cell
c) Assiging a string "B11" to a variable which was used to define a range.

You should look up in XL's help on Dim (dimension) variables. You should
note that I would have been better to use Dim Var1 as a variant rather than
as a double. That way, any value could be placed in A1. However, I used
double so that most numerical values could be placed in A1.

Also, the '\ are simply comments. VBA requires only the single apostrophe
to designate a comment. Whatever follows the single apostrophe is
considered a comment. However, I use '\ because it is easier to see it. It
stands out more. The backslash is merely part of the comment.

Sub Test1()
Dim dVar1 As Double
Dim sImportantRange As String

'\ Silly example to demonstrate some basic concepts
'\ "A1" on Sheet1 has the numerical value 5

sImportantRange = "B11"

dVar1 = Sheets("Sheet1").Range("A1").Value

Sheets("Sheet1").Range("A2").Value = dVar1

Sheets("Sheet1").Range(sImportantRange).Value = dVar1

End Sub

I hope that helps.

Regards,
Kevin




in message:

 

[Paul]

Hi Brian,

It's good to see you again.

I had assumed (always dangerous) that if a person was asking about
programming questions, he or she has at least gotten to the VBE
editor.

It all seems to be there in the help under "Automating tasks" for using
the VBA editor and a few examples to kick off under "Programming
concepts" which is the second topic under "Programming information"
after "What's New."
I'd be very surprised that someone reading this group wouldn't have
ventured into the VB editor ever.


I find most people usually get started by recording a macro of performing
an action and then inspecting what happens. Granted the code isn't great,
but it'll get you started.
This continues to be useful even after you progress beyond being a
beginner as you try to identify what the particular obscure feature you
need is called.

Paul

 

[Brian Tozer]

Thanks so much for your trouble Kevin in supplying this superb and
comprehensive answer.
I will play with this tomorrow and get back to you if I have any query.
A stroke has made learning a difficult process so I really appreciate your
effort.
Brian Tozer

 

[Brian Tozer]

Thanks Paul for your reply, but none of the terms that you quote apear in
the index of the Help for my Excel 2002 or its Help for the VBA Editor, so I
am at a loss to know what you are referring to.
Brian Tozer

 

[Brian Tozer]

Hi Kevin.
Just reporting total success, and I understood everything fine.
In the last few days I have gone thru 3 printer ink cartridges printing
hundreds of pages of info but have not come across all that you have just
explained.
Thanks so much again.
Brian Tozer

 

[Paul]

Thanks Paul for your reply, but none of the terms that you quote apear
in the index of the Help for my Excel 2002 or its Help for the VBA
Editor, so I am at a loss to know what you are referring to.
Brian Tozer

They are chapters in the contents page. I strongly suggest you summon the
Help window from Excel itself and not from the VBA editor, from Excel 2000
onwards calling it up from the VBA editor only gives you the help files for
the VBA language and nothing else. I am at a loss to explain why MS deemed
this a good idea, in Excel 97 asking for help in either place got you the
same help file, I think it is some sort of cross-office-products help
integration gone wrong.

If you are trying to figure out what's going on without reading through the
contents I can see how you'd have problems. It would be rather like trying
to learn a foreign language by opening a dictionary and picking random
words out of it.

Personally I turn off the help assistant, so I go straight to the help
contents when I press F1.

Paul

 

[Paul]

Thanks Paul for your reply, but none of the terms that you quote apear
in the index of the Help for my Excel 2002 or its Help for the VBA
Editor, so I am at a loss to know what you are referring to.

Another thing worth mentioning is that a lot of the VBA help seemed to get
dropped from the Index and text search when they "upgraded" the help system
for Office 2000, Office 2002 (I refuse to use the silly marketing name)
fares no better. If you are only looking in the Index or only asking the
paperclip you will probably not find anything of use at all for VBA.

To get to the VBA help you need to look in the contents under "Automating
Tasks" and "Programming Information".

Paul

 

[Tim Coddington]

Woah. Didn't recognize the situation hear. I was one who was
assuming too much. This tidbit may help then.
Do you realize that VBA help isn't loaded by default when Excel
is installed? Unless you did, try re-installing Excel. Do NOT use
the default package. Instead, painstakingly go through all the
installation options, and be certain to check all things having to do
with VBA. I don't know why Microsoft is being so rediculous as
to not have this loaded by default.

 

[Paul]

Woah. Didn't recognize the situation hear. I was one who was
assuming too much. This tidbit may help then.
Do you realize that VBA help isn't loaded by default when Excel
is installed? Unless you did, try re-installing Excel. Do NOT use
the default package. Instead, painstakingly go through all the
installation options, and be certain to check all things having to do
with VBA. I don't know why Microsoft is being so rediculous as
to not have this loaded by default.

Even when the VBA help is there it doesn't show up in the index or in the
text search. In my opinion the answer wizard is absolutely useless, as it
stands you want to search for a keyword in the help and you can't because
the answer wizard doesn't know it, whereas the good old text search would
have found it.

Compare with the HTML help system that came with Visual studio that
builds the index and contents on the fly by checking the .chm files in
the directory. If the directory contents change, then so does the index.
It is hugely useful to be able to step forward and back ... when I read a
book I don't have to go back to the contents page before moving on to the
next or previous page, nor do I have to guess words out of the index to
find the next page.

Paul

 

[Brian Tozer]

Thanks very much for your patience Paul, I have now found the two items
thanks to you, and will devour them.
Brian Tozer

 

[Sam]

Thanks to all of you for your responses. Last night, I was finally able
to get my code to do what I wanted, so I am feeling better now.

Sam

 

[Jeff Webb]

I know this thread is a little old, but I just saw that it mentioned my old book. Yes, it is out of date! I do think it did a pretty good job for the time, though. One of the very first sections is Analyzing Recorded Code -- I agree with others on this thread that is one of the best ways to learn

That said, I didn't write Using Excel VBA for beginners (the back cover shows Accomplished to Expert level). I assumed the beginner market would be filled by other, thinner books. I was right to some degree

I think Walkenbach Power Programming VBA book is pretty good, though I was disappointed to see it didn't roll in many of the new 2003 features. It's definitely better for beginning programmers than my old Excel book, and I think John is an excellent writer. The Wrox book is also pretty good -- more of a reference. I'm not sure how that fits in now that Wrox is owned by Wiley (I think)

If anyone's interested, I've got a new project started at www.mstrainingkits.com/Excel. There are currently draft chapters up there dealing with Excel 2003 features; Lists, XML, and Security. Again, it's not beginner stuff, but if anyone is interested I'm looking for constructive feedback

Thanks

Jeff Webb