I have never had anything to do with the Excel spreadsheet
Offset
The documentation for the Excel spreadsheet
Offset does say that it returns a range reference, but I never realised that the thing inside the
" " in
Range(" ") would be evaluated similar to like the
Evaluate(" ")
These 4 all do the same ( in a few of my computers, systems, Excels, that i just tried...) , which is to select the range
A3:B4
Evaluate("OFFSET($A$3,0,0,2,2)").Select
Range("OFFSET($A$3,0,0,2,2)").Select
Evaluate("=OFFSET($A$3,0,0,2,2)").Select
Range("=OFFSET($A$3,0,0,2,2)").Select
(
So this thing is working for me in Excels 2003 – 2013 )
I have seen some similarities in the way both
Evaluate(" ") and
Range(" ") react to a string address reference in the
" ", but this "Offset( )" or "=Offset( )" in the
Range( ) is a new one on me.
I can’t quite figure out why VBA would decide to do an evaluate in the
Range(" ") when it sees
Offset…
Or… Having a quick look at this… these sort of things also work
Range("=Indirect(""A1"")").Select
Range("=INDEX(A1:B2,1,1)").Select
Range("=CHOOSE(1,A1,B1)").Select
Range("=IF(1=1,A1)").Select
Couple this with the fact that
Evaluate(" “) does not quite work in the simple way of doing exactly what is in it as if it was in a cell, ( examples like
Evaluate("A1,C3") returns us a two area range object, just like
Range("A1,C3") does, or we can fiddle with it the dependence trees of a procedure run to give us a UDF that can change values in cells other than the cell in which the UDF is used ) … then this opens up some interesting ideas.
Or…
Its possible that
Range(" ") and
Evaluate(" “) are almost the same, and that like
Range(" ") does something like this
Code: Select all
Sub Testit() '
MethodOfRange("A1:B2").Select
MethodOfRange("A1:Bsafh").Select
End Sub
Function MethodOfRange(ByVal strEval As String) As Range
Dim vEval As Variant
On Error GoTo MethodOfRangeFail
Let vEval = Evaluate(strEval).Address
On Error GoTo 0
Dim RngEval As Range
Set RngEval = Evaluate(strEval)
Set MethodOfRange = RngEval
Exit Function
MethodOfRangeFail:
MsgBox prompt:="Range Method Function Property Failed to be one, " & vbCr & vbLf & "(and by the way, you have raised my exception state)"
End
End Function
That possibly helps partially explain why Evaluate wont work on a closed workbook reference ( although I have figured out in the meantime almost how to do that now also ) …_
_.. I will need to look into this a bit further. I will post back here in a year or five, when I think I have figured it all out
Interesting.. I thought range referencing was part of that minuscule portion of the vast area of VBA that I was passably clued up on. Clearly I missed something…
Ref https://www.myonlinetraininghub.com/exc ... ment-84892
_.____________________________________
@Ken
_1 As I now figured out what I had missed – I have tried various versions of what you are doing in my various Computers and Excels, and all is working correctly as we expect. Can you give us a sample workbook that demos your problem. Maybe some strange data is causing the problem
_2 Where did you come across the idea to use the Excel spreadsheet
Offset inside the VBA
Range(" ") ? ( This and related stuff I am starting thinking about could be very interesting ) .
_.___________________
Edit P.S.
HansV wrote: ↑18 Feb 2021, 18:55
…in the non-Microsoft 365 version of Excel 2019.
That’s something else I missed … I didn’t know there was a non Microsoft version of Excel
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also