Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Comparing strings (2000/XP)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing strings (2000/XP)

    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):

    <pre>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
    </pre>



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

    <pre>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
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Comparing strings (2000/XP)

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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing strings (2000/XP)

    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.
    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Comparing strings (2000/XP)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •