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

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 ...

A Revised Cell Map!

In the Blog ' What is a Cell Map? ', I included an image of a Cell Map. On reflection the presentation was muddled. A simpler and clearer design seemed worthwhile. The result below is more clearly rectangular, with the headings for the map, the formula table and the names/validation table matched along the same row. The validation table is new and highlights where user inputs are limited by validation and the nature of the validation criteria. The revised software is available at ACBA-Mapping . In addition, although software is intended to be intuitive and self explanatory, it rarely is. I have created a user group at MSExcelCellMapping@groups.io . You are welcome to join and ask questions about the software. https://drive.google.com/file/d/1w3tHk1l-G9LjmCsOqMBkJpE9kaSj0iX_/view?usp=sharing Spreadsheet errors and inconsistensies is a subject that is of concern by professional developers as well as amateurs. The European Spreadsheet Risk Interest Group (EuSpRIG...

Loading an Excel Add-In

Microsoft offers a range of Add-Ins and other extensions to Excel’s normal functionality. These are accessed most easily from the ‘Developer’ ribbon. The Developer Tab Clicking the Add-Ins icon, offers the user a list of all Add-Ins currently available to your Excel program. The problem comes when your particular program isn’t listed there. Even when you know where the Add-In file is and double click it, the functionality is still not activated. According to SuperUser.com , the problem results from security patch in KB31152, released in July 2016. Apparently, Microsoft software engineers have said: "With this update, we changed the behaviour of Excel so that it will not load certain file types (including .xlam) when they are untrusted. The easiest workaround is to find the add-in that is causing you trouble, right-clicking on it in Windows Explorer, and checking Unblock" However, SuperUser.com recommend placing the add-in in a Trusted Location (in Excel, go to File ...

Logic, computing and people

Search This Blog