Excel XXL get cell values

Add-in Express™ Support Service
That's what is more important than anything else

Excel XXL get cell values
How to retrive cell values for calculation in ADX formulas 
Michael Kaden




Posts: 67
Joined: 2017-11-15
I am new to ADX - previous programming via VSTO Visual Studio

I started a XLL project and produced a UDF – works well

Then I want the UDF to call another function (say Public Function Func() ) in another class (Functions01) – works also well

Then I want that Func() fetches values from the Calling excel sheet. In VSTO I did this with:

Public Class Functions01
Dim activeWorksheet As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet
Dim activeExcel As Excel.Application = Globals.ThisAddIn.Application

Public Function Func() As Double
Dim ac As String
Dim OC, HC, LCV, NoOrg, Org, C, H, O As Double
On Error GoTo Errorhandler
Func = 0
If activeWorksheet IsNot Nothing Then
ac = activeExcel.ActiveCell.Address.ToString
Dim range2 As Excel.Range = activeWorksheet.Range(ac)
Func = range2.Offset(1, 0).Value
Func = Func + range2.Offset(2, 0).Value
End If
Exit Function
Errorhandler:
Resume Next
End Function
End Class

What is the equivalent for my ADX XLL project – in VB please?
What Import statements are needed?
Posted 15 Nov, 2017 07:27:35 Top
Andrei Smolin


Add-in Express team


Posts: 14300
Joined: 2006-05-11
Hello Michael,

It looks like you need to use an Excel Automation add-in instead of the XLL one. This is because when you Excel calls an XLL function, "Excel's Object Model does not expect, and is not prepared for, incoming Automation calls. Consequently, unexpected results or crashes may occur." This citation was taken from http://support.microsoft.com/kb/301443. Alas, the page doesn't exist any longer. I can't tell if this means they've fixed this issue.

As to creating an Excel automation add-in, see https://www.add-in-express.com/docs/net-excel-automation-addins.php.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 15 Nov, 2017 09:57:10 Top
Michael Kaden




Posts: 67
Joined: 2017-11-15
Hello Andrei

thank you for the prompt response. I had already the Impression that the XLL does not support the getting of cell values except the one of the calling cell.

I also believe that with an Excel Automation add-in I cannot create Excel UDF's.Is that correct?

So will I have to have both, an XLL and an Excel Automation add-in ?

Does the user have to run two Setup programms then??

How does the UDF then call the function in the Excel Automation add-in


Instead of writing a new Excel Automation add-in I could use my existing VSTO and link my XLL to it. You send me the code in your mail 10.11.2017 =



public static int MyFunc() {
object aComAddinObject = Module.ExcelApp.COMAddIns.Item(\"MyAddin216.AddinModule\").Object;
object result = aComAddinObject.GetType().InvokeMember(\"Test\", System.Reflection.BindingFlags.InvokeMethod, null, aComAddinObject, null);
if (result is int)
return Convert.ToInt32(result);
else
return -1;
}


I would Need this in VB please
or in other words, how would my UDF in the XLL look?


Would this do it? (My VSTO Project is called "alerasoftAddIn")

Function alera(Prog As String, Optional D01 As String) As Variant
On Error GoTo Errorhandler
Dim addIn As COMAddIn
Dim automationObject As Object
Set addIn = Application.COMAddIns(\"alerasoftAddIn\")
Set automationObject = addIn.Object
Call automationObject.DoWork1(Prog, DoWork, DoText)
If Left(Prog, 2) <> \"TX\" Then
alera = DoWork
Else
alera = DoText
End If
Exit Function
Errorhandler:
Resume Next
End Function


I used this in a xla to link Excel to my VSTO functions and this worked well, but the user would have to instal the xla and run the VSTO ClickOnce installation. This is not very professional.

Perhap one could get an msi Installation which executes both ClickOnce Setups?

Would there perhaps be a way around this if I put all my functions in a DLL (class project) and the XLL would then call These. Could the DLL functions then be able to get cell values? Could the DLL be manually included in the XXL build?

thank you

Michael
Posted 15 Nov, 2017 11:08:34 Top
Michael Kaden




Posts: 67
Joined: 2017-11-15
Hello Andrei

Looks like i solved the issue with an Excel Automation Add-in. Could make an UDF an my own functions calling values from other Excel cells. Works fine.

Thank you

so for now no more is needed.

Will come back when I hit the next wall :-)

regards & thank you.

Michael
Posted 15 Nov, 2017 15:21:59 Top
Michael Kaden




Posts: 67
Joined: 2017-11-15
Sorry got stuck again

in an Excel Automation Add-in function, how do you retrieve the Value of the cell Offset(1,1) of the calling cell?

thank you

Michael
Posted 15 Nov, 2017 16:10:31 Top
Andrei Smolin


Add-in Express team


Posts: 14300
Joined: 2006-05-11
Hello Michael,

If your function is called from a cell, ExcelApp.Caller returns a Range object representing that cell. Use Range.Offset to get any cell related to the caller cell.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 16 Nov, 2017 01:52:27 Top
Michael Kaden




Posts: 67
Joined: 2017-11-15
Hello Andrei,


1.) Where do I put the UDF's in a ComAddIn - in an XLL Container? I could not find any other UDF area in the ComAddIn template.

2.) Using ExcelApp.Caller in the ComAddIn (XLL Container) gives Error

"Reference to a non-shared member requires an object reference".

When I make ExcelApp shared i get

"Cannot refer to an instance member of a class from within a shared method or shared member initializer without an explicit instance of the class".

Do you have a VB code example of this XLL function or a UDF in anóther ComAddIn Container?, please with Imports and Declaration DIMs?

3.) Still I want to try the XLL route once more. It has Advantages such as Speed but also to hide the UDF's.

What is the Application caller in the XLL UDF called? With Declarations/Imports necessary please.

In VBA it is "Application.Caller" without necessity of declaration so I can use

Application.Caller.Worksheet.Parent.FullName
Application.Caller.Worksheet.Name
Application.Caller.Address

If I then create a class project with functions and connect to the calling Excel Instance/Workbook/Cell with

getObject using the Caller Information above. I have done this with a Class-Project which is called from VBA

Can the XXL UDF do what the VBA UDF did?

my DLL functions have

<System.Runtime.InteropServices.ComVisible(True)> and Register for COM interop


would that work on an XXL Project with functions called by the UDF?


regards & thank you

Michael
Posted 16 Nov, 2017 07:28:19 Top
Andrei Smolin


Add-in Express team


Posts: 14300
Joined: 2006-05-11
Hello Michael,

You need to follow steps outlined in https://www.add-in-express.com/docs/net-excel-automation-addins.php. No XLL container is required as no XLL API function can be called in this case; your functions must be declared in the module class which descends from ADXExcelAddinModule.

Michael Kaden writes:
What is the Application caller in the XLL UDF called?


Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller); call it from a function defined in the XLLContainer class. The ADXExcelRef returned by that method allows determining the index (indices) of the cell(s) on the worksheet the UDF is called from. You can also call the ADXExcelRef.ConvertToA1Style (or ConvertToR1C1Style) method to get a string representing the caller's address.

Michael Kaden writes:
Can the XXL UDF do what the VBA UDF did?


You shouldn't call the Excel object model from your XLL. In fact, I don't understand completely your #3 above.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 16 Nov, 2017 09:07:21 Top
Michael Kaden




Posts: 67
Joined: 2017-11-15
Dear Andrei,

I have the ComAddIn working, just would like to have confirmation that it will actually work with several Excel instances open and with several workbooks in the instances, and that the calling function always gets the result. (I assume that the Range Caller Objects also pass the Workbook.Fullname and the Sheet.Name, so it will always referr back to the Workbook and Sheet in which the Calling Cell is. My code is:

Excel UDF (In the ADX ExcelAddInModule)


Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Imports System.Windows.Forms ‘unnecessary ??
Imports AddinExpress.MSO ‘unnecessary ??

'Add-in Express Excel Add-in Module
<GuidAttribute("75B38F2E-CFF9-4B8D-89FB-1A60585FCA1B"), _
ProgIdAttribute("aleraCom03.ExcelAddinModule1"), ClassInterface(ClassInterfaceType.AutoDual)> _
Public Partial Class ExcelAddinModule1

Inherits AddinExpress.MSO.ADXExcelAddinModule
Public Function alera2(ByVal Range As Object) As
Double
On Error GoTo Errorhandler
Dim F1 As New Function01
Dim xlExcel As Excel.Application
Dim xlRange As Excel.Range
alera2 = 0
xlExcel = HostApplication ‘No ExcelApp reference in ADX ComAddIn Excel Add In Module

xlRange = xlExcel.Caller
Call F1.Test01(xlRange, alera2)
Exit Function
Errorhandler:
MsgBox("ERROR" & Err.Number & " " & Err.Description)
Resume Next
End Function
End Class

Then the actal calculation is done in my Public Class Functions01

Imports Microsoft.Office.Interop

Public Class Function01
Public Sub Test01(xLRange As Excel.Range, ByRef TestResult As Double)
On Error GoTo Errorhandler
TestResult = 0
TestResult = xLRange.Offset(1, 0).Value2
Exit Sub
Errorhandler:
MsgBox("ERROR" & Err.Number & " " & Err.Description)
Resume Next
End Sub
End Class

This seems to work fine. The differences to your recomendations are:

I define my own xlExcel (only in the UDF not in the Calculation Function as I assume this is passed by xlRange?)

Dim xlExcel As Excel.Application
xlExcel = HostApplication

Can I leave out in teh ADX ExcelModule

Imports System.Windows.Forms
Imports AddinExpress.MSO

out. It seems to work without These Imports

Please advise if there are any objections to above method?

Thank you & Kind regards

Michael
Posted 19 Nov, 2017 13:52:57 Top
Andrei Smolin


Add-in Express team


Posts: 14300
Joined: 2006-05-11
Hello Michael,

Michael Kaden writes:
just would like to have confirmation that it will actually work with several Excel instances open


If you succeed in starting several Excel instances, I expect that each of the Excel instances should load a separate instance of the the add-in.

Michael Kaden writes:
Imports System.Windows.Forms ‘unnecessary ??
Imports AddinExpress.MSO ‘unnecessary ??


This is of minor importance. You can delete these lines since your code doesn't refer to any type defined in these namespaces.

Michael Kaden writes:
‘No ExcelApp reference in ADX ComAddIn Excel Add In Module


We will fix this omission in a future Add-in Express build. As to now, you can add this property to your code:

    Public ReadOnly Property ExcelApp() As Excel._Application
        Get
            Return CType(HostApplication, Excel._Application)
        End Get
    End Property


Michael Kaden writes:
I assume this is passed by xlRange?


You can use the fact that every type from the Excel Object Model provides the Application property.

Michael Kaden writes:
xlRange = xlExcel.Caller


In theory, this may fail if the Caller returns an object other than Excel.Range.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 20 Nov, 2017 05:21:47 Top