[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Excel VBA cell data types

Posted on 2007-03-28
3
Medium Priority
?
388 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

IF you are either unfamiliar with rootkits, or want to know more about them, read on ....
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

650 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