SXLPCA.dll and VB.net

The code below is generating an error. Everything is fine up until I try to assign values to .OutputRange or .VarRange. If I assign "" OR nothing to .OutputRange, however, this does not generate the error. What am I doing wrong. Why would assignments to all the other properties work except for those two?

Dim spPCA As New SXLPCA.clsPCAClass
spPCA = New SXLPCA.clsPCAClass

Dim pcaMethod As New SXLPCA.SXLPCAMethod
Dim pcaExtraction As New SXLPCA.SXLPCAExtraction
Dim intDataRowEnd As Integer
spPCA.PlotCoefficients = True
spPCA.PlotScatter = True
spPCA.PlotScree = True
spPCA.ComponentsToPlot = 2
spPCA.OutputCorrelations = True
spPCA.OutputDescriptives = True
spPCA.OutputScores = True
spPCA.StandardiseScores = True
spPCA.Method = pcaMethod.sxlPCACorrelation
spPCA.Extraction = pcaExtraction.sxlPCAExtractAll
intDataRowEnd = dsCompetitors.Tables(0).Rows.Count + 4

spPCA.OutputRange = "Sheet1!$X$4" System.Runtime.InteropServices.COMException - Object variable or With block variable not set
spPCA.VarRange = "Sheet1!$C$4:$U$" & intDataRowEnd.ToString System.Runtime.InteropServices.COMException - Object variable or With block variable not set
spPCA.Results()

Comments

  • What statistiXL DLLs have you set references to? You will need references to the SXLMain.dll in addition to the SXLPCA.dll. If statistiXL isn't loaded the SXLMain.Excel object will need to be set to an instance of Excel (eg from within the Excel VBA module you would need to set SXLMain.Excel = Application). The Range properties you are setting reference the Excel object in order to validate the ranges passed.
  • I have references to both those dlls. However, I did not set the SXLMain.Excel object to an instance of Excel. Thanks for the correction
  • The previous error eliminated with the following code. Now I just get an Automation Error when I use the .Results() method. Anything else straightforward that I'm missing here?

    Excel = New Microsoft.Office.Interop.Excel.Application
    .
    .
    .
    .
    Dim spPCA As New SXLPCA.clsPCAClass
    spPCA = New SXLPCA.clsPCAClass

    Dim stxlMain As New SXLMain.clsMainClass
    stxlMain.Excel = Excel

    Dim pcaMethod As New SXLPCA.SXLPCAMethod
    Dim pcaExtraction As New SXLPCA.SXLPCAExtraction
    Dim intDataRowEnd As Integer
    spPCA.PlotCoefficients = True
    spPCA.PlotScatter = True
    spPCA.PlotScree = True
    spPCA.ComponentsToPlot = 2
    spPCA.OutputCorrelations = True
    spPCA.OutputDescriptives = True
    spPCA.OutputScores = True
    spPCA.StandardiseScores = True
    spPCA.Headers = True
    spPCA.Method = pcaMethod.sxlPCACorrelation
    spPCA.Extraction = pcaExtraction.sxlPCAExtractAll
    intDataRowEnd = dsCompetitors.Tables(0).Rows.Count + 4

    spPCA.OutputRange = "Sheet1!$X$4"

    spPCA.VarRange = "Sheet1!$C$4:$U$" & intDataRowEnd.ToString <== value = 8</i>


    spPCA.Results() System.Runtime.InteropServices.COMException - Automation error

    MORE INFO on ERROR
    {System.Runtime.InteropServices.COMException}
    [System.Runtime.InteropServices.COMException]: {System.Runtime.InteropServices.COMException}
    HelpLink: Nothing
    InnerException: Nothing
    Message: "Automation error"
    Source: "Line 0 in SXLMain.clsMain.ValidateInputRange.Results"
    StackTrace: " at SXLPCA.clsPCAClass.Results(Boolean& OverWrite)

    So far, it seems that the error occurs whenever all the values in a column are identical (e.g. all 1s, or all 0s, or all 5s, etc.) That's easy enough to change in the input dataset. I'll see if that solves it.

    This takes care of the problem when running StatistiXL directly in Excel.
    However, the error is still generated from within VB even after the input data has been modified to assure there are no columns in which all the values are identical.

    Any guidance you could give would be greatly appreciated



    OCTOBER 24 1:34PM EST
    Made some progress. The automation error from .Results was because I have headers in my dataset and forgot to set the .Headers property to TRUE
    Now I have the following error to address


    {System.Runtime.InteropServices.COMException}
    [System.Runtime.InteropServices.COMException]: {System.Runtime.InteropServices.COMException}
    HelpLink: Nothing
    InnerException: Nothing
    Message: "Object variable or With block variable not set"
    Source: "Line 0 in SXLPCA.clsPCABO.Analyse.GetResults.Results"
    StackTrace: " at SXLPCA.clsPCAClass.Results(Boolean& OverWrite)
  • Yes statistiXL will throw an exception if you try to analyse a dataset with columns with no variance.

    What routine is throwing the exception when you run the code from VB. The SXLMain DLL needs to reference an instance of Excel that has a copy of the statistiXL.xla addin installed. It uses worksheets within the xla as temporary holding sites for building up the formatted results etc.
  • the routine throwing the exception is the results method .Results(TRUE)

    So I need something like Excel.AddIns.Add("StatistiXL.xla") so the instance of Excel has the addin?
  • Quite possibly, it's not something I've done, I always call from within Excel....
  • I've got the AddIn successfully installed in the Instance of Excel and have what I think should be one last issue. A different error generated by the .Result method:



    Dim objExcelAddin As Excel.AddIn
    With Excel
    .SheetsInNewWorkbook = 1
    .Workbooks.Add()
    objExcelAddin = Excel.AddIns.Add("C:\Program Files\statistiXL\StatistiXL.xla")
    objExcelAddin.Installed = True
    .Workbooks.Open(objExcelAddin.FullName, , False)
    .
    .
    .
    .
    Dim stxlMain As New SXLMain.clsMainClass
    stxlMain.Excel = Excel

    Dim pcaMethod As New SXLPCA.SXLPCAMethod
    Dim pcaExtraction As New SXLPCA.SXLPCAExtraction
    Dim intDataRowEnd As Integer

    Dim spPCA As New SXLPCA.clsPCA
    'spPCA = SXLPCA.clsPCA

    spPCA.PlotCoefficients = True
    spPCA.PlotScatter = True
    spPCA.PlotScree = True
    spPCA.ComponentsToPlot = 2
    spPCA.OutputCorrelations = True
    spPCA.OutputDescriptives = True
    spPCA.OutputScores = True
    spPCA.StandardiseScores = True
    spPCA.Headers = True
    spPCA.Method = pcaMethod.sxlPCACorrelation
    spPCA.Extraction = pcaExtraction.sxlPCAExtractAll
    intDataRowEnd = dsCompetitors.Tables(0).Rows.Count + 4

    spPCA.OutputRange = "Sheet1!$X$4:$X$4"

    spPCA.VarRange = "Sheet1!$C$4:$U$" & intDataRowEnd.ToString
    spPCA.Results(True)


    {System.Runtime.InteropServices.COMException}
    [System.Runtime.InteropServices.COMException]: {System.Runtime.InteropServices.COMException}
    HelpLink: "C:\Program Files\Microsoft Office\Office1233\XLMAIN11.CHM#1001004"
    InnerException: Nothing
    Message: "PasteSpecial method of Range class failed"
    Source: "Line 0 in SXLPCA.clsPCA.Results"
    StackTrace: " at SXLPCA.clsPCAClass.Results(Boolean& OverWrite)
  • All that is happening here is that the DLL is trying to paste the output into the location specified by spPCA.OutputRange. Make sure that the range can be written to and it should work. This will fail if part of the output range contains part of an Excel array for instance.
Sign In or Register to comment.