Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)

[DocAElstein]

This is post https://www.excelfox.com/forum/showt...ll=1#post21923
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21923&viewfull=1#post21923
Page #58 https://www.excelfox.com/forum/showt...s-etc-)/page58
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)/page58

---

Some notes arising from discussions in this Thread
http://www.eileenslounge.com/viewtop...61085a6575decc

---

Edit Aug 2023: The general ideas are OK here, but we all overlooked, or maybe did not understand fully some basic stuff about how Range and range "values" in this basic code line work, Range2.Value = Range1.Value. Because of this some of the stuff in the final actual solution are redundant: - in particular, the solution of 1x and related stuff.
See
https://www.excelfox.com/forum/showt...el-Cell/page10
https://www.excelfox.com/forum/showt...el-Cell/page11
https://www.excelfox.com/forum/showt...el-Cell/page12

Number stored as text, alignment of numeric values in cells

This problem/feature pops up from time to time.
https://i.postimg.cc/GpwT3Mzj/Number-stored-as-text.jpg
Number_stored_as_text.JPG

I have seen it so far in these sort of places/ things, such as text box contents are strings by definition , …VBA enters text that is inherent to a TextBox. Every 'number' in a textBox is a string "1234". VBA has to convert those strings into long/integer, etc….

, and when pasting things out,
https://www.eileenslounge.com/viewto...269105#p269105 https://eileenslounge.com/viewtopic....272704#p272704
, but it’s not always consistent, - in the next example the 3 seems to go in as a number, but the 4 and 5 go in as …" Number stored as text"
https://i.postimg.cc/cCzwRfG1/Number...onsistancy.jpg

'   https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21923&viewfull=1#post21923
Sub Number_stored_as_text__alignment_of_numeric_values_in_cells()   '      https://i.postimg.cc/cCzwRfG1/Number-stored-as-text-inconsistancy.jpg
Dim Ex As String
 Let Ex = "3"
 Let ActiveSheet.Range("A10").Value = Ex ' Puts in as number
Dim Exs(1 To 2) As String: Let Exs(1) = "4": Exs(2) = "5"
 Let ActiveSheet.Range("A11:B11").Value = Exs()
End Sub

[DocAElstein]

Number stored as text, alignment of numeric values in cells
Evaluate Range correction

The problem from the last post lends itself nicely to the Evaluate Range ideas. As often, this idea is based on a simple mathematical formula, which is itself one of a few ways in Excel and VBA we have a few ways to coerce a text to a number:
In Excel and VBA we have a few ways to coerce or "Methods to convert", http://www.eileenslounge.com/viewtop...309140#p309140
One simple way is to multiply by 1.
Edit Aug 2023: The general ideas are OK here, but we all overlooked, or maybe did not understand fully some basic stuff about how Range and range "values" in this basic code line work, Range2.Value = Range1.Value. Because of this some of the stuff in the final actual solution are redundant: - in particular, the solution of 1x and related stuff.
See
https://www.excelfox.com/forum/showt...el-Cell/page10
https://www.excelfox.com/forum/showt...el-Cell/page11
https://www.excelfox.com/forum/showt...el-Cell/page12



As ever with these Evaluate Range solutions, we start with a single cell formula which works in Excel.
Take the screenshot example from the last post. In C11 this appears to work, =1*A11
_____ Workbook: Number stored as text, alignment of numeric values in cells.xls ( Using Excel 2013 32 bit )

Row\Col	A	B	C
10	3
11	4	5	=1*A11
12

Worksheet: Sheet1
https://i.postimg.cc/prJFZt2s/1xA11.jpg
1xA11.JPG
What that simple formula appears to do is to make the 4 in cell A11 be written in cell C11 a true number, and so we effectively get rid of that warning of the form , "Number stored as Text"

Now the Evaluate Range bit.
Evaluate Range ideas are often a neat alternative to looping in VBA
Staying with Excel initially. (**Note: This explanation may not suit so well for an Excel version with the Spill/Dynamic arrays stuff, (currently from about version 365).)
In Excel, like in a lot of computer stuff, what we look at, or get as output to see, is based on something
chucking out from left to right
,then returning to the left, ( sometimes referred to as "invisible" character like Carriage Return vbCr )
, and then going to a new line/row, ( sometimes referred to as "invisible" character like LineFeed vbLf )
, then starting again until we get a 2 dimensional picture we as humans understand. In some cases this means that similar calculations may be done by default over an extended range, but normally we don’t see that as in Excel we look at doing stuff in one of excel boxes/cells. Somehow Excel will be wired to show a single value in a cell in a typical formula such as our =1*A11
Whether by design or accident, right from the start of when Excel came about, we can get some interesting results if we replace a single cell reference by a multiple one.
For example, in C11, write =1*A11:B11 instead, so as to maybe see if we can somehow do our basic formula for cells A11 and B11 at the same time.
But, try that and it will error**. Never mind. Let’s keep experimenting.
https://i.postimg.cc/k5CmNLgp/1x-A11-B11.jpg
https://i.postimg.cc/zBXY6WWw/1xA11B11.jpg
(** For Excel 365 it may not error, and I prefer not to consider yet what is happening there)

Type 1 CSE entry
There may be some reason such as extra overheads for Excel to show the same calculation for both cells, resulting in perhaps a deliberate prevention of the thing working, but my guess is that it does work without too much effort. Often we talk about Type 1 CSE entry, or CSE entry in general, as a way to tell Excel that you would like to take advantage of the possibility to do the calculation for a range other than a single cell.
To get the first inclination about what I am talking about, do this: do the same thing again, in C11, write =1*A11:B11 , but don’t hit Enter yet. Instead, hold keys Ctrl+Shift and then hit Enter
We don’t error anymore. But we still only see one value. https://i.postimg.cc/m2v5xMCx/1x-A11-B11-CSE1.jpg
If you apply a bit of lateral thinning to what we said earlier about how computer things work , ( ……chucking out from left to right , returning to the left and gong to a new line/row, then starting again ) , a good guess might be that in our example the other calculated value might be somewhere close to the right. That takes us nicely on to the next section, and how that relates to our example

Type 2 CSE entry
CSE generally seems to tell Excel to do multiple cell calculations, at least that is what we suggested. (We talk sometimes about "array calculations", but this may be strictly in mathematical definitions a bit incorrect)
But we generally write things in a cell, and generally for simple formulas we would expect a result in a cell.

But let's try it a bit differently, bearing in mind our example. So, start again, but before you type =1*A11:B11, first select the two cells, C11 and D11. Then after you type that, do the CSE entry again, not a simple Enter
https://i.postimg.cc/m2w2rQN9/1x-A11-B11-CSE2.jpg
Bingo!
https://i.postimg.cc/m2RgRdMR/1x-A11-B11-CSE2-Bingo.jpg
1xA11B11 CSE2 Bingo.JPG


Evaluate Range Solution as alternative to VBA Looping
So far we have just considered an Excel solution. That background was necessary to understand the Evaluate Range type Solution as alternative to VBA Looping
In VBA, a conventional way to do multi cell calculations would be to Loop. It loops, in other words does a similar codung a few times. It would need to do it twice, https://i.postimg.cc/Wb8ZRJM7/Loop-i...mn-A-and-B.jpg , in our example:

Sub LoopItTwice()  '  https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21924&viewfull=1#post21924
Dim Nmbr As Long
    For Nmbr = 1 To 2 Step 1
     Let ActiveSheet.Cells.Item(12, Nmbr + 2).Value = ActiveSheet.Cells.Item(11, Nmbr).Value  '    Always uses row 12 ,   done twice, first  for  column 3 value = column 1 value    and  then   column 4 value = column 2
    Next Nmbr
End Sub

We have just discussed that the CSE stuff in Excel gives us a way to do it in one go. Let’s investigate if we can do something like that in VBA

Do Excel stuff in VBA
In VBA there is a Evaluate("__") function. In simple terms, to a first approximation, we can put Excel formulas inside the quotes, here
Evaluate("here")
, and the result is returned, similar to what would be returned in a cell with that formula in it.
Exactly what may be "returned" (chucked at us from Evaluate("__") ) , may be many things, depending on the exact formula, so it makes sense that the thing is wired to return a Variant type, so that can hold whatever actual type comes from the evaluation. So something like this sort of form would be done in VBA
Dim Var As Variant
_Let Var = Evaluate("=A11:B11*1")

If you put a Stop statement (or alternatively click in the left margin to get a brown circle which works the same), then run the following full macro, then while it is paused, (or before you start) , highlight Var , hold key Shift and then hit key F9, to put a watch on that variable, then you can see the results.
https://i.postimg.cc/vmPf13Cx/Put-a-watch-on-Var.jpg
https://i.postimg.cc/mDtM77GX/Put-a-watch-on-Var.jpg

Sub RangeEvaluate()
Dim Var As Variant
 Let Var = Evaluate("=A11:B11*1")

Stop   '     If you put a Stop statement (or alternatively click in the left margin to get a brown circle which works the same), then run the following full macro, then while it is paused, (or before you start) , highlight Var , hold key Shift  and then hit key F9, to put a watch on that variable, then you can see the results.
'https://i.postimg.cc/vmPf13Cx/Put-a-watch-on-Var.jpg
'https://i.postimg.cc/mDtM77GX/Put-a-watch-on-Var.jpg
End Sub

https://i.postimg.cc/9Mb931YQ/Put-a-watch-on-Var.jpg


Occasionally, the Evaluate(" ") does not "return" more than one result in formulas involving multi cell ranges, but this appears to be a separate issue to the need for CSE measures. Often we can do empirically derived tricks to overcome such problems.
But in many cases the Evaluate(" ") does returns the same results immediately that are only returned in a spreadsheet via the CSE way discussed. It would appear that principally the Evaluate(" ") is not restricted by the single cell , or need for CSE entry issues as in the spreadsheet. Perhaps this is because in use, Evaluate(" ") is not tied in some way to a particular spreadsheet format to show its output. There appears to be a lot of uncertainty ad empirically derived solutions and tricks in Evaluate Range

So, we have our result, or a way to get it chucked at us , "returned" , by using an Excel spreadsheet formula from within VBA
So what do we do with it/ how do we get it in a cell, or more specifically in our example, how do we get it in cells,
……. Next post

[DocAElstein]

From last post, to recap a bit……
Do Excel stuff in VBA
In VBA there is a Evaluate("__") function. In simple terms, to a first approximation, we can put Excel formulas inside the quotes, here
Evaluate("here")
, and the result is returned, similar to what would be returned in a cell with that formula in it.
Exactly what may be "returned" (chucked at us from Evaluate("__") ) , may be many things, depending on the exact formula, so it makes sense that the thing is wired to return a Variant type, so that can hold whatever actual type comes from the evaluation. So something like this sort of form would be done in VBA
Dim Var As Variant
_Let Var = Evaluate("=A11:B11*1")

If you put a Stop statement (or alternatively click in the left margin to get a brown circle which works the same), then run the following full macro, then while it is paused, (or before you start) , highlight Var , hold key Shift and then hit key F9, to put a watch on that variable, then you can see the results.
https://i.postimg.cc/vmPf13Cx/Put-a-watch-on-Var.jpg
https://i.postimg.cc/mDtM77GX/Put-a-watch-on-Var.jpg

Sub RangeEvaluate()
Dim Var As Variant
 Let Var = Evaluate("=A11:B11*1")

Stop   '     If you put a Stop statement (or alternatively click in the left margin to get a brown circle which works the same), then run the following full macro, then while it is paused, (or before you start) , highlight Var , hold key Shift  and then hit key F9, to put a watch on that variable, then you can see the results.
'https://i.postimg.cc/vmPf13Cx/Put-a-watch-on-Var.jpg
'https://i.postimg.cc/mDtM77GX/Put-a-watch-on-Var.jpg
End Sub

https://i.postimg.cc/9Mb931YQ/Put-a-watch-on-Var.jpg


So, we have our result, or a way to get it chucked at us , "returned" , by using an Excel spreadsheet formula from within VBA
So what do we do with it/ how do we get it in a cell, or more specifically in our example, how do we get it in cells


Simple application of Evaluate("=A11:B11*1")type solutions
( Put what is chucked at us, ("returned") into cells )
Conveniently, Excel VBA allows a “field” or array of values, in other words a “square” or grid full of values to be applied directly to a rectangular range, pseudo code examples:

Simple application of Evaluate("=A11:B11*1") type solutions
Conveniently, Excel VBA allows a “field” or array of values, in other words a “square” or grid full of values to be applied directly to a rectangular range, pseudo code examples:

Range(“A1:B2”) values = { a b
_________________--__________c d }

Range(“A1:C1”) values = { x y z }

Range(“D1:E1”) values = { 4, 5 }

The right hand side of that last equation could be the array or field of values which was returned by our example, Evaluate("=A11:B11*1") . An actual working syntax to do in VBA what we did via CSE techniques previously would be
Range("C11:D11").Value = Evaluate("=A11:B11*1")

VBA coding “ works backwards “ , doing the right hand side first, so if we wanted to replace the original range with the values without the Number stored as text thing, then this would work

Range("A11:B11").Value = Evaluate("=A11:B11*1")

At this point we note that this basic coding layout can be written in a few different forms. In particular, we have great flexibility on the right hand side by virtue of the fact that the Evaluate takes a string argument. We are free to build up strings in VBA as we choose. They can be built up from hard coded text, as we currently have, or we can also include VBA things which would be converted at run time to what they evaluate to. For example, Range("A11:B11").Address returns $A$11:$B$11 , which for our purposes, in spreadsheet syntax, as used in a spreadsheet cell, has the same effect as A11:B11. So that last code line could be written

Range("A11:B11").Value = Evaluate("=" & Range("A11:B11").Address & "*1")


The last two code lines are effectively replacing a range which might have the Number stored as text thing showing to a range without that showing. In other words, it removes the Number stored as text thing.
We are free to replace the range Range("A11:B11") with any range we choose.
_ It could be some variable defined as a range like this
Dim Rng As Range
Set Rng = Range("A11:B11")
Let Rng.Value = Evaluate("=" & Rng.Address & "*1")
_ Similarly, we could make some selection in the spreadsheet, and then run the macro on this range using
Let Selection.Value = Evaluate("=" & Selection.Address & "*1")
This last macro is often the most convenient, but bear in mind that it will replace the original range, and things done by VBA can’t be undone. Best would be to test first by pasting a copy in a spare range. For example, if you have data up to column F, then this would put the test range to the side of it.
Selection.Offset(0, 6).Value = Evaluate("=" & Selection.Address & "*1")
(As often in VBA, the Offset works on the top left, shifting the whole range with it. In the inner workings of Excel VBA, things are often anchored or referenced to the top left)

In a few posts time we some simple variations of the Evaluate range way to remove the Number stored as text thing issue, specifically we are making the last few coding version less susceptible to problems / more workable on different data types
But first we will recap and discuss a useful typical format / versions of our final coding for test and developments






https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[DocAElstein]

Number stored as text, alignment of numeric values in cells

Improved/ Extended Evaluate Range solution. Recap General Purpose solution
In a few posts time we will discusses extending the last solution to overcome a couple of problems that may occur with certain types of range data, specifically, if there may be empty cells or cells with real text in it. In this and the following 2 posts we will recap and come up with a general purpose solution to then go on and modify.

General all purpose version of our final Evaluate Range solution Range variable, Rng
Let us both standardise and recap on a good general purpose version of our Evaluate Range solution, with emphasis on learning and test and developing

Range variable, Rng
It’s probably more often than not a good idea to use a variable for our main data range. In technical terms we might re say that, as a variable for our main Range object. Using a variable for things is often a good idea, and particular a good idea for a range object, since Excel and Excel is all about boxes and manipulating them, and in VBA in particular, we have a lot of useful built in things like Methods, Properties, and the such, that we can use on them/ with them, in particular the range object since that is in particular to do with the box, and boxes structure that Excel is all about.
So for some test range, such as A1:F7,
https://i.postimg.cc/nctNH0WJ/Range-A1-F7.jpg
Range(A1 F7).JPG
, then somewhere towards the start of any macro we might have something like this
Dim Rng As Range
_Set Rng = Worksheets.Item("Sheet1").Range("A1:F7")
You can often get away without the Worksheets.Item("Sheet1"). , if you know what you are doing and are sure that Excel will guess correctly which worksheet you are wanting to refer to, but often in a final coding, Excel may guess wrong, so more fully referencing a range is a good habit to get into.
We may as well, from the start of our coding get fully into such good habits, and use a variable for the worksheet also
Dim Ws1 As Worksheet
_Set Ws1 = ThisWorkbook.Worksheets.Item("Sheet1")
Dim Rng As Range
_Set Rng = Ws1.Range("A1:F7")
This Rng is referring to the test data range……._

[DocAElstein]

General all purpose version of our final Evaluate Range solution Range variable, The left hand side, LHS, of a general purpose code line.
The left hand side, LHS, of a general purpose code line.
_....... This Rng is referring to the test data range.
We want to modify/ correct that, so up until now that was the thing on the LHS.
But, it is convenient in test and development not to replace the original range, but perhaps rather, if you have space, put the output from the coding calculations somewhere alongside. That way if something goes wrong, you don’t have to try and find the original data in order to try again. For example this would be the LHS to place the result alongside to the right. _Let Rng.Offset(0, Rng.Columns.Count).Value =

That is a good choice to use as we are sure to place to the right, since we offset the output a number of column equal to the number of columns that the range has. Personally, I might do this
_Let Rng.Offset(0, Rng.Columns.Count + 1).Value =
, since an extra empty column in between can have later advantages.



















https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[DocAElstein]

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
Range(A1 F7) Address default Properties.JPG

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,

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

, we can clearly see that we have the correct syntax
https://i.postimg.cc/GpLGBTPQ/Debug-Print-str-Eval.jpg
Debug Print strEval.JPG

[DocAElstein]

Number stored as text, alignment of numeric values in cells

Improved/ Extended Evaluate Range solution.

Based on all the posts so far on this page, we have got this far,

Sub Number_stored_as_text()  '    https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=21967&viewfull=1#post21967
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 '  gives =1*$A$1:$F$7

 Let Rng.Offset(0, Rng.Columns.Count + 1).Value = Evaluate(strEval)
End Sub

This is our test data, https://i.postimg.cc/KvhPVN44/Test-Data.jpg
https://i.postimg.cc/8PkRNCBV/Test-Data.jpg
Test Data.JPG
_____ Workbook: Number stored as text, alignment of numeric values in cells.xls ( Using Excel 2013 32 bit )

Row\Col	A	B	C	D	E	F
1	Page	Letter	ordernr	Quant	Cost	Cost/quant
2	11	22	33	44	55
3	11	22	33	44	55
4	55	44	33	22	11	0,5
5	55	44	33	22	11	0,5
6	44	Y	23457	34	60,7	1,7568741
7	55	X	2234	34	160,7	4,60458453

Worksheet: Sheet1

This is the result after running the macro

_____ Workbook: Number stored as text, alignment of numeric values in cells.xls ( Using Excel 2013 32 bit )

Row\Col	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Page	Letter	ordernr	Quant	Cost	Cost/quant		#WERT!	#WERT!	#WERT!	#WERT!	#WERT!	#WERT!
2	11	22	33	44	55			11	22	33	44	55	0
3	11	22	33	44	55			11	22	33	44	55	0
4	55	44	33	22	11	0,5		55	44	33	22	11	0,5
5	55	44	33	22	11	0,5		55	44	33	22	11	0,5
6	44	Y	23457	34	60,7	1,7568741		44	#WERT!	23457	34	60,7	1,756874
7	55	X	2234	34	160,7	4,60458453		55	#WERT!	2234	34	160,7	4,604585

Worksheet: Sheet1



Oh bollox, a couple of problems
We see a couple of problems. An empty cell comes out as a 0 in the output, and text in any cell results in an error in the output.

In the next post, we look at overcoming these two problems

[DocAElstein]

Number stored as text, alignment of numeric values in cells

Improved/ Extended Evaluate Range solution.

Problem 1 text produces error in output.
Is it text or is it a number. Not as simple a question as you might think.
Initially a Layman might expect this problem, since multiplying a text by a number sounds dodgy. But we never know exactly how the makers of Excel program things, so we might just as well have thought it might return one of the text, or just recognise the text and so decide to do nothing but return it. But it appears that it errors.
So what to do.
The point of using these Evaluate Range ideas is to get a simple single code line that appears to do all in one go what more conventionally would be done in classic looping techniques. So initial we would usually see if we can solve the problem in the spreadsheet formula / expression.
We need something to help us distinguish between text and numbers, bearing in mind we have the complication of some numbers held as text. We want something that will distinguish between what we see as numbers and text, regardless of how Excel is holding anything we see as a number.
We have an ISTEXT and ISNUMBER function in Excel.
If we do some simple testing on the test data range we find that the ISTEXT will return true for our cells with a number stored as text, but not for cells with a simple number. So that looks perhaps less useful to us. Initially I did not expect the ISNUMBER to be useful either, since the Microsoft documentation say something like ISNUMBER("19") will return False.
Initial experiments confirm this. Take a small sample of our test range, A6:B7
https://i.postimg.cc/jdBJLVDM/Range-A6-B7.jpg
Range A6 B7.JPG

[DocAElstein]

Stop
Time to think again, before going further
Aug 2023: The general ideas were/ are OK here so far, but we all overlooked, or maybe did not understand fully some basic stuff about how Range and range "values" in this basic code line work, Range2.Value = Range1.Value. Because of this some of the stuff in the final actual solution are redundant: - in particular, the solution of 1x and related stuff.
See
https://www.excelfox.com/forum/showt...el-Cell/page10
https://www.excelfox.com/forum/showt...el-Cell/page11
https://www.excelfox.com/forum/showt...el-Cell/page12










https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=jdPeMPT98QU
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[DocAElstein]

This post https://www.excelfox.com/forum/showt...age2#post21998 Post #15

---

Some notes on support of these Threads and posts
https://eileenslounge.com/viewtopic....309151#p309151
https://www.excelfox.com/forum/showt...ll=1#post22078

Why did we Stop after the last few posts
We need to start again with the Evaluate(" "), that is to say the RHS _____ = Evaluate(" ") thing , and consider at the same time the RHS range thing _____ = Range(" ")

_ RHS _____ = Evaluate(" "), and RHS range object assignment thing _____ = Range(" ")
There seems to be some similarity in things they do
What is Evaluate(" "), with emphasis with what goes on in a spreadsheet cell
(Or what is going on in a cell after typing = ). I think maybe that when in a [cell] you write = , it can be considered as similar to this pseudo coding in VBA
[write in Cell] = Evaluate("What you put in the cell after the = ")
In simple terms, to a first approximation, Evaluate(" ") allows you to do in VBA within the " " what you do after the = in a cell.
([color=grey]So perhaps its understandable that
_ you can omit the = within the " " in Evaluate(" "), although it is accepted syntaxly: Perhaps it is then just redundant* since Evaluate is about doing what is after the = in an Excel cell
_ You can’t just put simple text in evaluate, as you can with a cell, you need to do it as
___ ' 1a)(i) you can additionally give text in a cell, as part of a formula, ( Example In cell ="Text in Formula in Excel"
___ ' 1a)(ii) or carefully tell the Evaluate(" ") to take/Evaluate a literal string, rather than Evaluate the string as if it were something after a = in a cell, which is what Evaluate is about*
Range objects in spreadsheets and Excel worksheet functions returning a range object. )
When we use cells in a spreadsheet, we may appear to be using simple text, numbers and formulas. Often we may have a formula which finally gives us a number. In some cases we may indeed get a simple number result. Within a formula, cell references may appear to just get the number or text from a cell, but in fact Excel manipulates range objects. Once you have some basic VBA knowledge, this will perhaps make sense, especially if you are aware that it is something similar to VBA that makes what you see work: Think of Excel spreadsheets as lots of functions that trigger when you change things in cells.
Bearing this in mind, it is perhaps reasonable to expect that things like Evaluate("=A1") , Evaluate("A1:B2") etc. in VBA might return a range object. Indeed they do!
You must make sure to do a Set them to a range object on the LHS,
Set Rng = Evaluate("=A1")
If you Let them to a Variant variable, (or for the case of a single cell, any suiting variable type), then VBA seems to be written to oblige and give an array of values (or a value for a single cell)

 Sub RangeEvaluate()  ' https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=22078&viewfull=1#post22078
Rem 0
Dim Ws As Worksheet
 Set Ws = ThisWorkbook.Worksheets("RangeValuevalues")
 
Ws.Range("B1").Clear
Rem 1 write in Cell, a value and something else - I want to do this cell writing from VBA   ="Text in Formula in Excel"
' 1a)(i)Excel value is =      "     Text in Formula in Excel     "     in other words a formula
 Let Ws.Range("B1") = "=" & """" & "Text in Formula in Excel" & """"  ' This is like manually writing  ="Text in Formula in Excel"  in a cell, a formula     or like saying is to be evaluated  "Text in Formula in Excel"
                     Ws.Range("B1") = "=""Text in Formula in Excel""" ' simplid Alternative - take out all  " & "  since in VBA they stop text" and lin& to "start of another text
Ws.Range("B1").Clear  '       is      "     =     "      "      Text in Formula in Excel     "       "     "
Let Ws.Range("B1") = Evaluate("=" & """" & "=" & """" & """" & "Text in Formula in Excel" & """" & """" & """"): Debug.Print "=" & """" & "=" & """" & """" & "Text in Formula in Excel" & """" & """" & """"  '   It is always worth doing this to see what text  Evaliate("  ")  actually sees inside the  "  " , in this case it gives in  Immediate Window    ="=""Text in Formula in Excel"""
    Ws.Range("B1") = Evaluate("=""=""""Text in Formula in Excel""""""") ' Alternative     https://i.postimg.cc/W3n3Q2ST/Remove-some-in-VBA-Quote-Ambersand-Quote.jpg
' 1a)(ii) use Evaluate to put simple text in a cell, sort of, it actually does an evaluation of the literal string, perhaps a combination of the  =   and then the final  carriage return
Ws.Range("B1").Clear:                                    Debug.Print "=""Text in Formula in Excel"""         '   It is always worth doing this to see what text  Evaliate("  ")  actually sees inside the  "  "    It gives in this case  ="Text in Formula in Excel"   But this is not quite the equivalent of what you would write in a cell, since  Excel would not try to evaluate   and error instead https://i.postimg.cc/htQPr8dL/Wtire-Text-in-Formula-in-Excel-in-a-Cell-and-it-will-error.jpg
                      Ws.Range("B1").Clear:  Let Ws.Range("B1") = Evaluate("=""Text in Formula in Excel"""): Debug.Print "=""Text in Formula in Excel""" ' gives ="Text in Formula in Excel"    or   try     ? "=""Text in Formula in Excel"""   ="Text in Formula in Excel"      This is extra to  Evaluate(" ")  - it is attempting to evaluate a string of  Text in Formula in Excel, like  =Text in Formula in Excel  and it recognises that as a text, so puts that in the cell     Watch :   : Evaluate("=""Text in Formula in Excel""") : "Text in Formula in Excel" : Variant/String : Tabelle6.RangeEvaluate

'1b) Something Else
In the first cell, A1, we have a number held as text. The range object seems to know this, and we find that if we try to write that in another cell, ( Let LHS = ) then we don’t get an error, but rather the text is put in. This is a strange thing, and its discussed in more detail somewhere else, ( Post is #21995 Page2 #12 https://www.excelfox.com/forum/showt...age2#Post21995 ) , but important for our considerations here is that it is a range object phenomena, so in '1b) we compare what happens for the RHS of Range(" ") or Evaluate(" ").
The result is the same.
In fact, not known to many people, some Excel functions also return range objects, ( https://eileenslounge.com/viewtopic....280942#p280942
https://www.myonlinetraininghub.com/...#comment-84892 February 20, 2021 at 3:18 am ). and we find that the behaviour in what happens for the RHS of Range(" ") or Evaluate(" ") is consistent for this as well, it’s the same again

 '1b) Something Else    https://www.youtube.com/watch?v=Td3r55oyiu0
Ws.Range("A2").Clear
 Let Ws.Range("A2") = Ws.Range("=IF(1,A1)")    ' Watch : + : Ws.Range("=IF(1,A1)") : "44" : Object/Range : Tabelle6.RangeEvaluate
Ws.Range("A2").Clear
 Let Ws.Range("A2") = Ws.Evaluate("=IF(1,A1)") ' Watch : + :  Ws.Evaluate("=IF(1,A1)") : "44" : Variant/Object/Range : Tabelle6.RangeEvaluate

So far so good.

What’s all this got to do with my Evaluate range one liner answer here: https://eileenslounge.com/viewtopic....309137#p309137
Well, I was trying to get a number held as text to change to a number, (to get rid of the little warning and the alignment difference to that for a number). My solution was based on pseudo code of CellValue x 1, as this is a typical way in Excel, or computing generally, to get a text that looks like a number to become a true number.
It worked.
But then when I looked at this in more detail, I thought it shouldn’t
https://www.excelfox.com/forum/showt...el-Cell/page10
https://www.excelfox.com/forum/showt...el-Cell/page11
, and then I went right back to re learn about range objects in particular with reference to the typical code line of
Range2.Value = Range1.Value
https://www.excelfox.com/forum/showt...el-Cell/page12


I know enough now to start again.
https://www.excelfox.com/forum/showt...ll=1#post23185
https://www.excelfox.com/forum/showt...ll=1#post23186
https://www.excelfox.com/forum/showt...ll=1#post23189