Solved

Excel VBA cell data types

Posted on 2007-03-28
3
378 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Learn how to PXE Boot both BIOS & UEFI machines with DHCP Policies and Custom Vendor Classes
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

726 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