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.
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 |
|
|
|
https://i.postimg.cc/prJFZt2s/1xA11.jpg
Attachment 5105
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
Attachment 5106
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:
Code:
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
Code:
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
Reply With Quote
Bookmarks