# 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()

• 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.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

{System.Runtime.InteropServices.COMException}
[System.Runtime.InteropServices.COMException]: {System.Runtime.InteropServices.COMException}
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}
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)

• 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:

With Excel
.SheetsInNewWorkbook = 1
.
.
.
.
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.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}