I'll answer this as I read it, top-to-bottom, so my answers might be a
little disjointed ...
> Please bare with me, I want to understand and am not trying
> to be a wise guy.
Not a problem. I'm glad you didn't think I was just trying to be picky,
too.
> I still have problems telling a parameter from a method as well as
> which methods apply to which objects.
Firstly, we have functions and subroutines (subs) in VBA. Under normal
circumstances, they should be called by these terms, although it's common to
use either term "function" or "subroutine" in a generic term when
perhaps the other one is meant.
Particularly, when code is separated out into subroutines or functions, the
generic term "subroutine" is often used for both. (In some other languages,
there are no subroutines, just functions, and that term will be used
throughout.
To be precise on this, however, a function returns a value and can be used
inside an expression. A subroutine doesn't return a value and is a
statement in itself, possibly with a "call" in front of it.
Then we have parameters. These are what is inside the parentheses of a
function or subroutine declaration and are supplied in the call to the
function or subroutine. The term "formal parameter" is applied to the one
that is in the definition of the subroutine or function (and is used inside
that routine); the term "actual parameter" is applied to the one that is in
the call to the subroutine or function.
The term "method" is an object-oriented term and signifies the method with
which you communicate with the object. It applies equally to methods
defined with function and with sub in VBA. So a method is a public function
or sub defined in an object/class.
Private functions or subs defined in a class module should not be called
methods (although it is hard to get out of the habit of calling them
"private methods") and should really be called "private functions" (or
subs).
Private variables declared in the main part of a class should be called
attributes, although other names are used as well, including "private
variables". Public variables declared in the main part of a class are
usually called by some sort of rude name (they are not "nice") but can be
called "public attributes", "public variables" or even "method variables".
Easiest way to get around what to call them is not to have them.
Re which methods apply to which objects. There is nothing to assist you
here (except for the excellent help you get from excel when you type the
dot). The methods that an object has depends entirely on the original
programmer, although there are some conventions that are reasonably well
adhered to in the Excel objects.
> Intellisense shows parameters and methods which don't necessarily apply to
> a given object, so this
> is a pitfall.
There's no shortcut to this. You've just got to learn them or use help a
lot.
> I try things and the solutions I arrive at usually
> make sense when I get to the proper form/syntax (and things
> subsequently make sense reading MSDN, when MSDN was confusing in the
> beginning).
As you write more and more VBA code, some of the constructs will stick in
your mind, and for others, you'll be able to remember what you did that in
last time, and go there and grab it out. I have written more lines of VBA
than is probably healthy, and I still very frequently go back and grab code
out of previous programs. Basically I never throw anything away.
> I'm used to assembler and old Basic. OO has new and
> strange concepts and terminology.
Unfortunately, the object implementation in VBA is "so near but so far" from
more traditional OO that it is sometimes more confusing than a "proper" OO
language is.
When I first started writing user classes in VBA, I had a fair bit of
trouble, despite many years of using OO languages.
> So, yes I used the wrong term, but "Target", indeed provides some
> kind of a link to the changed cell and I get it now - a reference to
> it. I was able to print simply "Target" and see the contents of the
> cell and print Target.Address and see the reference in RC format. So
> that's cool.
Actually, their choice of "Target" as the parameter name doesn't really
help the situation. What is the "target" of an event that fires after a
cell has been changed and after the focus has moved? Even a simple
parameter name such as "Cell" would have been more intuitively
understandable for this event. So feel a bit of annoyance at the programmer
that dreamt up that formal parameter name.
> However, here's a confusing point and this is part of the misleading
> part. Doing:
> Debug.Print Target
> results in the "contents" of the cell (If it has any) showing in the
> immediate window.
Yes, this is the default for a cell (the .Value method call) when a simple
value is needed. I have found similar problems in code written by
professional programmers. They omit a method call and then wonder why they
aren't actually getting what they wanted.
The following code
Dim A as Range
Dim B as String
Set A = Cells(1, 1)
B = Cells(1, 1)
is quite legal (unless I stuffed it up), yet the thing being assigned is
very different in the two circumstances. At least, the presence here of a
Set gives you an indication. But, if the cell is being passed as an actual
parameter of a subroutine call, there isn't even a Set.
> From this it is easy to infer that target "represents" the *content*
> of the cell. So it looks like either a variable -- Ok it's a
> reference with a default definition of the value (IF there is
> something there). So I would call this a mixed metaphor..whatever,
> I have it working...
Your point is a good one. If I had been designing the CBA Excel interface,
I would have not provided a default method call. It is no effort to put
.Value on the cell, and sometimes makes all the difference for clarity.
> Except that my above observed behavior obscures this.
Yes. That's why I raised it. It's one thing that the VB environment
doesn't really help you with, and you need to keep constantly in mind.
> I'm not debating,
(No, I'm not taking it that way)
> I'm just pointing out the source of confusion.
And you are right. And it's not just a trap for the amateur.
> This is part of the VBA-assumes-what-you-want-and-gives-it-to-you paradigm
> which makes it easy to use at other times (like polymorphism), or
> gets you in trouble when that wasn't what you wanted.
....
> New_Variable = Target [[ results in VALUE being transferred ]]
Provided New_Variable is a "value" variable, rather than an object variable.
> I think I got your previous, but writing these explanation isn't
> easy. However, what you describe isn't my problem. I don't need the
> BEFORE-CHANGE value.
Yes, I think that's where it went wrong. When I was writing the original, I
had the "before" in mind as a requirement too. When I re-read the question,
I was clear that I was mistaken.
> This is where I was going wrong. _Change() fires (as I wanted to
> understand it) in response to a change in value, therefore I think it
> should fire the routine after the change in value, but before the
> focus change to a new cell -- seems logical to me, perhaps.
> Therefore any reference to ActiveCell should be BEFORE the Focus
> change as well. There is a _ChangeSelection() event which (in my
> opinion) should fire after both the value/content change and the
> focus change. I'll drop this and move on to my next related question.
Your reasoning is quite sound. Just to make things worse ... inside a form,
the change event fires for a text box every time the box is changed in any
way (i.e. for each character typed into it). This is almost unusable and
leaves you longing for something more like the cell change event. :-(
> Re-cap.
> Enter "c" in a cell. _Change() fires and all is well in Mudville.
> Target allows me to do the magic to change this to "CW" (I take care
> of upper/lower case too).
> HOWEVER, if I CLEAR a GROUP of cells (select more than one cell then
> Alt-E, A,C), when the _Change() event handler fires, I get an ERROR
> of "Type mismatch". for this:
>
> Debug.Print " Target = "; Target
I think this is just telling you that a multi-cell range doesn't have any
sort of default that Debug.Print can use. I think you should be able to do
something like
Dim Cell as Range
For Each Cell in Target
Debug.Print " Target (part of) =", Cell
Next Cell
or simply
Debug.Print " Target (1st cell) =", Target.Range("A1")
However, if the area you cleared was not a single block of cells, then I
don't think the .Range("A1") method of grabbing the top left of a range
would work. You'd probably need to look at the Areas that Target covers.
In a practical sense, you'll need to look at Target to see if it is giving
you a single cell's change or something more sinister. For instance, one of
my spreadsheet templates has code in it to cater for a person who tends to
type in lower case a lot and to abbreviate other things that are not
supposed to be abbreviated in these sheets. So, in ThisWorkbook, I have
Private Sub Workbook_SheetChange(ByVal Sheet As Object, ByVal Cell As Range)
Call FixAbbreviations(Cell)
End Sub
And then
Public Sub FixAbbreviations(ByVal Cell As Range)
If VarType(Cell) = vbString Then
Select Case Cell.Value
Case "tbd", _
"tba", _
"kp", _
"wp", _
"ds", _
"n/r", _
"n/a": Cell.Value = UCase(Cell.Value)
Case "nil", _
"NIL": Cell.Value = "Nil"
Case "NR", _
"nr": Cell.Value = "N/R"
Case "NA", _
"na": Cell.Value = "N/A"
End Select
End If
End Sub
The important statement here is
If VarType(Cell) = vbString Then
It does two things for me. Firstly it stops me looking for strings in
non-string cells. But more importantly, it ensures that I'm only checking a
single cell.- not an area. There are other ways of checking for this, of
course - this is just the one I chose at the time as being simple and
actually doing two checks in one.
> This is baffling.
> I did the Debug.Print because I got the same error where I was using
> Target to get to the value (which is now null due to clearing).
> (again, changing the code to use Target as a reference may solve
> this)
>
> I tried to do various things to detect if "Target" represents >1 cell
> & the ActiveCell is null, but couldn't find any Intellisense choice
> which told me this. "Target.Address" gives me the range (i.e
> $C$1:$D$3) of the cleared Range.
> What I wound up doing was to check if Target represented more than
> one cell by using the Properties (I think) of RowHeight and
> ColumnWidth then simply not execute the offending code.
Possibly the easiest attribute to check is Count. It will tell you how many
cells are selected.