Formula from Excel into VBA Code

Question

Hello fellow programmers!

I am new to VBA and shall make big changes in an existing Excel template... Now my problem is, while trying to export a Formula from excel (it is supposed to work later on button click), that I am unable to use this simple step:

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

This does work but when inserting my sadly big formula

Sub InsertTodaysDate() 
    Sheets("Sheet1").Select 
    Range("A1").Select 
       Selection.FormulaLocal = "=WENN(LÄNGE('Step 2 - Add Contact Informatio'!A4)>100;""too many characters"";""
WENN('Step 2 - Add Contact Informatio'!A4="""";""Email is mandatory"";
WENN(ISTZAHL(FINDEN(""!"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""*"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN("":"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""="";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""`"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""\"";
'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""]"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""["";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""}"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""{"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""´"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""?"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";WENN(ISTZAHL(FINDEN("")"";
'Step 2 - Add Contact Informatio'!A4));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""("";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";WENN(ISTZAHL(FINDEN(""/"";'Step 2 - Add Contact Informatio'!A4));""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""&"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""%"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""$"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""§"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""~"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""“"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""^"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""°"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""<"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN("" "";'Step 2 - Add Contact Informatio'!A4));
""spaces are not allowed"";
WENN(ISTZAHL(FINDEN(""#"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN(""'"";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN("","";'Step 2 - Add Contact Informatio'!A4));
""special characters are not allowed"";
WENN(ISTZAHL(FINDEN("">"";'Step 2 - Add Contact Informatio'!A4));""special characters are not allowed"";
WENN(ISTFEHLER(D8);""error"";WENN(D8=FALSCH;""error"";""ok"")))))))))))))))))))))))))))))))))"
End Sub

I get an Syntax Error, I even tried escaping the "" with \"\" but that just worked for half of the formula. Does anybody know what the problem is?

The real question is why do you need such a long formula. There is undoubtedly a better way. Maybe start with a question like that over at **codereview.stackexchange** — CallumDA, Oct 06 '17 at 13:39
@CallumDA well because my boss says it, seems like it has worked before and they want to keep using it — Patrick.H, Oct 06 '17 at 13:41
I can understand that - shame your boss isn't open to better solutions though! — CallumDA, Oct 06 '17 at 13:42
@CallumDA CodeReview is for working code. If the OP tried pasting this question there it will voted down and closed. — Scott Craner, Oct 06 '17 at 13:44
@ScottCraner, I probably wasn't clear enough. Assuming the formula works on the worksheet, asking how to improve the worksheet formula would be acceptable on codereview -- as far as I know? — CallumDA, Oct 06 '17 at 13:46
That's one insane formula... literally insane and error prone. Still, you have your constraints... If you can confirm that the formula works in another cell, then can you use it as a reference rather than hard coding it? Maybe even on a separate hidden reference sheet?....`Selection.Formula = Worksheets("ReferenceSheet").Range("A1").Formula`... or if the value is the same regardless then just reference then value and don't even copy the formula. — u8it, Oct 06 '17 at 13:47
@ScottCraner I tried it but it still says compilation error: Syntax Error — Patrick.H, Oct 06 '17 at 13:48
@u8it sorry it is my first day with vba, do you mean I can save the formula from one of the cells by calling the code you suggested? — Patrick.H, Oct 06 '17 at 13:50
You can get the current formula in a cell using Range.Formula... so you can use that to return an existing working formula... the code I posted puts that directly in the selected cell — u8it, Oct 06 '17 at 13:51
I will give it a try tomorrow at work, thanks for your help! @u8it — Patrick.H, Oct 06 '17 at 13:54
@ScottCraner I tried your suggestion aswell, as you can see above in the edited question, but it remains as syntax error — Patrick.H, Oct 06 '17 at 13:54
Sure, but also, if that equation is not cell dependent... meaning the value is the same regardless of what cell it is in, then I would recommend not even copying the formula and just reference the value. You could do this by referencing a particular range OR a [named range](https://support.office.com/en-us/article/Define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64) which you could reference [like this](https://stackoverflow.com/a/17228394/3546415) or [between workbooks](https://stackoverflow.com/a/29151674/3546415) — u8it, Oct 06 '17 at 13:59
That being said, if you need to use this macro between workbooks and can't count on such a reference being available, then avoid it. The problem with references is that they introduce fragility, but that's a crazy equation and I'd hate to see it propagated more than it needs to be. The reference solution ensures that all uses of it are controlled by the same implementation, so they share all corrections and changes. The point is there's a trade-off in referencing but it might be worth it in this case. — u8it, Oct 06 '17 at 14:07
Another option that would help you reduce the instances of such a crazy formula would be to package it in a function called `TimeFormula`, for instance, and then return `TimeFormula=Application.Evaluate(...put actual formula here)`, which could be complimented by an option to return the working formula text or a reference. You could then use `Selection.Formula = "=TimeFormula"`... I know that doesn't really solve your problem right now but it could help reduce errors down the road and make it easier to improve this formula as needed. It would at least isolate it from your larger macro. — u8it, Oct 06 '17 at 14:17

barvobot · Answer 1 · 2017-10-06T17:57:09.857

I tried converting it from FormulaLocal to just Formula and put it into "regular" VBA and it seems to work on my end at least. I would have put each IF on a separate line but after 25 _'s it gets mad. (If I mistranslated any of the functions, let me know!)

Sub InsertTodaysDate()

Sheets("Sheet1").Select
Range("A1").Select

Selection.Formula = _
"=IF(LEN('Step 2 - Add Contact Informatio'!A4)>100," & """" & "too many characters" & """" & "," & _
"IF('Step 2 - Add Contact Informatio'!A4=" & """" & """" & "," & """" & "Email is mandatory" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "!" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "~*" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & ":" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "=" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "`" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "\" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "]" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "[" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "}" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "{" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "´" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "~?" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & ")" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "(" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "/" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "&" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "%" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "$" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "§" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "~~" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "“" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "^" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "°" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "<" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "#" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & "'" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & "," & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & "IF(NOT(ISERROR(SEARCH(" & """" & ">" & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "special characters are not allowed" & """" & "," & _
"IF(NOT(ISERROR(SEARCH(" & """" & " " & """" & ",'Step 2 - Add Contact Informatio'!A4)))," & """" & "spaces are not allowed" & """" & "," & _
"IF(ISERROR(D8)," & """" & "error" & """" & "," & _
"IF(D8=FALSE," & """" & "error" & """" & "," & """" & "ok" & """" & ")))))))))))))))))))))))))))))))))"

End Sub

Edit: I realized that ?, *, and ~ needed a ~ in front of them or they would be rendered as various wildcards and give false positives, plus I realized all the searches should have NOT in them as well. Should be good now.

score 0 · Accepted Answer · answered Oct 09 '17 at 09:43

0

I really appreciate your help guys, sadly none of them worked. But I was able to convince my boss to use a regex. Thanks again, @u8it your approach did work, but I do not know how to mark it as answer

answered Oct 09 '17 at 09:43

Patrick.H

535
3
6
21

Formula from Excel into VBA Code

2 Answers2