XL97: Cell.Formula ignores leading single qote? 2

[VBAjedi]

It is VERY important that I get this right:

If a cell contains the value 'some string and I set range object "c" to equal that cell, it appears that when I refer to c.formula it ignores/strips off the leading quote. Is this always true? For example:

Range("A1").value = "'some string"
Set c = Range("A1")
x = c.Formula

returns an x value of "some string" (without the leading quote). Will it always do this (across XL versions, etc)?

WHY (if you care):
I coded an add-in that encrypts/scrambles worksheet contents. Because the resulting cell contents are random characters, sometimes Excel tries to reinterpret my values as dates, etc. This is bad.

To prevent this, I am adding a single quote to the beginning of every encrypted cell value. I need to make sure that when I go to decrypt each cell value, referring to c.formula will strip that quote off for me EVERY time.

VBAjedi

 

[SkipVought]

VBA,

Why are you using the Formula property rather than the Value property?

Skip,

http://www.TheOfficeExperts.com

 

[VBAjedi]

Because I wanted to make sure I got exactly what was stored in the cell (which is a string made up of random ASCII characters between 31 and 127). Before I thought of padding my encrypted strings with a leading quote, there was always the possibility that the encrypted string would start with an "=" sign, and the cell value would be a "#Value" error.

I suppose I could reconsider going back to referencing .Value now - do you see an advantage to doing so?

VBAjedi

 

[RobBroekhuis]

I'm pretty sure the answer is yes - it always gets stripped off. Certainly it behaves the same in XL2000.
[a1]="'quotes?"
?[a1]
quotes?


Rob

 

[RobBroekhuis]

The advantage I see is not having to refer to any property, since .value is the default property. I'm a stickler for writing code as short as possible...

Rob

 

[VBAjedi]

Rob,

I know you are (and I've learned much because of it!). With the potential for this app to royally munch sensitive data, I'm not assuming ANYTHING (even default properties) if I can help it! This thing basically takes every value and formula in a range and turns them into mush based on an encryption phrase supplied by the user. To decrypt it, they have one shot to enter the correct case/space-sensitive phrase (well, if they don't save changes they can close and reopen the workbook to try again).

VBAjedi

 

[finanl]

I know this may not be the cleanest way to do this but you could add the ' to the front.

Range("A1&quot.value = "'some string"
Set c = Range("A1&quot
x = "'" & c

Not the most favorable way but will work

Rich

 

[VBAjedi]

Thanks, Rich. I'm actually already doing a variation on that (just adding the quote to the string before I write it to the cell). That part of the process is working smoothly.

What threw me for a loop was discovering that when you reference the .Formula property of cell whose contents start with a single-quote, the string that is returned is without the quote.

All is well now that I am reasonably sure I can count on Excel to do that consistently. It just means I don't have to run code that strips off the leading quote before decrypting the cell's contents.



VBAjedi

 

[TheBitDoctor]

VBAjedi:

The single quote mark is a remnant of Lotus 123 formatting. It's interpreted as left aligned text. Search Excel Help on a single quote and you'll see that the way Excel treats this mark is dependant on an option setting. The only way I know of to force any text to be acceptable is to prefix the string with an acceptable value and then strip it off whenever you reference it:

Range("A1&quot = "ZZZ" & MyHashCode

StoredHashCode = Right(Range("A1&quot,Len(Range("A1&quot -3)

A little slower but not dependant on outside forces to function without side effects.

Ron

 

[xlbo]

Ron - that is correct but the important bit is that it marks what is in the cell as TEXT - it is a text marker rather than a character in it's own right. AFAIK, it should never appear when you reference the cell. I believe the only way to reference it properly in a cell is to use ="'" so VBAJedi - I think you're ok with this

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[VBAjedi]

Well, I've modded my code and it passed the testing I did without any issues. . . will use this method until some creative user finds the one case that this doesn't work in and comes screaming 'cause their data got munched (in which case I will laugh evilly like Ratbert of Dilbert fame!)

[LOL]

Thanks to Rob for confirming my observation, and to Geoff for giving a little background on why it is a fairly safe assumption. Stars to both!



VBAjedi