Using Evaluate Method for string expansion

[jgross]

For Excel 2010 under Windows 7

I'd like to write a function that sums a range based on the formatting of the cell. I wrote this, which worked wonderfuly:

For Each cell In target
    If cell.Font.Italic = True Then
        runningTotal = runningTotal + cell.Value
    End If
Next

The function then returns the runningTotal variable. Then I tried to expand on this. I wanted to pass a code number to the function, and have it change the formatting criteria based on that code (similar to the SUBTOTAL function). Using a Select Block to parse the code number was simple, I didn't want to copy the same For Each loop into each case though. My thought was to pass a string with code to return the proper format criteria to the If block and then using something similar to BASH's eval statement to expand the string. However, looking over the help page on the Application.Evaluate method, it seems that will only expand Excel defined names. Is there a way to have VBA expand a string as a statement? Something like:

Dim searchTerm As String
searchTerm = "cell.Font.Italic"

For Each cell In target
     If {searchTerm} = True Then
         runningTotal = runningTotal + cell.Value
     End If
Next

Or perhaps this isn't the most efficient solution. Is there another way to store a set of properties in a variable and then pass them to an object? Thanks.

Jim

 

[snb_]

Sub M_snb()
  For Each cl In target
      runningTotal = runningTotal - (cl.Value*cl.font.italic)
   Next
end sub

 

[Norie]

Jim

If you are asking if you can use Evaluate to convert a string to usable code then I think the short answer is no.

It might be possible to do something with CallByName but to be honest I think that's unlikely.

PS Using formatting for criteria, eg font colour/style, isn't always a good idea.

 

[jgross]

I guess what I'm really asking, or what I should be asking, is how can I store the concept of a property (Italic, Bold, whatever) in a variable and then use that variable to access the matching property of a specific object. Something that would look like this:

Function SUMFORMAT(function_code As Long, target As Range)
    Dim cell As Range
    Dim searchTerm As Object
    Dim runningTotal As Variant
    runningTotal = 0

    Select Case function_code
        Case 1
            searchTerm = Font.Italic
        Case 2
            searchTerm = Font.Bold
    End Select

    For Each cell in target
        If cell(searchTerm) = TRUE Then
            runningTotal = runningTotal + cell.Value
        End If
    Next

Is something like this even possible?

Jim