|
|
|
Michael Kaden
Guest
|
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: 18817
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.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 15 Nov, 2017 09:57:10
|
|
Top
|
|
|
Michael Kaden
Guest
|
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 =
gray
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")
gray
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
Guest
|
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
Guest
|
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: 18817
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.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 16 Nov, 2017 01:52:27
|
|
Top
|
|
|
Michael Kaden
Guest
|
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: 18817
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.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 16 Nov, 2017 09:07:21
|
|
Top
|
|
|
Michael Kaden
Guest
|
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 red
Imports AddinExpress.MSO red
'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 red[/COLOR]
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: 18817
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.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 20 Nov, 2017 05:21:47
|
|
Top
|
|
Forums
Topics
Search
Help
Login
Register