Solved

Excel VBA cell data types

Posted on 2007-03-28
3
375 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:murbro
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 500 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:murbro
ID: 18818726
great. thanks very much
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

786 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