?
Solved

Excel VBA cell data types

Posted on 2007-03-28
3
Medium Priority
?
383 Views
Last Modified: 2010-04-13
Hi

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
0
Comment
Question by:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 85

Expert Comment

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

Regards,
Rory
0
 
LVL 13

Accepted Solution

by:
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
    Else
        '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
        Else
            '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
Index)
*********************************************************************
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
(Ex:=64+25)
                        Selection.Font.ColorIndex = 46 'Orange
                    Case 3
                        'Whatever you want to do with formulas (Ex:
=if)
                        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






0
 

Author Comment

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

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

764 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