Excel VBA - Differences between 'Text' and 'Value'

Following the publication of 'What is a Cell Map?', I was playing with the functionality and found some unexpected mapping presentations.

On one worksheet the mapping delivered a block of constants - all coloured purple.

Constants are coloured purple.

But on the worksheet in question the software delivered the following.

Yellow represents user entry cells

But I knew that the original spreadsheet (from which the map was derived) contained numbers in these cells. In fact they were a lookup table. The user should not have been allowed any where near them.

On monitoring the code I found that the yellow cells were deemed to be empty, even though they clearly contained a number - in this case 7.

Monitoring code

The code in question used the expression MyCell.Text. This did not recognise the 7 as a text value.

Changing the expression to MyCell.Value brought about the desired effect. Although I have acheived the 'correct' result, testing has shown that MyCell.Text delivers the 'correct' result in other, almost identical circumstances. I'm still not sure why.

Comments

The Blood Sugar Diet: A Control Approach

Here is a quote from ‘ Get the Gloss ’ , High blood sugar levels - millions of us have it, but many of us don’t know it and it’s led to not just a type 2 diabetes epidemic, but also a rise in prediabetes , the condition that precedes it. Could calorie restriction help? It worked for medical journalist, Dr Michael Mosley and in his book, The 8-Week Blood Sugar Diet , he shares the science behind it . This website offers a specific diet which, it seems, you should repeat for each day of the 8 weeks. That is one approach but I would get bored very quickly. I guess my likelihood of success under these circumstances would be small. My wife and I wanted controlled variety, but for that we needed to know the calorie content of a wide variety of foods and to measure them out for each meal. I also needed “proof” that I had adhered to the regime throughout the 8 weeks. Hopefully, it would demonstrate how I was moving away from the type 2 diabetes risk zone. Th...

What is a Cell Map?

It is a simplified, colour coded version of an Excel Worksheet. The map shows the structure of the worksheet, its input cells, constants, primary formula and 'named' ranges. The map is designed for spreadsheet developers who work in isolation (i.e. not part of a team), but who, nevertheless, build complex spreadsheet systems. It highlights oddities and inconsistencies within a spreadsheet structure. Excel has its own built-in auditing and mapping tools. Excel's name manager and formula auditing tools These are useful for auditing one formula or one name, but they become overburdened with detail when trying to audit/review a whole worksheet. The ACBA Mapping [A revised version of the software is now available. Stephen Allen 05 September 2023] utility generates a map of the original spreadsheet and three map keys to aid the interpretation of the detail. For example, I went on strict weight reducing diet last year where I measured the weight/volume of everythin...

Cell Maps - Critical Comments

Cell maps are intended as tools for reviewing spreadsheets. If you spot an error or an inconsistency in the cell map this should be recorded and, if practicable, corrected. The cell mapping software provides a method for recording a reviewer's comments. All comments are linked to a specific map (or data table), The comments for a workbook under review are collated in a single worksheet. In this case, there's just one comment. The comment above refers to the area highlighted towards the bottom of the map below and are hyperlinked.to the specific area of the map through the column headed 'Map/Source Link'. The top left cell the source range provides a link back to the 'Comments. worksheet'. The yellow cells suggest that users should be entering information for collection by a formula, but which one. At the this point I went back to the orginal worksheet and traced the dependents for some of these 'so called' user entry cells - also coloured yellow as it ...

Logic, computing and people

Search This Blog