Comparing strings (2000/XP)

Topic

New Reply

My company created an Add-In for Excel that consist of several functions that connect to a SQL Server DB and return financial data. In order to send these XLS files to others that do not have the Add-In the cells formula has to be replaced with the result value of that formula removing these custom functions from the worksheets. I have the code to make this all happen however I have hit an issue. Currently I use the VBA Extensability objects to create a csv string of these custom functions from the add-in. I then iterate through every cell that has a functtion and extract from the function all of the characters up to the first ‘(‘. These first n number of characters identify the function name. I then compare this to the csv string to see if there is a match. This works fine so long as the cell has either only 1 function of the first function in the cell if one of these custom functions.
What I am looking for is a way to take the entire function from a cell and compare it to the csv string for a possible match without having to check each value in the csv string 1 at a time via a loop.

Suggestions?

Thanks
Ed

Reply | Quote

Replies

First, I assume that just replacing all formula with their results is not acceptable.

The two ways that I can think of doing this both involve looping through all of the function names:

1- Use some code like this (this code has not been tested and may contain errors):

Public Sub FixFunctions()
Dim oSheet As Worksheet, oCell As Range
Dim I As Long

' define and array named strFuns and load the function names into the array

    For Each oSheet In Worksheets
        For Each oCell In oSheet.Cells.SpecialCells(xlCellTypeFormulas)
            For I = LBound(strFuns) To UBound(strFuns)
                If InStr(UCase(oCell.Formula), UCase(strFuns(I) & "(")) Then
                    oCell.Copy
                    oCell.PasteSpecial xlPasteValues
                    Exit For
                End If
            Next I
        Next oCell
End Sub

2- Use something like this code (also not tested):

Public Sub FixFunctions()
Dim oSheet As Worksheet, oCell As Range
Dim I As Long

' define and array named strFuns and load the function names into the array

    For Each oSheet In Worksheets
        For I = LBound(strFuns) To UBound(strFuns)
            Set oCell = oSheet.Cells.Find(what:=UCase(strFuns(I) & "("), LookIn:=xlFormulas)
            Do While Not oCell Is Nothing
                oCell.Copy
                oCell.PasteSpecial xlPasteValues
                Set oCell = oSheet.Cells.Find(what:=UCase(strFuns(I) & "("), LookIn:=xlFormulas)
            Loop
        Next I
    Next oSheet
End Sub

Reply | Quote

Why do you say that replacing formulas with their result values is not acceptable?

Reply | Quote

I was saying that I was assuming that replacing ALL formulas with their values, even if the formula does not contain one of these functions, is not acceptable. If that is acceptable, then just copying each entire sheet and doing a Paste Special / Values would be much faster than searching through the sheets for the formula.

Reply | Quote

Gotcha. You are correct. I only want to replace formulas that contain one or more of these custom functions. Thanks again.

Reply | Quote