How to write raw string in Excel VBA?

Question

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""" + "))),""" + """)"

Answer 1

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.