Tests and Notes on Range objects in Excel Cell

**DocAElstein** · 07-22-2023, 01:39 PM

General all purpose version of our final Evaluate Range solution Range variable, The right hand side, RHS, of a general purpose code line.
The right hand side, RHS, of a general purpose code line.
As far as the RHS of the equation goes, that is our Evaluate Range bit, and that will want to be referencing our test data, which is simply Rng, as far as VBA is concerned – that is the whole point about using a variable,
However we need to remember that we need Excel spreadsheet syntax inside the Evaluate("____") . For simple hardcoding that is =A1:F7 , just as we would write it in the cell. We could also use fixed cell referencing, =$A$1:$F$7 , as for our purposes it would work just the same. So they are OK for us.
For our general purpose standardised solution we want to make it dynamic, in other words we don’t want to hard code to a specific range, but would like to be able to get the Excel syntax from our VBA Rng variable, regardless of what the actual range might be at any time in our test and development
The VBA Range.Address Property (Excel) is useful here ( https://learn.microsoft.com/en-us/of....range.address )
The VBA Range.Address Property
The VBA Range.Address Property has a few arguments, here is the first 4
RowAbsolute:= default value is True, which gives the absolute $ type references $A$1:$F$7 , which are OK for us
ColumnAbsolute:= default value is True, which gives the absolute $ type references $A$1:$F$7 , which are OK for us
ReferenceStyle:= default gives us the default Excel spreadsheet column Letter and row Number referencing ( in other words A1, B$3 etc., rather than RC[-1] stuff ) which we want
External:= This could do with a bit of discussion:
If we set this to True, which is not the default we are arguably doing things a bit better since we are ensuring we refer to the correct worksheet, (and the correct workbook).
https://i.postimg.cc/TYCc8Fc4/Range-...ernal-True.jpg
https://i.postimg.cc/zGvFq7tX/Range-...ernal-True.jpg
https://i.postimg.cc/jS1Q2fm6/Range-...ernal-True.jpg
https://i.postimg.cc/LXmjVN2z/Range-...ate-Window.jpg
And/ but note a couple of things,
_Although the full syntax is allowed in the worksheet, ( https://i.postimg.cc/jS78Gv9p/Full-e...-Worksheet.jpg
https://i.postimg.cc/fTF221H7/Full-W...-Worksheet.jpg ) , it is not necessary in the worksheet, since the formula is in the worksheets, so the extra syntax is redundant
_Evaluate(" ") is not necessarily always linked to a worksheet, so the full reference could be a good idea.
However, relying on the defaults for the VBA Range.Address Property on the RHS helps us to simplify the RHS equation, so we will not do this, but instead note*** we can do this better, more fuller referencing in another way, Later***.

So the point of this post was to produce a general purpose Evaluate Range type solution to then go on and extend, and this last bit was considering the RHS
Hard coded had in it of this form for the data range, , A1:F7 , and we noted that this will do as well, $A$1:$F$7
We can get that by relying on the defaults in the The VBA Range.Address Property and so can , in VBA get something like that from Rng.
https://i.postimg.cc/5jdHjw5R/Range-...Properties.jpg
Attachment 5114

Code:

Sub DevelopmentTest()  '   https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21964&viewfull=1#post21964
Dim Ws1 As Worksheet
 Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
Dim Rng As Range
 Set Rng = Ws1.Range("A1:F7")
Debug.Print Rng.Address '  gives im Immediate Window        $A$1:$F$7      ( After running macro,  hit  keys    Ctrl+g    from  VB Editor to get Immediate window( Direktbereich in German), in which this code line should have put the value of    Rng.Address(External:=True)      something like this sort of form      '[Number stored as text, alignment of numeric values in cells.xls]Sheet1'!$A$1:$F$7
Debug.Print Rng.Address(External:=True) ' After running macro,  hit  keys    Ctrl+g    from  VB Editor to get Immediate window( Direktbereich in German), in which this code line should have put the value of    Rng.Address(External:=True)      something like this sort of form      '[Number stored as text, alignment of numeric values in cells.xls]Sheet1'!$A$1:$F$7
End Sub

We want this result , $A$1:$F$7, to be available inside Evaluate(" ")
Possibly one of the most useful things about Evaluate(" ") is that it takes a string of text, in other words, what goes in between the " ") is in the syntax of what you would put in a worksheet cell. In VBA we are free to use actual hardcoded text and/ or VBA things to build up that text within VBA, following the usual VBA syntax rules of having text and other things joined by a __&___ Actual hard coded text, as you want in the worksheet formula goes inside a " ") pair. VBA things in VBA syntax should not be inside a " ") pair, as then VBA will first “do them” , and as long as they return a text string, the _&__ or _&_s will finally result in one final string.
If we are only using a single VBA thing, and nothing else, then " ") no pair is required, since the use of a [FONT=Courier New]" ")/FONT] pair in VBA coding tells VBA that it is being given actual hardcoded text. When the VBA thing is done, it returns a string, or text, which any _&__s would join to other text. What I am trying to say here is that to get the text $A$1:$F$7 inside Evaluate(" "), then this would do
Evaluate(Rng.Address)

As far as what VBA “sees”, that is the same as if you were to write this
Evaluate("$A$1:$F$7")

A few oddities
It is not clear why you do not need a leading =, but it will accept it, and these return the same as if the = was ommited.
Evaluate("=" & Rng.Address)
Evaluate("=$A$1:$F$7")
My guess is that VBA assumes you have it/ effectively adds it by default if you miss it out. (Perhaps we should note here that we cannot write a simple text in Evaluate("__") as we would in a cell. This would error Evaluate("xyz")
Evaluate("__") will however take a number. This would not error Evaluate("6")
So there is not a perfect correlation between what we put in Evaluate("___" ) and what we put in a cell.
Perhaps Evaluate("__") is associated with all things in a cell to do with numbers and calculations. Simple text writing in a cell is something extra for a worksheet and slightly outside what Excel is about. Excel is mainly about boxes with numbers in it, and calculations on them

Almost finished, back to the main issue
Personally, even if I am just using a VBA thing in the Evaluate("__"), I prefer to include both leading and trailing " " bits, like this
Evaluate("=" & Rng.Address & "")
Doing that has the advantage, for example, of making it more easy to build up a final string.
In our case, for example, we finally want =1*A1:F7 ( or =1*$A$1:$F$7 will do just as well )
I think it is fairly easy at this stage to see what we finally need on the RHS:
__________________Evaluate("=1*" & Rng.Address & "")

One final Useful Tip
A final useful Tip, that helps to make sure you halve the correct syntax inside the Evaluate("___ ")
If we try to be very precise, I think we can say that it is the thing inside the brackets in this, Evaluate(___ ) , that takes the syntax of a string. Now, bearing that in mind, along with the fact that in VBA coding an enclosing " " needs to be used to tell VBA that you are giving it text, we can either hard code like this
_________________________ = Evaluate("=A1")
, or use a string variable like this
Dim strEval As String
_ Let strEval = "=A1"
_________________________ = Evaluate(strEval)
( We must always remember the strange exceptional difference between what we can put in a cell and what we can put in = Evaluate("___") – Unlike in a cell, it will not take a simple text , in Evaluate("___") it must be a formula or number. )

It is very easy to get text and text shown in enclosing "s mixed up, since often in various writings an enclosing " " pair may or may not be used. As an approximate general rule we can often say that
_ (i) a medium predominantly used for displaying normal human readable basic written text, will not display the text in enclosing "s.
_ (ii) a medium which uses text but may not predominantly be used to display normal human readable basic written text, or a medium not used to display some specific normal human readable basic written text that comes from somewhere else, (for example, when quoting some text from somewhere else) will enclose the normal human readable basic written text in enclosing "s.
VBA is like (ii) . It needs to be or otherwise coding might get mistaken for simple normal human readable basic written text

The Immediate Window, is like (i) , because its primary use is to allow us to see the single value in a simple variable, or one element of an array.
OK, now the Tip
What is inside the " " here, Evaluate(" ") or the actual text in our variable strEval is what we would manually put in an Excel cell.
So if we use
Debug.Print strEval
, then what is seen in the Immediate window should have the correct syntax for what we would manually write in an Excel cell. If it is not then, Evaluate(strEval) will most likely error.
Using our previous example,

Code:

Sub CheckSyntaxForEvalString()
Dim Ws1 As Worksheet, Rng As Range
 Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
 Set Rng = Ws1.Range("A1:F7")
Dim strEval As String
 Let strEval = "=1*" & Rng.Address & ""
Debug.Print strEval
End Sub