xlf QandA series


Change argument value


QUESTION

I NEED A VBA PROCEDURE THAT CAN HELP ME TO CHANGE ONE OF THE ARGUMENTS IN AN EXISTING WS FUNCTION

For example I have arguments like =XXX(3,3,3), and need to change the last "3" into "1". Manually changing every function argument is an option but unfortunately I have 100s of them. [Sean, 29 July 2020]


Answer icon Answer


Let =XXX(3,3,3), from the question example, be =SUM(6,54,123) in the code 1 demonstration. The formula character count is shown in figure 1, with 14 characters in total.


character count
Fig 1: Example formula - character count by position

VBA :: xlfReplaceSpecificArgument


An argument is the value passed to the function. The code 1 macro replaces the value of a specific argument in a WS function. The example changes =SUM(6,54,123) to =SUM(6,54,321).



Code 1: Macro xlfReplaceSpecificArgument using InStr (left to right) - returns the position from the start of the string, or specified position 
Sub xlfReplaceSpecificArgument()
Dim Item As Range
Dim strFormula As String
Dim intComma1 As Integer, intComma2 As Integer
Dim i As Long

    For Each Item In Sheets("Demo").UsedRange
        If Item.HasFormula Then
            strFormula = Item.Formula
            If Left(strFormula, 5) = "=SUM(" Then
                intComma1 = VBA.InStr(1, String1:=strFormula, String2:=",")
                intComma2 = VBA.InStr(intComma1 + 1, String1:=strFormula, String2:=",")
                Item.Formula = Left(strFormula, intComma2) & "321)" ' replace 3rd argument
                i = i + 1
            End If
        End If
    Next Item

    Debug.Print i, "Formulas replaced"

End Sub

About Code 1

  1. Line 12: HasFormula property returns Variant TRUE if cell has formula
  2. Line 13: Formula property returns the formula as a String in A1-style notation, "=SUM(6,54,123)". The value is assigned to strFormula variable
  3. Line 15: Using the inStr function start at character 1, and search strFormula for the first occurrence of a comma ",". Returns 7, assign to intComma1.
  4. Line 16: Using the inStr function start at character 8 (7 + 1), and search strFormula for the first occurrence of a comma ",". Returns 10, assign to intComma2.
  5. Line 17:   Concatenate the 10 left characters from strFormula =SUM(6,54, and 321) to form =SUM(6,54,321), then assign formula property to Item object


Alternate code: Macro xlfReplaceSpecificArgument using InStrRev (right to left) - returns the position from the end of the string 
'...
            If Left(strFormula, 5) = "=SUM(" Then
                intComma = VBA.InStrRev(strFormula, ",")
                ' inStrRev (line 15) -- [Rev]erse (right to left)
                Item.Formula = Left(strFormula, intComma) & "321)" ' replace 3rd argument - after last comma
                i = i + 1
 '...

  1. Line 16: alternate Using the inStrRev function start at the end and search strFormula for the first occurrence of a comma ",". Returns 10, assign to intComma.

    • This example was developed in Excel 365 :: VBA 7.1
    • Published: 29 July 2020
    • Revised: Saturday 25th of February 2023 - 09:39 AM, [Australian Eastern Standard Time (EST)]