Passing an Excel formula to a function

  • Thread starter Thread starter Bill Murphy
  • Start date Start date
B

Bill Murphy

I have a user defined subroutine where I create Excel spreadsheets,
total columns, format cells, etc. I need to pass a formula to the
subroutine to use when summing columns, as follows:

strFormula = """""=SUM(""" & " & rng.address & " & """)"""""
InsertFormulas strFilePath, strQueryToUse, 16, 8, strFormula

The resulting string variable in the subroutine looks the same as above,
except there is an extra pair of double quote marks surrounding it:

""=SUM(" & rng.address & ")""

What's the proper syntax for passing a parameter like this to get:

"=SUM(" & rng.address & ")" for use in the subroutine?

Bill
 
Hi Bill,

The reason there's an extra pair of quote marks in your result is that
you're putting them there yourself. Remove two of the five quote marks
at beginning and end of the strFormula assignment.
 
John,

I could not get this to work, even after following your suggestion
below. However, I came up with a workaround of passing the first and
last part of the formula as separate parameters, and letting the range
middle portion be supplied within the function, after rng has been
defined. Here are several partial code snippets to show how this works:

In the calling module:
_________________________

strFormulaLeftPart = "=SUM("
strFormulaRightPart = ")"
InsertFormulas strFilepath, strQueryToUse, 2, 16, _
strFormulaLeftPart, strFormulaRightPart, 1


In the subroutine:
_________________________

Sub InsertFormulas(strWorkBookName As String, _
strSheetName As String, _
intStartRow As Integer, intColumnToInsert As Integer, _
strFormulaLeftPart As String, _
strFormulaRightPart As String, intColumnOffset As Integer)

' insert a formula in the passed range

' passed variables:
' strWorkBookName = Excel workbook
' strSheetName = sheet in Excel workbook
' intColumnToInsert = column to contain the formula
' intStartRow = first row to contain the formula
' strFormulaLeftPart = the leftmost part of the formula
' strFormulaRightPart = the rightmost part of the formula
(mid part supplied in this sub)
' intColumnOffset = number of columns to right to offset to
begin range selection
__________________________

For i = intStartRow To intRowCount

Set rng = objSht.Range(objSht.Cells(i, intColumnToInsert _
+ intColumnOffset), objSht.Cells(i,intColumnToInsert _ +
intColumnOffset).End(xlToRight))

objSht.Cells(i, intColumnToInsert).Formula =
strFormulaLeftPart _ & rng.Address & strFormulaRightPart

Next
_________________

Bill
 
Back
Top