VarType - how does it work?

[George]

Hi all,

I've put together a macro to tell me the variable type used in a range. The code works fine and does its job well, but I'm wondering how Excel decides what data type numbers are represented as?
An example, a range containing only single digit numbers have been stored as Double - is this just how excel handles numbers, or is there something hidden somewhere that can tell me what data type it should be?

For the record (in case it helps), this is the code:

    For ColCount = 1 To MaxCol                                          'cycle through all columns
 
        MaxVar = 0                                                      'restart MaxVar
 
        For RowCount = 2 To MaxRow                                      'cycle through all rows
         
            VarHolder = VarType(Cells(RowCount, ColCount))
         
            If VarHolder = 10 Then                                                            '10 is error - this way we can easily find cells that have any possible problem data and can contact the client
                Cells(RowCount, ColCount).Interior.ColorIndex = 3                              'fill red
            End If
         
         
            If VarHolder > MaxVar Then              'keep MavVar as the highest level of datatype
                MaxVar = VarHolder
            End If
             
        Next RowCount
     
        Sheets("FieldLayout").Activate
        Cells(ColCount + 3, 3).Value = VarTypeName(MaxVar)              'Module 6
     
        Cells(ColCount + 3, 3).Borders.LineStyle = xlContinuous
     
        Sheets("SourceData").Activate
    Next ColCount

 

[Smallman]

Hi George

You have only given us part of your procedure....

When programming the idea is not to let XL decide what data type should be assigned to a variable. The idea is for you to tell XL what data type each variable has.

OPTION EXPLICIT

Press Ctrl F11 - Tools Options and now check 'Require Variable Declaration'

Welcome aboard, now XL will never again decide the type of a particular variant. You are the Master of the ship, happy sailing skipper!!!

This is the best error trapper you can add to the top of each module. It will mean that you have to declare each variable right off the bat at the start of your procedure. It also means you now look like you know what you are doing if anyone who knows anything looks at your coding.

As for how XL decides what data type a variant is? I would imagine it is one of Bill's complex algorithms written in C++ buried deep in the pile.

Take care

Smallman

 

[George]

sorry Smallman, I don't think I was quite clear here - it's not checking the data type of variables (these have been defined outside of the code snippet I've provided), but the contents of cells in a worksheet.

The reason I'm doing this is to get a rough idea of what kind of data I have for importing in to a SQL database later. In the table sizes of xls sheets (65536 rows) just calling all numbers "double" isn't all that much of a problem (still really sloppy, but it's not going to end the world), but when you start talking about serious datasets the difference between integer and double really matters.

I suppose the bottom line question is "how does Excel decide what datatype to assign to a cell when it's left formatted as general"

 

[Smallman]

Sorry George I misunderstood your question.

I have had the same problem when exporting data from Access to Excel. I seem to recall you need to get the data type correct in your source file and then XL has a better chance of picking up the more appropriate data source. I have often had a problem with numbers formatted as text in the source database. XL doesn't have a chance when this is the case and you need to either get it right in the database or use code to change the data type.

Mmmmmm - I have not been a great deal of help on this one and I apologise.

Take care

Smallman

 

[George]

no worries dude. My suspicion is that excel automatically sets number cells to double so that then it doesn't need to worry about changing it if there are any calculations performed that make the value exceed the standard length for integer or single (or whatever the "smallest" real number option is).
If anyone knows a way of identifying what data type it really is that would be great - the only way I can think of doing it is putting in something like this in the row counting for loop:

if application.match(".",cell.value,false) then
  DotFinder = True
End if

then adding this in the column counting one:

if DataType = "Double" then
  if DotFinder = True then
    Use length to set DataType to "Single" or "Double"
  else
    use length to set DataType to "Integer", "Long" or "Double"
  End if
End if

If there's a cleverer way I'm all ears, as this really doesn't seem like an optimum solution.

 

[NARAYANK991]

Hi George ,

I am not really sure what you are looking for ; however , you might find this link useful :

http://msdn.microsoft.com/en-us/library/office/bb687869.aspx

I quote from it :

All worksheet numbers in Excel are stored as doubles so that it is not necessary (and in fact introduces a small conversion overhead) to declare add-in functions as exchanging integer types with Excel.

Narayan

 

[George]

Narayan, that's exactly what I wanted to know, thanks .