Excel VBA cell data types

Posted on 2007-03-28
Medium Priority
Last Modified: 2010-04-13

I am writing an Excel VBA app wich does certain things based on the type of
content in a cell.
What is the best way to see if a cell contains text, numbers, dates etc
Question by:Murray Brown
LVL 85

Expert Comment

by:Rory Archibald
ID: 18807659
You could use TypeName(Activecell.value)

LVL 13

Accepted Solution

adraughn earned 2000 total points
ID: 18810156
I wrote code for this. It distinguishes between text, true formulas, formulas that contain values and numeric. This particular instance will color code the cells for you.

First the function:
Function TypeOfValue(cell As Range) As Long
    Dim i As Long
    Dim s As String
    Dim lReturn As Long
    If cell.HasFormula Then 'Does this cell contain a formula?
        'Yes, cell contains a formula return either 2 or 3

        'Assume 2, unless proven otherwise
        lReturn = 2

        'Scan text of cell one character at a time
        For i = 1 To Len(cell.Formula)
            'look at current character from string position i
            s = Mid(cell.Formula, i, 1)
     'Is current character a letter
            Select Case UCase(s)
                Case "A" To "Z"
                    'Yes, character is a letter, return 3
                    lReturn = 3

                    'Stop scanning, we've found a letter
                    Exit For
                Case Else
                    'No, character not a letter
            End Select
        Next i
        'No, cell does not contain a formula, return either 1 or 4

        If IsNumeric(cell.Text) Then
            'Return 1, if the cell contains a numeric value
            lReturn = 1
            'Return 4, if the cell does not contain a num
            lReturn = 4
        End If
    End If
  'return type of value
    TypeOfValue = lReturn
End Function
Then an example of how to use it: (may want to change the col & row
Sub FormatCellsByType()
    For rwIndex = 1 To 10
        For colIndex = 1 To 10
            ActiveSheet.Cells(rwIndex, colIndex).Select
            If Len(ActiveCell.Text) > 0 Then
                Select Case TypeOfValue(ActiveCell)
                    Case 1
                        'Whatever you want to do to values (Ex: 35)
                        Selection.Font.ColorIndex = 5  'Blue
                    Case 2
                        'Whatever you want to do with value formulas
                        Selection.Font.ColorIndex = 46 'Orange
                    Case 3
                        'Whatever you want to do with formulas (Ex:
                        Selection.Font.ColorIndex = 10 'Green
                    Case 4
 'Whatever you want to do with text (Ex: text)
                        Selection.Font.ColorIndex = 3 'Red
                End Select
            End If
          Next colIndex
    Next rwIndex
End Sub


Author Comment

by:Murray Brown
ID: 18818726
great. thanks very much

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Take advantage of one of the most useful technologies available - virtualization!
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question