Rob Bovey posted a bit of code to http://www.devx.com/tips/Tip/5676

Function szEncryptDecrypt(ByVal szData As String) As String
''' This key value can be changed to alter the
''' encryption, but it must be the same for both
''' encryption and decryption.
Const KEY_TEXT As String = "ScratchItYouFool"
''' The KEY_OFFSET is optional, and may be any
''' value 0-64.
''' Likewise, it needs to be the same coming/going.
Const KEY_OFFSET As Long = 38

Dim bytKey() As Byte
Dim bytData() As Byte
Dim lNum As Long
Dim szKey As String

For lNum = 1 To ((Len(szData) \ Len(KEY_TEXT)) + 1)
szKey = szKey & KEY_TEXT
Next lNum

bytKey = Left$(szKey, Len(szData))
bytData = szData

For lNum = LBound(bytData) To UBound(bytData)
If lNum Mod 2 Then
bytData(lNum) = bytData(lNum) Xor (bytKey(lNum) _
+ KEY_OFFSET)
Else
bytData(lNum) = bytData(lNum) Xor (bytKey(lNum) _
- KEY_OFFSET)
End If
Next lNum

szEncryptDecrypt = bytData
End Function


I'm finding that it generates an awful lot of overflow errors because the
value being returned to bytData(lNum) is not in a byte range. There seem to
be dozens of scenarios where varying the key and the key offset (seemingly
within the expected parameters) that bytData(lNum) Xor (bytKey(lNum) -
KEY_OFFSET) cannot return byte data OR that the final bytData cannot be
converted back to a string.

Am I missing something here?

Do I need to do some preliminary checks before setting the constants and
passing data into the function?

Regards

GPO

Hi GPO,

The general rule for modifying the obfuscation constants in this
function is that the KEY_OFFSET constant must be less than or equal to the
lowest ASCII character value in the KEY_TEXT string. So if KEY_TEXT =
"0123456789abcdefg" then KEY_OFFSET must be less than or equal to 48,
because that's the ASCII value of the character "0" in the KEY_TEXT string.


--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"GPO" wrote in message
...
Rob Bovey posted a bit of code to http://www.devx.com/tips/Tip/5676

Function szEncryptDecrypt(ByVal szData As String) As String
''' This key value can be changed to alter the
''' encryption, but it must be the same for both
''' encryption and decryption.
Const KEY_TEXT As String = "ScratchItYouFool"
''' The KEY_OFFSET is optional, and may be any
''' value 0-64.
''' Likewise, it needs to be the same coming/going.
Const KEY_OFFSET As Long = 38

Dim bytKey() As Byte
Dim bytData() As Byte
Dim lNum As Long
Dim szKey As String

For lNum = 1 To ((Len(szData) \ Len(KEY_TEXT)) + 1)
szKey = szKey & KEY_TEXT
Next lNum

bytKey = Left$(szKey, Len(szData))
bytData = szData

For lNum = LBound(bytData) To UBound(bytData)
If lNum Mod 2 Then
bytData(lNum) = bytData(lNum) Xor (bytKey(lNum) _
+ KEY_OFFSET)
Else
bytData(lNum) = bytData(lNum) Xor (bytKey(lNum) _
- KEY_OFFSET)
End If
Next lNum

szEncryptDecrypt = bytData
End Function


I'm finding that it generates an awful lot of overflow errors because the
value being returned to bytData(lNum) is not in a byte range. There seem
to
be dozens of scenarios where varying the key and the key offset (seemingly
within the expected parameters) that bytData(lNum) Xor (bytKey(lNum) -
KEY_OFFSET) cannot return byte data OR that the final bytData cannot be
converted back to a string.

Am I missing something here?

Do I need to do some preliminary checks before setting the constants and
passing data into the function?

Regards

GPO

By Golly that was quick! Thanks Rob.

"Rob Bovey" wrote:

Hi GPO,

The general rule for modifying the obfuscation constants in this
function is that the KEY_OFFSET constant must be less than or equal to the
lowest ASCII character value in the KEY_TEXT string. So if KEY_TEXT =
"0123456789abcdefg" then KEY_OFFSET must be less than or equal to 48,
because that's the ASCII value of the character "0" in the KEY_TEXT string.


--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"GPO" wrote in message
...
Rob Bovey posted a bit of code to http://www.devx.com/tips/Tip/5676

Function szEncryptDecrypt(ByVal szData As String) As String
''' This key value can be changed to alter the
''' encryption, but it must be the same for both
''' encryption and decryption.
Const KEY_TEXT As String = "ScratchItYouFool"
''' The KEY_OFFSET is optional, and may be any
''' value 0-64.
''' Likewise, it needs to be the same coming/going.
Const KEY_OFFSET As Long = 38

Dim bytKey() As Byte
Dim bytData() As Byte
Dim lNum As Long
Dim szKey As String

For lNum = 1 To ((Len(szData) \ Len(KEY_TEXT)) + 1)
szKey = szKey & KEY_TEXT
Next lNum

bytKey = Left$(szKey, Len(szData))
bytData = szData

For lNum = LBound(bytData) To UBound(bytData)
If lNum Mod 2 Then
bytData(lNum) = bytData(lNum) Xor (bytKey(lNum) _
+ KEY_OFFSET)
Else
bytData(lNum) = bytData(lNum) Xor (bytKey(lNum) _
- KEY_OFFSET)
End If
Next lNum

szEncryptDecrypt = bytData
End Function


I'm finding that it generates an awful lot of overflow errors because the
value being returned to bytData(lNum) is not in a byte range. There seem
to
be dozens of scenarios where varying the key and the key offset (seemingly
within the expected parameters) that bytData(lNum) Xor (bytKey(lNum) -
KEY_OFFSET) cannot return byte data OR that the final bytData cannot be
converted back to a string.

Am I missing something here?

Do I need to do some preliminary checks before setting the constants and
passing data into the function?

Regards

GPO

Hi again,

Even controlling for the prev issue, it also seems to fall over where the
offset is 0 and they key and data both start with the same char.

Cheers

GPO

"GPO" wrote in message
...
Hi again,

Even controlling for the prev issue, it also seems to fall over where the
offset is 0 and they key and data both start with the same char.

Hi GPO,

I'm not seeing this. Can you give me an example where it happens? I set
KEY_OFFSET to zero and used various identical strings for both KEY_TEXT and
the data without any problem.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Hi Rob.
I confirm that your fine UDF work properly, but not with the
KEY_OFFSET = 0
Fine in all the other situation i tried.

Try:
oggi piove ma forse domani sarà bel tempo; il futuro comunque è nelle
mani di Zeus
0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMN OPQRSTUVWXYZ
ScratchItYouFool

S.O = XP Office = XL2003

Regards
Eliano



On 25 Giu, 04:18, "Rob Bovey" wrote:
"GPO" wrote in message

...

Hi again,

Even controlling for the prev issue, it also seems to fall over where the
offset is 0 and they key and data both start with the same char.

Hi GPO,

* * I'm not seeing this. Can you give me an example where it happens? I set
KEY_OFFSET to zero and used various identical strings for both KEY_TEXT and
the data without any problem.

--
Rob Bovey, Excel MVP
Application Professionalshttp://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm

<<--------------
Try:
oggi piove ma forse domani sarà bel tempo; il futuro comunque è nelle mani di Zeus
0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMN OPQRSTUVWXYZ
ScratchItYouFool

S.O = XP Office = XL2003
------------------------

Hi Eliano,

This seems to work fine for me. I wonder if the problem has something to do with code page differences in different language versions of Windows? The test code that works correctly for me in US English Windows XP/Excel 2003 is shown below:

Sub Test()

Dim szTest As String

szTest = "oggi piove ma forse domani sarà bel tempo; il futuro comunque è nelle mani di Zeus"

szTest = szEncryptDecrypt(szTest)
Debug.Print szTest ''' Encrypted string
szTest = szEncryptDecrypt(szTest)
Debug.Print szTest ''' Decrypted string

End Sub

Function szEncryptDecrypt(ByVal szData As String) As String

Const KEY_TEXT As String = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLM NOPQRSTUVWXYZ"
Const KEY_OFFSET As Long = 0

Dim bytKey() As Byte
Dim bytData() As Byte
Dim lNum As Long
Dim szKey As String

For lNum = 1 To ((Len(szData) \ Len(KEY_TEXT)) + 1)
szKey = szKey & KEY_TEXT
Next lNum

bytKey = Left$(szKey, Len(szData))
bytData = szData

For lNum = LBound(bytData) To UBound(bytData)
If lNum Mod 2 Then
bytData(lNum) = bytData(lNum) Xor (bytKey(lNum) + KEY_OFFSET)
Else
bytData(lNum) = bytData(lNum) Xor (bytKey(lNum) - KEY_OFFSET)
End If
Next lNum

szEncryptDecrypt = bytData
End Function

------------------------------------------
Output in Immediate Window:
-------------------------------------------
_VUZE_XN\AD J 
Q
?VZ5'.4*}g!%j-99;=?q1<9 8&-<zØ\VXYSUXC F=

oggi piove ma forse domani sarà bel tempo; il futuro comunque è nelle mani di Zeus

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"eliano" wrote in message ...
Hi Rob.
I confirm that your fine UDF work properly, but not with the
KEY_OFFSET = 0
Fine in all the other situation i tried.

Try:
oggi piove ma forse domani sarà bel tempo; il futuro comunque è nelle
mani di Zeus
0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMN OPQRSTUVWXYZ
ScratchItYouFool

S.O = XP Office = XL2003

Regards
Eliano



On 25 Giu, 04:18, "Rob Bovey" wrote:
"GPO" wrote in message

...

Hi again,

Even controlling for the prev issue, it also seems to fall over where the
offset is 0 and they key and data both start with the same char.

Hi GPO,

I'm not seeing this. Can you give me an example where it happens? I set
KEY_OFFSET to zero and used various identical strings for both KEY_TEXT and
the data without any problem.

--
Rob Bovey, Excel MVP
Application Professionalshttp://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Developmenthttp://www.appspro.com/Books/Books.htm