Why is my VB6 variant changing depending on when it is assigned?

Question

I am new to VB6 (and visual basic in any form) development and I've come across a problem. There may be lexical errors below because I formatted the code to be a little more readable inside of the post here, but the essence of the question remains. Here is the code

My problem is that the values displayed by the MsgBox calls (the second set of them, the ones that reference the variables scrWord and resWord) differ depending on when I assign to the variables scrWord and resWord. If I assign to the variables scrWord and resWord in the first location that is uncommented in the code shown above, then the message boxes at the bottom of the code will print either strings I am not interested in (for example, the first messageBox will produce an output looking like srcws:resColNum:#) or what appears to be garbage data. Note that this means it assigned a static string I used in the previous message box to the variable scrWord. This is NEVER the intended behavior and I have no idea how it is happening.

If, on the other hand, the assignment is done immediately before the message boxes, where the variable assignment is commented in the code above, then the variables print a different value that is somewhat more like what is expected, but not exactly (typically, the two variables are exactly the same or one is numeric when both are expected to be different alpha strings).

I am baffled by this problem and I believe it has something to do with the GetData calls returning a Variant data type.

Thank you, Evan

New code is posted below. Still doesn't work.

Dim srcWord As Variant
Dim resWord As Variant

Do While (BinsCol.GetData(grouprownum, 1) = binfromnum And nogroupfound = True)
    Dim groupmismatch As Boolean

    groupmismatch = False

    For j = 1 To FormHyst.GroupList.ListCount
                        
                        
        srcWord = sourceWS.Columns(j).GetData(i, 1)
        resWord = "hello, world"
                        

                        
        MsgBox ("srcws:" & srcWord & vbNewLine &_
                "resws:" & resWord & vbNewLine &_
                "test:" & (resWord <> srcWord))
    Next
Loop

In this new code, both srcWord and resWord display "hello, world".

I still cannot make sense of this behavior.

Answer 1

As you said you are new to VB6, here some general thoughts and advices.

As a first advice: never use Variants as long as you don't really need them. Use the concrete data type you would expect. At MSDN there's an explanation how Variants work internally. There are implicit conversions taking place when comparing Variants, so it's kind of gambling if you don't really know your data.

Even if your GetData function returns Variant, you can cast the retrun value to the specific data type in that sheet column. So, if your column holds only strings, cast it to string with CStr() and put it in a string variable. Same with numbers, into double or long variables.

You might also want to use the VarType function to determine the real type of the values stored in your Variants.

The following is more of a debugging workflow, but maybe it helps you track down your problem:

1. Enable Option Explicit to avoid undeclared/misspelled variable problems. (as already mentioned by DJ)

2. Produce debug outputs after each line to make sure you have the values you expect and not something else. If not possible with step through debugging or "Debug.Print", then maybe you could log to an external log file or use MsgBox .

3. Decouple your source from external component dependencies for testing. In your case, try to reproduce the problem while not reading the data from the external grid (for example set the variables to some concrete strings). Then you will know whether the problem is in your programming logic or in the data you have to process.

4. Always doublecheck that your external data source is really that one you expect and not some one else. (Happened to me often enough...) So check that what data your grid holds at (i,j) and if it matches the data you get.

One more point: String comparisons are case sensitive in VB6, as long as you don't put "Option Compare Text" at the top of your module/class/form. So "hello world" <> "Hello world".