Excel VBA Formula1 compile error

Question

I continue to get a syntax error with the following code. It doesn't seem to like my formula, even though it works when I just paste it in the custom data validation.

Formula:

=IF(B3="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(B3,ROW(INDIRECT("15:"&LEN(B3))),15),"0123456789"&"abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))

VBA Code:

Public Sub Class_Initialize()
'Dim Range As String

With Range("e5").Validation
 .Add Type:=xlValidateCustom, _
 AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="=IF(B3="",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(B3,ROW(INDIRECT("15:"&LEN(B3))),15),"0123456789"&"abcdefghijklmnopqrstuvwxyz"))),FALSE,TRUE))"
 .InputTitle = "Integers"
 .ErrorTitle = "Integers"
 .InputMessage = "Enter an integer from five to ten"
 .ErrorMessage = "You must enter a number from five to ten"
End With

End Sub

"=IF(B3="""",TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(B3,ROW(INDIRECT(""15:""&LEN(B3))),15),""0123456789""&""abcdefghijklmnopqrstuvwxyz""))),FALSE,TRUE))" — dwirony
– dwirony, Commented Mar 23, 2018 at 19:12
Unfortunately when I do that I get a run time error 1004 - Application-defined or object-defined error. I also tried pasting that code in the cell and I get an error as well. — Daniel
– Daniel, Commented Mar 23, 2018 at 19:15
@Daniel The formula with double " wouldn't work in a normal cell in excel, this is just how you have to write it when insert a formula via VBA. — dwirony
– dwirony, Commented Mar 23, 2018 at 19:17

score 1 · Accepted Answer · 2018-03-23 19:45:00Z

1

Double up all quotes within a quoted string. You can also use text(,) as a substitute for any "" zero-length strings so you don't end up with """".

With Range("e5").Validation
     .delete
     .Add Type:=xlValidateCustom, _
     AlertStyle:=xlValidAlertStop, _
     Operator:=xlBetween, Formula1:="=IF(B3=text(,), TRUE,IF(ISERROR(SUMPRODUCT(SEARCH(MID(B3,ROW(INDIRECT(""15:""&LEN(B3))),15),""0123456789abcdefghijklmnopqrstuvwxyz""))),FALSE,TRUE))"
     .InputTitle = "Integers"
     .ErrorTitle = "Integers"
     .InputMessage = "Enter an integer from five to ten"
     .ErrorMessage = "You must enter a number from five to ten"
End With

edited Mar 23, 2018 at 19:45

answered Mar 23, 2018 at 19:15

user4039065

Sign up to request clarification or add additional context in comments.

6 Comments

Daniel Over a year ago

Just copied and pasted that code. Still same runtime error.

Daniel Over a year ago

@ScottCraner Can you paste the code and show me? I took my original code and pasted that piece and get the error still. Maybe a VBA setting?

Scott Craner Over a year ago

add .Delete to delete the validation that exists prior to adding.

user4039065 Over a year ago

@ScottCraner - Good point. There may be a residual data validation from previous attempts.

Scott Craner Over a year ago

@Daniel when this works for you make sure you mark it as correct, as it will remove it from the unanswered queue and reward Jeeped. You mark it as correct by clicking the check mark by the answer.

|

Collectives™ on Stack Overflow

Excel VBA Formula1 compile error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related