Excel Validation Error HRESULT:0x800A03EC when automated from Vb.net

Question

Any Solution for Error occured from Excel Validation 0x800A03EC when automated via vb.net.

Note: The same code runs in 90% of the systems & the same fails in rest.

Below is the code which i have used:

For K = 2 To 1000
    objxl.Range("F" & K).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2]&RC[-2],C[13]:C[14],2,0),"""")"
    Next
    i = 25
    objxl.Range("D2:D1000").Select()
    If lastrow_main = 2 Then
        lastrow_main = lastrow_main
    Else
        lastrow_main = lastrow_main - 1
    End If
    With objxl.Selection.validation
        .Delete()
        .Add(Type:=Excel.XlDVType.xlValidateList, AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, Operator:= _
        Excel.XlFormatConditionOperator.xlBetween, Formula1:="=$X$2:$X$" & lastrow_main & "")
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

There are a number of reasons this error is returned from Microsoft Excel -- the most common is when the user attempts to write data larger than Excel can handle. For example, if you try to write a string longer than 1024 characters to a cell in Excel — VB.NET LEARNER, Jul 25 '13 at 06:13
Welcome to [so]. I bet you're doing it wrong, see here how I got a bountry for helping fix the same error message: http://stackoverflow.com/questions/13908505/how-to-add-number-validation-in-ms-excel-cell-using-c-sharp - pay attention to what values are in the `Formula1:="=$X$2:$X$" & lastrow_main`. Since its intermittent you'll probably have to log out the value so you can reproduce it. — Jeremy Thompson, Jul 25 '13 at 06:16
Just a friendly heads up, [so] is a very active site and you should hang around for a bit straight after asking. Good luck :) — Jeremy Thompson, Jul 25 '13 at 06:40

score 0 · Answer 1 · answered Jul 25 '13 at 06:19

0

See here:

Excel.XlFormatConditionOperator.xlBetween, Formula1:="=$X$2:$X$" & lastrow_mail

=$X$2:$X$" & lastrow_mail has to be a delimited string of values. eg 1 To 10:

Formula1:="1,2,3,4,5,6,7,8,9,10"

answered Jul 25 '13 at 06:19

Jeremy Thompson

61,933
36
195
321

score 0 · Answer 2 · answered Sep 12 '13 at 13:21

This is most likely related to the fact that some of his users are running Excel under a different language than English. I have experienced this exact same issue and have been searching for an answer.

It appears that Excel does not do automatic formula translation when using a custom formula with Validation.Add.

I have tried the suggestions here http://www.add-in-express.com/creating-addins-blog/2009/02/13/old-format-invalid-type-library/ which are also posted here http://msdn.microsoft.com/en-us/library/office/aa537159(v=office.11).aspx None of them work when using Validation.Add.

The only thing I have found to work is when you manually translate the function names yourself. Personally maintaining the translations for all the function names is not something I really want to do. I would much prefer to use Excel to translate them or just set them in English like "VSTO" is supposed to allow you to do.

Excel Validation Error HRESULT:0x800A03EC when automated from Vb.net

2 Answers2