Hi...
.. i have a feeling I will be looking once a year at building strings for Evaluate and other Excel and VBA things, and trying to relearn. It seems that the mastering of quotes in Excel and VBA is the key to the understanding. Once you master that, the VBA Evaluate Method seems to become very useful.
.. I think I need to go through at a Snail's Pace….Originally Posted by snb
. Maybe a small follow up here could be appropriate to share my thoughts. This Thread did set off my yearly thoughts again….( and to keep it relevant: see****** right at the end, for yet another 2 Codes to meet the OP's Post # 1 original request) ...
.......
. 1) Basics
. It helps at the outset, I feel to try and maintain a colour convention where possible to try and distinguish betweenExcel Spreadsheet andVBA "Worlds" as I call them..
.. ( For code lines here I will drop my irritating habit of explicitly referencing all Ranges so , In a normal module, an unqualified Range call equates to Application.Range, which in most cases is the ActiveSheet.Range. Or alternatively put the code in the sheet module of the sheet to which it relates, and that will work I think that an unqualified Range call equates to that sheetAnd I will mostly let Let out for snb_..)
....
. Fundamentally a quote seems to inform / prepare / warnExcel Spreadsheet and VBA that a string is coming.
Hence we have
Dim v As Variant
Let v = "3" ' Results in a Variant variable containing astring value "3"
Let v = 3 ' Results in a Variant variable containing a Long Number 3 ( actually an Integer ? )
...
. Coming to understanding the Evaluate, whichhas become a strange obsession with me. A very basic, common , but not reallycorrect IMO explanation is that Evaluate allows VBA to do whatever one can doby writing in some ( string ) of characters, numbers, mathematical operators, Functionsand the like within aExcelSpreadsheetcell.
. This leads to a syntax
= Evaluate("___________")
. often verbally given as Evaluate ( Stringexpression )
. where string expression is what one writes ina cell . At the outset i like to try and stay explicit, not missing bits outwhich VBA often allows you to do as a sort of recognised shorthand..
. So the classic example is
Excel Spreadsheet =A1 -------- VBA =Evaluate ( "= A1 " )
. I have a modified definition, and why not, ExcelandVBA seems so badly documented that no one really "knows" for sure anymore:what goes on Evaluate encompasses a lot of what Excel Spreadsheetand VBA [COLOR=#000000]is about and if it was not for the annoying 255character string limit[FONT="Verdana"][SIZE=3] ( . 4 ) ( IMPORTANT : that is the string it actuallysees
Reply With Quote
Bookmarks