Macro to isolate a 3rd party add-in group of functions.

[Risk]

I've got this nifty macro that I use to isolate a specific 3rd party add-in group of functions so that they can be valued without other stand XL functions being valued, but I'm very new to VB and I don't know how to make a modification to it. The limitation of this function is it doing a IF left 3 spaces "=HP" then value cell, but many times I have cells which contain the "HP" functions and they are not at the beginning of the code. e.g. =IF((HPVAL(X,X,X,X,X)>1),TRUE,FALSE)

In this example, the cell would not be copy paste valued since the left three spaces are "=IF" and not "=HP". The code I presently have is at the bottom of this message

....would the proper correction be


For Each z In Selection
If CONTAINS(z.FormulaR1C1) <> "HPLNK" Then
If CONTAINS(z.FormulaR1C1) = "HP" Then




Thanks for your help,

Risk



***** ORIGINAL ******

Sub ValueHPAll()
TxtMsg = "You have selected to value all Hyperion formula's in this workbook. If you wish to proceed please press OK"
y = MsgBox(TxtMsg, vbOKCancel, "Proceeding with valuing Hyperion formula's.")

If y = 1 Then

For Each x In Worksheets
Sheets(x.Name).Activate
Range("a1").Select
ActiveCell.SpecialCells(xlLastCell).Select
LastCell = ActiveCell.Address
Range("a1:" & LastCell).Select

For Each z In Selection
If Left(z.FormulaR1C1, 7) <> "=HPLNK" Then
If Left(z.FormulaR1C1, 3) = "=HP" Then
z.Copy
z.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
Next z
Range("a1").Select
Next x


Else
MsgBox "You have chosen to cancel this process"

End If

End Sub

 

[Andrew Poulsom]

Try:

If Instr(1, z.FormulaR1C1, "HP") > 0 Then

 

[Risk]

I know this might sound noob, but what does this do? I'm really trying to learn VB. Thanks,

If Instr(1, z.FormulaR1C1, "HP") > 0 Then

 

[Andrew Poulsom]

The InStr function returns the position of the first occurrence of one string within another (like Excel's FIND function). If it's greater than zero the string is found.

 

[Norie]

Try this.

Sub ValueHPAll()

    TxtMsg = "You have selected to value all Hyperion formula's in this workbook. If you wish to proceed please press OK"
    y = MsgBox(TxtMsg, vbOKCancel, "Proceeding with valuing Hyperion formula's.")
    
    If y = vbOK Then
        
        For Each x In Worksheets
            With x
                LastCell = .Cells.SpecialCells(xlLastCell).Address
                For Each z In .Range("a1:" & LastCell)
                    If InStr(UCase(z.FormulaR1C1), "HP") <> 0 Then
                        z.Value = z.Value
                    End If
                Next z
            End With
        Next x
    Else
        MsgBox "You have chosen to cancel this process"
    End If

End Sub

 

[Risk]

OK I've learned a lot so far today, but I've got one thing that was lost with the modification. The original code exempted HPLNK from pastevalues, but the new code pastevalues everything with HP.

Sub ValueHPAll()
TxtMsg = "You have selected to value all Hyperion formula's in this workbook. If you wish to proceed please press OK"
y = MsgBox(TxtMsg, vbOKCancel, "Proceeding with valuing Hyperion formula's.")

If y = 1 Then

For Each x In Worksheets
Sheets(x.Name).Activate
Range("a1").Select
ActiveCell.SpecialCells(xlLastCell).Select
LastCell = ActiveCell.Address
Range("a1:" & LastCell).Select

For Each z In Selection
If Left(z.FormulaR1C1, 7) <> "=HPLNK" Then
If InStr(1, z.FormulaR1C1, "HP") > 0 Then
z.Copy
z.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
Next z
Range("a1").Select
Next x


Else
MsgBox "You have chosen to cancel this process"

End If

End Sub

 

[Norie]

Well just change the logic a bit.

For Each z In .Range("a1:" & LastCell)
     If InStr(UCase(z.FormulaR1C1), "HP") <> 0 Then
        If InStr(UCase(z.FormulaR1C1), "HPLNK") = 0 Then
           z.Value = z.Value
        End If
     End If
Next z

 

[Andrew Poulsom]

Is it?

If InStr(1, z.FormulaR1C1, "HPLNK") = 0 Then
If InStr(1, z.FormulaR1C1, "HP") > 0 Then

 

[sbendbuckeye]

Finding HP but not HPLnk within function names

Hello,

Maybe this is overkill, but using the InStr function doesn't really protect you from some function with an HP embedded within its name (eg RhPass would be processed by the examples given above).

There are very interesting pattern recognition capabilities within Excel known as Regular Expressions. To use them, you need to set a reference to Microsoft VBScript Regular Expressions x.x. Use the highest version you have available to you.

The following function defines a pattern which requires at least one non Word character in front of the match - eg = or ). It then matches HP and at least one other Word character - eg HP fails but HPTest passes. It then checks explicitly for HPLnk since you mentioned that you needed to exclude that one specifically.

Function TestFormula() As Boolean
    Dim cell As Range
    ' Create a Regular Expression object
    With CreateObject("VBScript.RegExp")
        ' Set options for Regular Expression object
        .IgnoreCase = True
        .MultiLine = False
        
        ' Pattern to test
        ' ^ - Match beginning of string
        ' .* - Match anything 0 or more times
        ' \W{1} - Match a non word character exactly once
        ' hp - Match the letters hp exactly
        ' \w+ - Match additional word characters one or more times
        .Pattern = "^.*\W{1}hp\w+"
        For Each cell In Selection.Cells
            ' Only interested in formula cells
            If cell.HasFormula Then
                ' Does Regular Expression pattern defined above match within this cells contents
                If .Test(Trim$(cell.Value)) Then
                    ' Ensure HPLnk is not being processed
                    If InStr(LCase$(Trim$(cell.Value)), "hplnk") = 0 Then
                        TestFormula = True
                    Else
                        TestFormula = False
                    End If
                Else
                    ' Formula text does not match pattern defined above
                    TestFormula = False
                End If
            Else
                ' Non formula value
                TestFormula = False
            End If
        Next cell
    End With
    Set cell = Nothing
End Function 'TestFormula

If you are very new to VBA then don't worry about it if this goes over your head. I was programming a long time before I learned how to use Regular Expressions. Good LucK!

 

[Risk]

Yeah that was way over my head, but I've got that yearning inside to understand what it means. Could you expand upon the

"but using the InStr function doesn't really protect you from some function with an HP embedded within its name (eg RhPass would be processed by the examples given above). "

I'm not even sure what RHPass is. Wow, i can see i have a lot to learn.