[RESOLVED] Excel Custon Validation Formula

[Semola]

Hi All,

I'm writing a xlsx file directly from vb.net
I have a problem when I try to add a cell custom validation formula with this istruction:

Sub AddValidationFormula(byval range as range)
   dim validationFormula as string = "=mod(C2,F2)=0"
   range.Validation.Add(XlDVType.xlValidateCustom, XlDVAlertStyle.xlValidAlertStop, validationFormula) 
End Sub

The validationFormula above indicated is an semplified formula that I have try to used to solve my problem.

When I run my code, it crashes with "exception from HRESULT: 0x800A03EC".
I have also tryed the italian Fornula ("=RESTO") but without result.

If I write the formula directly in the Excel file, I don't have any problem.

I have attached the stackTrace error

Can anyone help me?

Thanks

Marcello

[ChrisE]

add a semicolon in your Formula not a comma

in Germany it would look like this

=REST(12;2)=0

or

=REST(-3;2)

would return 1

[Semola]

I have tryed using also the semicolon, but I have the same error.

I also tryed a formula using values instead of cells, but the error is always the same

I'm in dubt if I need to specify the formula (only for validation) in italian (RESTO) or english (MOD).

Using the formula without the final "=0" in a single cell formula, I don't have any problem (in this case I have used the italian formula and the FormulaLocal property)

Marcello

[ChrisE]

well I tried this this and it worked

 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Try
            Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
            Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
            xlWb = xlApp.Workbooks.Open("D:\TestFolder\vbexcel.xlsx")
            Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle3"), Worksheet)

            With xlSt
                .Range("G22").FormulaLocal = "=REST(C2;F2)=0"
            End With
            xlWb.Save()
            xlApp.Quit()
            xlApp = Nothing
        Catch g As Exception
            MessageBox.Show(g.ToString)
        End Try
    End Sub

[Semola]

Hi ChrisE,

with the code that you propose, you set a formula in a single cell (in the example, in the cell G22).

I need to add a validation formula (range.validation.add) to this cell, like the Data Validation inserted from Excel using menu data --> data validation.

In another cell, i Have set a data validation data, using the XlDVType.xlValidateTextLength and I don't have any problem.

Thanks.

Marcello

[Semola]

I'm resolved.

I'm rewrite entire class using the Epplus library.
With this library, I was able to set the formula for validation.

Thanks to everyone who tried to help me.

Marcello