Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Fri May 20, 2011 3:52 pm

Hi,
I'm trying to set the following formula to a cell using this simple code:

Code: Select all
 
Dim ws = xls.Worksheets(2)
Dim wsSSC = xls.Worksheets(0)

        For Each r In ws.Range("C2:C10000")
            For Each c In r.Cells
                If c.Value.Contains("POIBS") Then
                    Try

                        Dim formula = _
                      String.Concat("=HYPERLINK( CELL(", """", "address", """", ";INDIRECT(CONCATENATE(", """", "Active_SSC!", """", ";", """", "B", """", ";MATCH(D2;Active_SSC!B:B;) )));D2)")

                        'Working Excel Formula: =HYPERLINK( CELL("address";INDIRECT(CONCATENATE("Active_SSC!";"B";MATCH(D2;Active_SSC!B:B;) )));D2)

                        c.Formula = formula

                    Catch ex As Exception
                        Dim ErrorMessage = "Unexpected token.Unexpected token type: Identifier, string value:   at position 26. Formula: ..."
                    End Try
                End If
            Next
        Next


If gives me the error:

{"Unexpected token.Unexpected token type: Identifier, string value: at position 26. Formula: HYPERLINK( CELL("address";INDIRECT(CONCATENATE("Active_SSC!";"B";MATCH(D2;Active_SSC!B:B;) )));D2), Position: 26"}

Does anyone know how to do it?

Cheers

pakko
 
Posts: 1
Joined: Fri May 20, 2011 3:19 pm

Mon May 23, 2011 7:20 am

Dear pakko ,
Thanks for your inqury.
The formula you typed contains an error! I tested it in MSWord and uploaded the error pictures. Check it ,please !
Attachments
error1.png
error1.png (9.92 KiB) Viewed 10367 times
error.png
error.png (5.29 KiB) Viewed 10367 times
Justin
Technical Support / Developer,
e-iceblue Support Team
User avatar

Justin Weng
 
Posts: 110
Joined: Mon Mar 28, 2011 5:54 am

Fri Mar 23, 2012 2:30 pm

I've the same error when trying to set a formula:

Code: Select all
    Public Overloads Overrides Property Formula(ByVal aSheetName As String, ByVal aCell As Excel.XLSCell) As Object
        Get
            Return _xlsWorkBook.Worksheets(aSheetName).Range(aCell.ToString).Formula
        End Get
        Set(ByVal value As Object)
            _xlsWorkBook.Worksheets(aSheetName).Range(aCell.ToString).Formula = value.ToString
        End Set
    End Property


Where value.ToString: "=IF(AC4>1,INDIRECT("E"&AC4) - INDIRECT("F"&AC4),0)"
And the exception is:
Unexpected token.Unexpected token type: tFunction1, string value: INDIRECT at position 10. Formula: IF(AC4>1,INDIRECT("E"&AC4) - INDIRECT("F"&AC4),0), Position: 18

Thanks,
Ricardo

rwilliams
 
Posts: 4
Joined: Wed Feb 15, 2012 7:57 pm

Tue Mar 27, 2012 3:30 am

Hello Ricardo,

Sorry for the late reply and thanks for your inquiry.

We have tested the issue you reported with the latest version(Spire.XLS v6.5.0), it works fine. You can download the new version at http://www.e-iceblue.com/Download/download-excel-for-net-now/spire-xls-v65.html, would you please have a kind try with it? Thanks in advance. I also attached the demo, please have a look.

If you still have any problems, please feel free to contact us.

Have a nice day.
BR
Suvi
e-iceblue support
Attachments
412IfFormula.zip
(4.25 KiB) Downloaded 552 times
User avatar

Suvi.Wu
 
Posts: 154
Joined: Thu Oct 20, 2011 2:53 am

Sun Apr 08, 2012 6:21 pm

Thanks Suvi,

I've downloaded the latest version (6.5.0) and I'm still having the same error. In addition I've new errors when trying other formulas that works in previous version.

Code:
Code: Select all
    Public Overloads Overrides Property Formula(ByVal aSheetName As String, ByVal aCell As Excel.XLSCell) As Object
        Get
            Return _xlsWorkBook.Worksheets(aSheetName).Range(aCell.ToString).Formula
        End Get
        Set(ByVal value As Object)
            _xlsWorkBook.Worksheets(aSheetName).Range(aCell.ToString).Formula = value.ToString
        End Set
    End Property


Formulas:
value = "=AA4*Principal!H4"
aSheetName = "Mapping"
aCellToString = "AB4"
Works Ok

In previous version works Ok:
value = "=IF(AND(B4=2,C4<=AB4,D4>=AB4),4,IF(AND(B5=2,C5<=AB4,D5>=AB4),5,-1))"
aSheetName = "Mapping"
aCellToString = "AC4"
Throw the following Exception:
Unexpected token.Unexpected token type: Identifier, string value: C4 at position 13. Formula: IF(AND(B4=2,C4<=AB4,D4>=AB4),4,IF(AND(B5=2,C5<=AB4,D5>=AB4),5,-1)), Position: 15

value = "=IF(AC4>1,INDIRECT("E"&AC4) - INDIRECT("F"&AC4),0)"
aSheetName = "Mapping"
aCellToString = "AD4"
Exception:
Unexpected token.Unexpected token type: tFunction1, string value: INDIRECT at position 10. Formula: IF(AC4>1,INDIRECT("E"&AC4) - INDIRECT("F"&AC4),0), Position: 18


Also I tryied to set the formula values without the starting "=" and I've the same results.

Note: I'm using Visual Studio 2008 with NETFramework,Version=3.5.

Thanks in advance,
Ricardo

rwilliams
 
Posts: 4
Joined: Wed Feb 15, 2012 7:57 pm

Mon Apr 09, 2012 6:25 am

Hi Ricardo,

So sorry for this inconvenience caused by us.

I made a demo(using Visual Studio 2008 with NETFramework,Version=3.5) set the two formulas you talked about, it works well. Would you please run the demo on your computer to help us find the reason? Please let us know if it can work on your computer. If the demo throw the same exception, please give us more information(the original excel file, your OS version, more relative code &etc) to reproduce the issue.

Thanks in advance and sorry again.

Have a great day.

Kind regards
Suvi
e-iceblue support
Attachments
Formulas.zip
(4.34 KiB) Downloaded 575 times
User avatar

Suvi.Wu
 
Posts: 154
Joined: Thu Oct 20, 2011 2:53 am

Tue Apr 10, 2012 7:08 pm

Suvi,
Thanks for your support.
I guess I found the problem. Regional settings: Must be in English formats:

First:
I ran the code you sent and I'm still having the same errors:
Code: Select all
Imports Spire.Xls

Namespace suviTest
   Friend Class Program
      Shared Sub Main(ByVal args() As String)
         Dim workbook As New Workbook()
         Dim sheet As Worksheet = workbook.Worksheets(0)
         sheet.Range("A1:E4").NumberValue = 2
         sheet.Range("A5:F6").NumberValue = 1
         sheet.Range("AC4").NumberValue = 4

         'set the formula of C9
         (A) --> [b]sheet.Range("C9").Formula = "=IF(AC4>1,INDIRECT(""E""&AC4) - INDIRECT(""F""&AC4),0)"[/b]
(A) Unexpected token.Unexpected token type: tFunction1, string value: INDIRECT  at position 10. Formula: IF(AC4>1,INDIRECT("E"&AC4) - INDIRECT("F"&AC4),0), Position: 18

         'set the formula of C10
         (B) --> [b]sheet.Range("C10").Formula = "=IF(AND(B4=2,C4<=AB4,D4>=AB4),4,IF(AND(B5=2,C5<=AB4,D5>=AB4),5,-1))"[/b]
(B) Unexpected token.Unexpected token type: Identifier, string value: C4  at position 13. Formula: IF(AND(B4=2,C4<=AB4,D4>=AB4),4,IF(AND(B5=2,C5<=AB4,D5>=AB4),5,-1)), Position: 15

         'save the workbook
         workbook.SaveToFile("..\..\result.xls", ExcelVersion.Version97to2003)

         'launch the workbook
         Process.Start("..\..\result.xls")
      End Sub
   End Class
End Namespace


Then:
I changed the formats to English (United States) with correct decimal point, separator, etc. and works fine.

Now I've a question:
My develop machine is configured in English (US) but I don't know if the server machine is configured with the same properties. If it is not, will the program work? Can I force Spire to work in English?

Thanks again,
Ricardo

rwilliams
 
Posts: 4
Joined: Wed Feb 15, 2012 7:57 pm

Wed Apr 11, 2012 6:17 am

Hi Ricardo,

Thanks for your feedback, it helps a lot.

Now, you can force the Spire to work in English putting this code
Code: Select all
 System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
in the method which setting the formulas. This is a workaround which can enable the program to work on your server machine no matter which property is configured. At the meantime, we will do more research on this issue. Once we have any better solution, you'll be surely notified.

In case of any ambiguity, please let me know.

Have a great day.
Best regards
Suvi
e-iceblue support
User avatar

Suvi.Wu
 
Posts: 154
Joined: Thu Oct 20, 2011 2:53 am

Tue May 08, 2012 7:34 pm

I have another problem trying to set formula using custom/user functions.

I've created a XLA (add in) with two functions: SIPFindRow and SIPGetValor these functions works ok in any Workbook opened in Excel.
But when I try to set formula using Spire it throws an error:
SIPFindRow isn't custom function. at position 1. Formula: SIPFindRow($B$4:$H$15,2,AB4), Position: 11

In addition I create the function in VBA in the same file that I'm loading and trying to set the formula and throws the same error.

Code:
Code: Select all
_xlsWorkBook.Worksheets(aSheetName).Range(aCell.ToString).Value = value.ToString

Where
value.ToString = "=SIPFindRow($B$4:$H$15,2,AB4)"


Is there a setting or something where Spire does not check for valid formula? Can I disallow programatically this checking?
Or may I set the XLA add in file to Spire?

Thanks in advance,
Ricardo

rwilliams
 
Posts: 4
Joined: Wed Feb 15, 2012 7:57 pm

Wed May 09, 2012 3:50 am

Hello Ricardo,

We are so sorry that we need some more time to fix this problem. Please have a short wait. After we fix it we will inform you at once. Thanks for your patience.

Regards

Eric
E-iceblue support

Eric
 
Posts: 17
Joined: Tue Apr 24, 2012 4:53 am

Return to Spire.XLS