CODEWITHSUNDEEP
vbaexcel
Tim Couwelier
Tim Couwelier

Reputation: 123

VBA for Excel - building .Formula with contents containing quotation marks

I'm coding a project in VBA for Excel, which loops to a file, matches the 'code' for each of the quantities, and then feeds all the matches for that code to a user defined function, which goes onto the Excel sheet.

I can read the info, sort it so postdata(nr_of_datafield, nr_of_item) returns me the row in the source sheet on which the value is listed. Based on this, I need to create (through .Formula) a syntax like this:

=formul(raming!J104) (if there's only one occurence) =formul(raming!J104;"+";raming!J108) (etc., always adding the same extra if there's multiple occurences) =formul(raming!J104;"+";raming!J108;"+";raming!312;"+";raming!J403) etcetera, with always needing to get the previous values from what's already in Cells.Formula.

Based on this check:

Code:

Right(Workbooks(meetstaatfile).Sheets("HOR_raming").Cells(lusteller12, 9 + CInt(postdata(3, eerstepositie)) * 3).Formula, 2) = "()"

I can detect if there is already any contents added here. If not (meaning the check for () ending is positive), I replace with this:

Code:

Workbooks(meetstaatfile).Sheets("HOR_raming").Cells(lusteller12, 9 + postdata(3, eerstepositie) * 3).Formula = "=formul('raming'!J" & postdata(2, eerstepositie) & ")"

To create a formula that looks like: =formul(raming!J104)

(the '104' in this example is the output from postdata(2,eerstepositie)

However, if it doesn't trigger for the () ending, there already is a value, and I need to extend the formula to something like this: =formul(raming!J104;"+";raming!J108)

I've been trying to figure out how to do this by replacing ')' with the block I want added, but I cannot get it to work to input the quotation marks. ('formul' is very similar to concatenating text).

How can I make a variation of the codeline above that lets me alter the cell input? Either by a Replace() like I was trying, or reading what's between the formul() brackets and rebuilding the formula?

Upvotes: 2

Views: 202

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

If you need to have quotation marks as content within a string literal in VBA, you have to double them. See: http://msdn.microsoft.com/en-us/library/ms234766.aspx

.Formula = "=formul('raming'!J" & 104 & ",""+""," & "'raming'!J" & 108 & ")"

Or with your postdata:

.Formula = "=formul('raming'!J" & postdata(2, eerstepositie) & ",""+""," & "'raming'!J" & postdata(2, whatevergets108)  & ")"

I don't know, whether I have understood it right, but if you need to concatenate the formula in dependence of the contents of an array, then this can be achieved like so:

Sub test()
 'one occurrence
 postdata = [{0;104}]
 sFormulaString = getFormulaString(postdata, 2)
 MsgBox sFormulaString
 'two occurrences
 postdata = [{0,0;104,108}]
 sFormulaString = getFormulaString(postdata, 2)
 MsgBox sFormulaString
 'three occurrences
 postdata = [{0,0,0;104,108,312}]
 sFormulaString = getFormulaString(postdata, 2)
 MsgBox sFormulaString
End Sub

Function getFormulaString(postdata As Variant, nr_of_datafield As Long) As String
 sFormula = "=formul("
 For i = LBound(postdata, 2) To UBound(postdata, 2)
  sFormula = sFormula & "'raming'!J" & postdata(nr_of_datafield, i) & ",""+"","
 Next
 sFormula = Left(sFormula, Len(sFormula) - 5) & ")"
 getFormulaString = sFormula
End Function

Hm, or is the real need, to append new formula parts into an existing formula? If so, the following code will append a new part into the Formula in A1 every time it runs.

Sub test2()

 postdata = [{0;104}]

 sFormula = Range("A1").Formula
 If sFormula = "" Then sFormula = "=formul("
 If Right(sFormula, 1) = ")" Then sFormula = Left(sFormula, Len(sFormula) - 1) & ",""+"","
 sFormula = sFormula & "'raming'!J" & postdata(2, 1) & ")"
 Range("A1").Formula = sFormula

End Sub

Greetings

Axel

Upvotes: 0

Related Questions