Question mark in spreadsheet causes problems in Excel VBA

Question

When I compare the value of a cell that contains ? to a variable, it always returns true. Is there any way I can prevent this? Here is my current code:

'Option Explicit
Dim hws As Worksheet
Set hws = ActiveSheet
Dim rng As Range, rng2 As Range
Dim letters(2, 2)
alpha = Range("CipherTable").Value

For x = 1 To 7
  For y = 1 To 7
    If alpha(x, y) = rng.Cells(i, j + 1).Value Then
      letters(2, 1) = x
      letters(2, 2) = y
    End If
  Next y
Next x

alpha, by the way, looks like this:

A   B   C   D   E   F   G
H   I   J   K   L   M   N
O   P   Q   R   S   T   U
V   W   X   Y   Z   1   2
3   4   5   6   7   8   9
0   ;   :   '   "   .   ,
(   )   _   -   +   ?   !

This always returns A, which is in alpha(1,1). Come to think of it, since they each go to seven, I don't know why it don't come back with !. How can I get around this and make it return true only when it actually matches?

Such a comparison behaves as expected for VBA in Excel 2007. If you provide a bit more of your code ( definitions of letters and rng for example ) I can test the whole thing to see if it was a bug that they have fixed. Or you could look at the Microsoft Help to see if it is a known fault. — sheepez, Jan 07 '11 at 21:53

MikeD · Accepted Answer · 2011-01-11T09:01:36.277

As far as I understand you want to create a substitution algorithm. If there is no specific reason to use a two dimensional cipher table I would rather use a one dimensional approach like the following:

Function Cipher(Argument As String) As String
Dim Model As String
Dim Subst As String
Dim Idx As Integer
Dim MyPos As Integer

    Cipher = ""
    ' note double quotation mark within string
    Model = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890;:'"".,()_-+?!"
    Subst = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890;:'"".,()_-+?!"

    For Idx = 1 To Len(Argument)
        ' get position from Model
        MyPos = InStr(1, Model, UCase(Mid(Argument, Idx, 1)))
        ' return character from substitution pattern
        If MyPos <> 0 Then Cipher = Cipher & Mid(Subst, MyPos, 1)
    Next Idx

End Function

calling this function with

Sub Test()
    Debug.Print Cipher("The quick brown (?) fox 123 +-")
End Sub

results in THEQUICKBROWN(?)FOX123+- (because we don't allow blanks in Model or Subst)

Now change Subst to

Subst = "!?+-_)(,.""':;0987654321ZYXWVUTSRQPONMLKJIHGFEDCBA"

result is 4,_73.+'?6910GBF)9ZWVUCD

if you feed the above into the cipher function, you end up again with THEQUICKBROWN(?)FOX123+- as you would expect from a symetrical substitution.

By the way, I did it 2D because substitution is not hard to break using brute force. — Arlen Beiler, Jan 12 '11 at 22:26
Fine it has helped and thanks for accepting ... well - any plain substitution - even Vigenère - is rather easy to crack if you have enough crypted text, using a statistical approach. I can share functions for Caesar, Vigenère and Fence with you - I developed them for educational purpose. — MikeD, Jan 13 '11 at 08:49
Sure, that would be interesting, though I don't know how we would. Are they in Excel VBA? I also work with C#, but I sometimes reference the VB.NET libraries because they are so extensive. Nothing like good ol' vb in all it's forms. — Arlen Beiler, Jan 14 '11 at 18:44

rskar · Answer 2 · 2011-01-14T15:43:44.957

I tried the following, and got the expected result (it was able to find the question mark):

(1) Created CipherTable range in worksheet, as above;

(2) Created a function QM similar to code above;

(3) Entered a formula in the style of =QM(cell-ref).

It worked fine. Function QM:

Public Function QM(theChar)

    Dim CipherTable
    Dim x As Integer
    Dim y As Integer

    CipherTable = Range("CipherTable").Value

    For x = 1 To 7
        For y = 1 To 7
            If CipherTable(x, y) = theChar Then
                QM = "X" & x & "Y" & y
                Exit Function
            End If
        Next y
    Next x

    QM = ""

End Function

====

I also tried something more direct, and got the response expected:

Public Sub QM2()

    Dim questMark As Range
    Dim someChar As String
    Set questMark = Range("CipherTable").Cells(7, 6)

    someChar = "A"
    Debug.Print questMark = someChar

End Sub

Question mark in spreadsheet causes problems in Excel VBA

2 Answers2

Linked