Why does Range(cells(1 1) cells(2 2)).clearcontents not work?
Alan Beban
>
> This will work when the referenced worksheet is active, not otherwise.
> The problem is that the Cells method refers by default to the active
> sheet. You should use something like:
>
> With showcell
> .Range(.Cells(2, 1), .Cells(101, 35)).ClearContents
> End With
The above involves "qualifying" the Cells Method/Property. One can also
use the following, because the object variable (rng) carries its own
"built-in" qualification; i.e., its own "built-in" implicit reference to
the relevant sheet:
Set rng = showcell.[a1]
Range(rng(2, 1), rng(101, 35)).ClearContents
Sorry, John; after our recent exchange of comments, I couldn't resist
jumping in :-)
Alan Beban be...@pacbell.net
Rraywilson
Set showcell = Application.Workbooks("Book3.xls").Worksheets("Sheet1")
showcell.Range(Cells(2, 1), Cells(101, 35)).ClearContents
does not work but
showcell.Range("a2:ag101").ClearContents
does.
Why?
John Green
The problem is that the Cells method refers by default to the active
sheet. You should use something like:
With showcell
.Range(.Cells(2, 1), .Cells(101, 35)).ClearContents
End With,
HTH,
John Green - Excel MVP
Sydney
Australia
In article <19981203003001...@ng131.aol.com>, Rraywilson
wrote:
> From: rrayw...@aol.com (Rraywilson)
> Newsgroups: microsoft.public.excel.programming
> Subject: Why does Range(cells(1 1) cells(2 2)).clearcontents not work?
> Date: 3 Dec 1998 05:30:01 GMT
Chris
you have,both don't if you haven't.
Chris
Brian Wilson
I've read you're postings this week. I'm curious, would
you explain the [a1] syntax in
Set rng = showcell.[a1]
Is Range(rng(2, 1), rng(101, 35)) the best "shorthand"
alternative for the cells method of referring to what I would
call a subset of a range (which is more than one item)?
Thanks,
Brian
Alan Beban wrote:
> John Green wrote:
> >
> > This will work when the referenced worksheet is active, not otherwise.
> > The problem is that the Cells method refers by default to the active
> > sheet. You should use something like:
> >
> > With showcell
> > .Range(.Cells(2, 1), .Cells(101, 35)).ClearContents
Bill Koran
VBA (how long can I claim that?), but here is my understanding.
Set rng = showcell.[a1]
is exactly equivalent to
Set rng = showcell.("a1")
In other words, anyplace you would refer to a range using the parentheses
and quotes you can, instead, use the square brackets. (I think I found
this info on Chip Pearson's web page.)
Bill
--
Bill Koran, P.E.
Energy Conservation Engineer
Brian Wilson <b...@ccms.net> wrote in article <3666BFD8...@ccms.net>...
Bill Koran
the code they post. I haven't tried the shorter syntax, because it
provides me, as a novice, with less info. In my defense, while Mr.
Pearson's web page described the syntax correctly, it was easy to
misinterpret:
Q I'm tired of typing Range("A1") to refer to that range. Is there a
shortcut?
A Yes. Surround the range name with square brackets, and don't use
quotes. For
example, Range("A1") and [A1] are equivalent.
--
Bill Koran, P.E.
Energy Conservation Engineer
John Green <jgr...@enternet.com.au> wrote in article
<VA.00000209.005df946@t8000>...
> Err - Thanks for the input, but not quite, Bill<g>
>
> I think you meant:
>
> Set rng = showcell.[a1]
>
> is exactly equivalent to
>
> Set rng = showcell.Range("a1")
>
> [A1] and Range("A1") are equivalent. In my stubborn old way, I prefer the
> longer version,
>
> Cheers,
>
> John Green - Excel MVP
> Sydney
> Australia
>
> In article <01be1efb$553df160$361ca798@HB021747_Bkoran.honeywell.com>,
Bill
> Koran wrote:
> > From: "Bill Koran" <bko...@aol.com>
> > Subject: Re: [a1] syntax
> > Newsgroups: microsoft.public.excel.programming
> > Date: Thu, 03 Dec 1998 12:27:47 -0800
Alan Beban
newsreader, but not Brian Wilson's earlier post, so I'm using Bill's as a
peg for my answer to Brian's questions.
First, basic range references are not case sensitive, so Range("A1") and
Range("a1") are equivalent. And in almost all cases [<bg> for Chip
Pearson--private joke], [a1] is equivalent to Range("A1"). So, for
example, [a1:c3] is equivalent to Range("A1:C3"), [a1:c3, d4:h6] is
equivalent to Range("A1:C3, D4:H6") to refer to the union of the two
ranges, and [a1:c3 b2:h6] is equivalent to Range("A1:C3 B2:H6") to refer to
the intersection of the two ranges.
An important exception is that whereas Range("A1")(2, 3), for example,
refers to Cell C2, [a1](2, 3) returns an error message; although
[a1].Item(2, 3) successfully refers to Cell C2.
I happen to favor the [a1:c3] syntax for the simple reason that
Range("a1:c3") requires using the shift key on the keyboard 5 times as
contrasted with once for the bracketed syntax.
So, as to Brian's specific questions:
> > would you explain the [a1] syntax in
> >
> > Set rng = showcell.[a1]
after Set showcell = Worksheets(1), the above is equivalent to Set rng =
Worksheets(1).Range("A1")
and
> > Is Range(rng(2, 1), rng(101, 35)) the best "shorthand"
> > alternative for the cells method of referring to what I would
> > call a subset of a range (which is more than one item)?
I wouldn't touch with a ten-foot pole the question of what is "the best"; I
do, however, think that it is convenient that when the ranges being
referred to are not on the Active Sheet, the object variable (rng, in the
illustrated case) carries its own qualification. That is, instead of
Range(Sheets(1).Cells(2, 1), Sheets(1).Cells(101, 35))
or With Sheets(1)
Range(.Cells(2, 1), .Cells(101, 35))
End With
one can use
Set rng = Sheets(1).[a1]
Range(rng(2, 1), rng(101, 35))
People with more experience than I have voiced (if you can "voice" things
in emails and postings) the question whether the bracketed syntax, and the
collection accessor form or Item Property [e.g., rng(1, 2)], methods of
range referencing are less readable or less intuitive than the more well
known Range("A1") and Cells Method/Property syntaxes, particularly for less
experienced users. I have proselytized the former syntaxes for many months
in these newsgroups, so I am probably not a very good judge; but having
said that, I do think the issue is more a function of how range referencing
is "taught" and has been historically implemented, rather than an issue of
innate intuitivity (?) or readability. But to each his own, especially for
oldtimers like John Green (who may think he's older than I am, but it's
probably only that he's ten times more experienced).
Alan Beban be...@pacbell.net
Bill Koran <bko...@aol.com> wrote in article
<01be1efb$553df160$361ca798@HB021747_Bkoran.honeywell.com>...
Alan Beban
Alan Beban be...@pacbell.net
Laurent Longre <lon...@wanadoo.fr> wrote in article
<366730...@wanadoo.fr>...
> Bonjour,
>
> In VBA, [Anything] is equivalent to Application.Evaluate("Anything")
> (Application is optional). The only difference is that the expression
> between [] is "hard-coded", whereas the argument of the Evaluate
> function can be variable (Evaluate(MyVariable), Evaluate(Var1 & Var2)
> etc).
>
> Evaluate("Anything") and [Anything] evaluates and returns the result of
> "Anything" as if it were used in a worksheet. The argument can be a
> formula involving cell references and built-in or add-in worksheet
> functions, for instance:
>
> [SUM(1,2,4,8)] returns 19
>
> [AVERAGE(A1:A100)/Sheet2!A1] returns the same result as the
> corresponding worksheet formula (in this case, A1:A100 refers to the
> active sheet).
>
> Since worksheet formulae can return not only values but also range
> references, so the Evaluate/[] function. For instance, [A1] returns a
> range reference, because the equivalent formula =A1 entered in a cell
> returns also internally a range reference (which is automatically
> coerced into its value). Of course, you can use this reference exactly
> like any "true" VBA Range object:
>
> [Sheet3!A1] = 5 puts '5' in the range Sheet3!A1 (you could also use
> [Sheet3!A1].Value = 5, but .Value is the default property for a Range
> object).
>
> Set MyRange = [OFFSET(A1:C30,3,3)] stores a reference to the range
> D4:F33 of the active sheet in the "MyRange" variable.
>
> ... and so on.
>
> Evaluate/[] can also return the result of an array formula (exactly like
> the EVALUATE() XLM function, on which it is probably based), for
> instance:
>
> [SUM((A1:A100>10)*(B1:B100<50)] returns the same result as the array
> formula {=SUM((A1:A100>10)*(B1:B100<50))} (ie the number of rows
> matching the criterias Ax > 10 and Bx < 50)
>
> MyArray = [ROW(1:10000)] stores the 2 dimensional array (1 column)
> {1;2;3;4;...;9999;10000} in MyArray (variant). No need to say that it is
> much more efficient than filling MyArray with a 'For I=1 To 10000 ...
> Next' loop.
>
> [A1:A5000] = [ROW(1:5000)] puts the values 1, 2, ..., 5000 in the range
> A1:A5000.
>
> This feature allows VBA user-defined functions to use (to a certain
> extent) array formulae, for instance the following function, which
> returns the cumulated number of characters contained in a range:
>
> Function NBCHAR(MyRange As Range) As Long
> NBCHAR = Evaluate("SUM(LEN(" & _
> MyRange.Address(external:=True) & "))")
> End Function
>
> This function is equivalent to the array formula {=SUM(LEN(Range))}.
> It's of couse much more efficient than the "pure" VBA function:
>
> Function NBCHAR2(MyRange As Range) As Long
> Dim MyCell As Range
> For Each MyCell In MyRange
> NBCHAR2 = NBCHAR2 + Len(MyCell)
> Next MyCell
> End Function
>
> If you test both functions and pass to them references to very large
> ranges, you will see that the evaluated array formula in NBCHAR runs
> much faster than the For... Next loop in NBCHAR2.
>
> Unfortunately you can't use VBA arrays in this way, but otherwise it's a
> powerful method which can be applied of course also in VBA Subs.
>
> Hope this helps,
>
> Laurent
John Green
John Green
I think you meant:
Set rng = showcell.[a1]
is exactly equivalent to
Set rng = showcell.Range("a1")
[A1] and Range("A1") are equivalent. In my stubborn old way, I prefer the
longer version,
Cheers,
John Green - Excel MVP
Sydney
Australia
In article <01be1efb$553df160$361ca798@HB021747_Bkoran.honeywell.com>, Bill
Koran wrote:
> From: "Bill Koran" <bko...@aol.com>
> Subject: Re: [a1] syntax
> Newsgroups: microsoft.public.excel.programming
> Date: Thu, 03 Dec 1998 12:27:47 -0800
>
>
Laurent Longre
[SUM(1,2,4,8)] returns 19
... and so on.
Hope this helps,
Laurent
Dana DeLouis
subject?
I have noticed that the VB Evaluate command [ ] can work with basic
functions and operators on a range. However, I have never been able to get
[ ] to work with Custom VB functions. I expect that the answer is that it
can not, but I thought I would ask. Is anyone aware on any techniques?
Here is an example on a small range of numbers.
Instead of doing a For - Next loop on each cell, one can convert a range of
numbers to integer in 1 pass with:
'// Works OK
[A1:B10] = 5.3
[A1:B10] = [Int(A1:B10)]
If one wants to apply a function to a range in 1 pass, [ ] can handle simple
operators like '*' as in:
'// This works. Multiplies all numbers in range by 2
[A1:B10] = 5.3
[A1:B10] = [(A1:B10)*2]
However, I have never discovered how to apply [ ] with a custom function to
a range of cells.
Does anyone have any insight or any techniques to make it work? The simple
function to multiply a number by 2 is picked for simplicity. I usually have
a more complicated function in mind.
Thank you for any insight / help.
'// However, evaluating a custom function does not work
'// Try to use custom function to multiply all numbers in range by 2 with
1 pass
[A1:B10] = 5.3
[A1:B10] = Application.Evaluate("Mul_2(A1:B10)") '<- #VALUE Errr
'// -or-
[A1:B10] = 5.3
[A1:B10] = [Mul_2(A1:B10)] '<- #VALUE Errr
End Sub
Function Mul_2(n As Double)
'// Just multiply a number by 2
Mul_2 = n * 2
End Function
Laurent Longre <lon...@wanadoo.fr> wrote in message
<366730...@wanadoo.fr>...
>
> Evaluate("Anything") and [Anything] evaluates and returns the result of
> "Anything" as if it were used in a worksheet. The argument can be a
> formula involving cell references and built-in or add-in worksheet
> functions, for instance:
>
> [SUM(1,2,4,8)] returns 15
Brian Wilson
Thanks! That was everything you ever wanted to know about [a1] :-)
And thanks to Bill and Alan too.
Brian
John Green
Especially:
MyArray = [ROW(1:10000)] stores the 2 dimensional array (1 column)
{1;2;3;4;...;9999;10000} in MyArray (variant). No need to say that it
is much more efficient than filling MyArray with a 'For I=1 To 10000
..
Next' loop.
John Green
functions. Even a simple application of your Mul_2 does not work:
[L1] = Application.Evaluate("Mul_2(10)") '<- #VALUE Err
let alone the more complex array calcs. You could convert your function to
return an array:
Function Mul_2(n As Range)
'// Just multiply a number by 2
nAddress = n.Address(external:=True)
Mul_2 = Evaluate("(" & nAddress & ")*2")
End Function
and it can be used in a a worksheet array calculation. For example, array
enter the following into D1:E10
=Mul_2(A1:B10)
But it still does not process in the evaluate method,
Cheers,
John Green - Excel MVP
Sydney
Australia
In article <#dIHdG1H#GA....@uppssnewspub04.moswest.msn.net>, Dana DeLouis
wrote:
> From: "Dana DeLouis" <da...@email.msn.com>
> Subject: Re: [a1] syntax
> Date: Fri, 4 Dec 1998 01:23:07 -0500
> Newsgroups: microsoft.public.excel.programming
Laurent Longre
John Green wrote:
>
> I believe you are correct - the evaluate method does not handle custom
> functions. Even a simple application of your Mul_2 does not work:
>
> [L1] = Application.Evaluate("Mul_2(10)") '<- #VALUE Err
It works fine by me, and puts 20 in L1 (??!!!???)
> let alone the more complex array calcs. You could convert your function to
> return an array:
Yes, it doesn't work when the custom VBA code is used as an array
function. I don't know why. Curiously, this problem occurs only with VBA
UDFs, and not with XLL array functions.
It doesn't occur with XLM array functions, too, like this one:
=ARGUMENT("Param",72)
=RESULT(64)
{=RETURN(Param*2)}
> Function Mul_2(n As Range)
> '// Just multiply a number by 2
> nAddress = n.Address(external:=True)
> Mul_2 = Evaluate("(" & nAddress & ")*2")
> End Function
[Off-topic]
Just for fun, I have tried to build a VBA function based on Dana's Mul_2
example, which would have the same "polymorphic" behaviour as XLM custom
functions like the one above.
Like any XLM function, this VBA function should be usable:
- As a simple "single cell" function : =Mul_2(10)
- As a multi-cells array function :
. with a Range argument : {=Mul_2(A1:C10)}
. with an Array argument :
{=Mul_2({1,2;3,4;5,6})} (constant array)
or {=Mul_2(A1:C10/E1:G10)} (calculated array)
I have obtained the following code (which uses Evaluate if the argument
is a range)
Const vbMatrix = vbArray + vbVariant
Function Mul_2(Param)
Dim S1 As Integer, S2 As Integer
Dim I As Integer, J As Integer
Dim Res() As Double, ParamType As Long
If VarType(Param) <> vbMatrix Then
Mul_2 = Param * 2
ElseIf TypeName(Param) = "Range" Then
Mul_2 = Evaluate(Param.Address (external:=True) & "*2")
Else
S1 = UBound(Param, 1)
On Error Resume Next
S2 = UBound(Param, 2)
If Err Then ' => 1-row range
ReDim Res(1 To S1)
For I = 1 To S1
Res(I) = Param(I) * 2
Next I
Else
ReDim Res(1 To S1, 1 To S2)
For I = 1 To S1
For J = 1 To S2
Res(I, J) = Param(I, J) * 2
Next J
Next I
End If
Mul_2 = Res
End If
End Function
This is probably not the best version. Anyway, the contrast between a
such VBA code and the 3 cells of the equivalent XLM function is rather
funny, isn't it? <g>
Amicales salutations,
Laurent
Myrna Larson
wrote:
>Yes, it doesn't work when the custom VBA code is used as an array
>function. I don't know why. Curiously, this problem occurs only with VBA
>UDFs, and not with XLL array functions.
I've gotten curious results with Evaluate/[] on array functions. One day
they will calculate correctly, the next day -- without any changes in the
code -- they don't. That was with XL95. Haven't fiddled with this with
XL97.
--
Myrna Larson
e-mail to: myrna...@csi.com
Laurent Longre
>
> I've gotten curious results with Evaluate/[] on array functions. One day
> they will calculate correctly, the next day -- without any changes in the
> code -- they don't. That was with XL95. Haven't fiddled with this with
> XL97.
With XL 97 (SR-1) too! It didn't work yesterday, and now it works.
Another Excel mystery...
Laurent
Myrna Larson
wrote:
>Myrna Larson wrote:
You might not believe this, but I am OVERJOYED to hear you say that. When I
was playing around with it several months ago, I began to think I was
losing my mind!
>Another Excel mystery...
Or bug <g>.
Laurent Longre
>
> You might not believe this, but I am OVERJOYED to hear you say that. When I
> was playing around with it several months ago, I began to think I was
> losing my mind!
Yes, but how can you be sure that I am not losing it too? <g>
Laurent ;-)
John Green
(If you are, or anyone else is, still following this thread<g>)
You are right - Dana's function does work with:
[L1] = Application.Evaluate("Mul_2(10)")
It was a modification of her function, using evaluate, that did not
work:
Function Mul_2(n As Double)
'// Just multiply a number by 2
Mul_3 = Evaluate("(" & n & ")*3")
End Function
Laurent Longre
John Green wrote:
>
> (If you are, or anyone else is, still following this thread<g>)
Of course, I am :-)
> You are right - Dana's function does work with:
>
> [L1] = Application.Evaluate("Mul_2(10)")
>
> It was a modification of her function, using evaluate, that did not
> work:
>
> Function Mul_2(n As Double)
> '// Just multiply a number by 2
> Mul_3 = Evaluate("(" & n & ")*3")
> End Function
It seems to me that it is the same with VBA array functions. If a VBA
function (returning an array or a single value) uses itself Evaluate()
it doesn't work. If it doesn't use itself Evaluate(), it works.
Comme quoi, tout a une limite en ce bas monde... :-(
Amicalement,
Laurent
====================
"WordPerfect isn't, Excel doesn't, Works won't" (J. Walkenbach)
Myrna Larson
wrote:
>Yes, but how can you be sure that I am not losing it too? <g>
I read your messages <g>. If we're both losing it, at least we're moving in
the same direction, so I'm in VERY good company <g>.
David J. Braden
Myrna that I was puzzled that I couldn't fast-assign arrays with Evaluate on
"ROW" or "COLUMN" and such constructs, but could do so with "{1;2;3}).. Started
up my computer after she notified me of the thread, and *the same code* ran
successfully. Weird thing is, this is a Mac, likely a different system than you
guys are running. I've been trying to get the code to not work since, but with
no luck (perverse, isn't it? but I wanted to discover the circumstances that
lead to its failure.) Anyway, inconsistant behavior of the function is a
definite bug (hey, if it works sometimes, why not all the time?), and *not*
platform specific.
I *do* seriously hope that the folks at MS can fix this in the right direction:
that is, let us fast-assign matrices; don't "fix" the problem by removing the
ability to fast-assign.
And Laurent and Myrna: we're all crazy, but that seems independent of the issue! <vbg>
--
David J. Braden
TMY Research