How to write raw string in Excel VBA?

3
votes

I need to write a formula in an Excel sheet via VBA, so I go through select range and apply formula, but my formula is too long and it contain lot of double quotes (") so to ignore double quotes (") I am adding two double quotes (")

Some time string write as per my desire or some time by mismatching double quotes (") string get changed and formula applied is not correct.

As in python we write r before string and it work as follows:

print(r'hello\'s Sam.')   

hello\'s Sam

but is there any way in Excel VBA to write such a raw string?

Formula is as below

=IF(NOT($E24=""),IF($Q24="0-10V(AI)","Direct (0-10V) = (0-100%)",IF($Q24="2-10V(AI)","Direct (2-10V) = (0-100%)",IF(OR($Q24="PT 1000",$Q24="NTC 20K"),"-50 to 150 Deg C","N/A"))),"")

And I apply it through VBA as follow

Sheet4.Range("R2:R50000").Formula = "=IF(NOT($E2=""" + """),IF($Q2=""" + "0-10V(AI)""" + ",""" + "Direct (0-10V) = (0-100%)""" + ",IF($Q2=""" + "2-10V(AI)""" + ",""" + "Direct (2-10V) = (0-100%)""" + ",IF(OR($Q2=""" + "PT 1000""" + ",$Q2=""" + "NTC 20K""" + "),""" + "-50 to 150 Deg C""" + ",""" + "N/A""" + "))),""" + """)"
3
vbaexcel
To write - not "wright" .... – marc_s
@John Coleman I what t apply this formula to sheet4 in column R(R2:R50000) formula is =IF(NOT($E24=""),IF($Q24="0-10V(AI)","Direct (0-10V) = (0-100%)",IF($Q24="2-10V(AI)","Direct (2-10V) = (0-100%)",IF(OR($Q24="PT 1000",$Q24="NTC 20K"),"-50 to 150 Deg C","N/A"))),"") – Dinesh Vilas Pawar
@John Coleman and I write in Excel VBA Sheet4.Range("R2:R50000").Formula = "=IF(NOT($E2=""" + """),IF($Q2=""" + "0-10V(AI)""" + ",""" + "Direct (0-10V) = (0-100%)""" + ",IF($Q2=""" + "2-10V(AI)""" + ",""" + "Direct (2-10V) = (0-100%)""" + ",IF(OR($Q2=""" + "PT 1000""" + ",$Q2=""" + "NTC 20K""" + "),""" + "-50 to 150 Deg C""" + ",""" + "N/A""" + "))),""" + """)" – Dinesh Vilas Pawar
What is the problem? The code seems to succeed in putting the formula in the cells with the quote marks where you want them. In what way is the formula that is inserted different from the formula that you want to insert? Please read minimal reproducible example. – John Coleman
@John Coleman Thanks for your reply my formla is succeed in VBA , but is single example I want to type like such formula in my entire code 70-80 Type so it difficult to take care of double quotes That's why I am looking for Raw string function in excel VBA. – Dinesh Vilas Pawar

3 Answers

2
votes

There is no notion of raw string in VBA, but you could write the formula using e.g. single quote marks rather than double quote marks and then replace them. You could even make a simple utility function to do so: 

Function r(s As String, Optional QuoteSymbol As String = "'") As String
    r = Replace(s, QuoteSymbol, """")
End Function

Then your formula could be inserted simply as:

Sheet4.Range("R2:R50000").Formula = r("=IF(NOT($E2=''),IF($Q2='0-10V(AI)','Direct (0-10V) = (0-100%)',IF($Q2='2-10V(AI)','Direct (2-10V) = (0-100%)',IF(OR($Q2='PT 1000',$Q2='NTC 20K'),'-50 to 150 Deg C','N/A'))),'')")

In the off-hand chance that you need to have single quote marks in the final formula then you could pass something like the back-tick ( ` ) to the optional parameter QuoteSymbol

Having said all that, you seem to be doing more work than needed in the sense that inside a string any two consecutive double quotes are replaced by just one double quote. You don't need all of that concatenation to build up the final string.

0
votes

Just add one quote(") as following example: Formula:

=text(now(),"mmm dd yyyy")

VBA:

Sub InsertTodaysDate() 
    ' This macro will put today's date in cell A1 on Sheet1 
    Sheets("Sheet1").Select 
    Range("A1").Select 
    Selection.Formula = "=text(now(),""mmm dd yyyy"")" 
    Selection.Columns.AutoFit 
End Sub
0
votes

As There is no notion of raw string in VBA, so this problem can solve with putting saw string in excel Cell and then use it in formula like

Sheet4.Range("R2:R50000").Formula = "=" & Cstr(Sheet10.Cells(1,2).Value)

next time only changing value in cell and formula get update