......
What are the advantages/disadvantages to using this method ?
- | A |
1 | Somefink |
Tabelle1 |
[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] strEvalCellContent()
[color=blue]Dim[/color] CellContent [color=blue]As[/color] [color=blue]String[/color]
[color=blue]Dim[/color] strEval [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'String to be built up for Evaluate[/color]
[color=blue]Let[/color] CellContent = Evaluate("A1") [color=darkgreen]'This has been evaluated to a range object, hence the next line works[/color]
[color=blue]Let[/color] CellContent = Evaluate("A1").Value [color=darkgreen]'(The line above was an allowed Implicit of this line, that of getting the value when assinging variable to a Cell Range)[/color]
[color=darkgreen]'Let CellContent = Evaluate(A1) ' This errors as it is expecting a string but sees an undefined, unfilled variable[/color]
[color=blue]Let[/color] CellContent = ["Al"] [color=darkgreen]'Gives "A1" and not cell contents[/color]
[color=darkgreen]'Let CellContent = [Al]'Gives incompatible types and..[/color]
[color=blue]Let[/color] CellContent = [A1].Value [color=darkgreen]'...this indicates that [] has evaluated to a range object[/color]
[color=blue]Let[/color] strEval = "A1"
[color=blue]Let[/color] CellContent = Evaluate(strEval)
[color=blue]Let[/color] CellContent = [strEval]
[color=blue]Let[/color] strEval = "A" & 1 & ""
[color=blue]Let[/color] CellContent = Evaluate(strEval)
[color=blue]Let[/color] CellContent = [strEval] [color=darkgreen]'Gives "A1" and not cell contents[/color]
[color=blue]Let[/color] CellContent = Evaluate("A" & 1 & "")
[color=blue]Let[/color] CellContent = Evaluate("A" & 1 & "").Value
[color=blue]Let[/color] CellContent = ["A" & 1 & ""] [color=darkgreen]'Gives "A1" and not cell contents[/color]
[color=blue]End[/color] [color=blue]Sub[/color]
[color=darkgreen]'[/color]
[color=blue]Sub[/color] strEvalRangeSelect()
[color=blue]Dim[/color] strEval [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'String to be built up for Evaluate[/color]
Range("A1").Select
Evaluate("A2").Select
[A1].Select [color=darkgreen]'Note: This will not work ["A2"].Select. Error says Requires Object[/color]
[color=blue]Let[/color] strEval = "A2"
Range(strEval).Select
[color=blue]Let[/color] strEval = "A" & 1 & ""
Range(strEval).Select
Evaluate(strEval).Select
Evaluate("A" & 2 & "").Select
[color=darkgreen]'["strEval"].Select 'Run time error Object "Needed" - Here it has been given a String, which you cannot Select[/color]
[color=darkgreen]'[strEval].Select 'Complie error "Invalid identifier" It needs a VBA thing...so[/color]
[color=blue]Dim[/color] rng [color=blue]As[/color] Range: [color=blue]Set[/color] rng = Range("A1") [color=darkgreen] '.. here we define one ( identify rng ) as a Range Object ..and...[/color]
[rng].Select [color=darkgreen]' this will work...![/color]
[color=blue]End[/color] [color=blue]Sub[/color]
You can't build up the address string if you use the [] notation - it has to be whatever you typed when you wrote the code. That alone is enough for me not to use it as I try to avoid ever hardcoding ranges.
Also, using the brackets adds a function (and an out-of-process call at that) where none is needed. All else being equal, the more that gets done by the compiler (versus at runtime), the faster code will run.
.....
Range("A1:A5").select
vs
[A1:A5].select
What are the advantages/disadvantages to using this method ?
So I suppose[A1:A5].Select generates an unnecessary call to the Evaluate function.
Range("A1:A5").Select doesn't.
The advantage is that it's compact.
Evaluate("A1:A5").Select is just silly.
Not disputing anything, but FWIW I occasionally find it useful to do something likeYou can't build up the address string if you use the [] notation - it has to be whatever you typed when you wrote the code. That alone is enough for me not to use it as I try to avoid ever hardcoding ranges.
Range("A1:A5").Name = "x"
[x].Select