xLL & Excel Objects - Add-in Express .NET forum

Michael Kaden

Posts: 306
Joined: 2017-11-15

Dear Andrei,

I want to follow up on the following topic:

https://www.add-in-express.com/forum/read.php?FID=5&TID=14827&MID=76286#message76286

The topic seems to be closed as I cannot input anything new.

I am quite comfortable with just executing XLL AddIn where the UDF collects data from Excel cells and calculates the UDF result. That works, I believe without any hidden problems.

I some instances, I would like to extend the UDF's action, for example to highlight cells background colour if the result is not in a certain range. Trying to do this in the XLL module gave some interesting results, mainly Error 1004. Especially interesting is that sometimes it works, sometimes it does not, without changing code. So to do this, I would like to use the method described in your above post:

To do this, your XLL should call a method in the COM add-in and return a value for Excel to calculate the formula; the method should prepare data (if any) for the next step, invoke ADXAddinModule.SendMessage() passing it an integer > 1024 and return. Calling the SendMessage method actually sends a message to a hidden window that add-in Express creates for your add-in behind the scenes; when the window procedure of that window receives that message, the OnSendMessage event is raised on the add-in module. There's a delay between sending the message and receiving it; this delay allows Excel to switch out of the XLL context after your XLL completes. In other words, the event handler of the OnSendMessage event is executed in the COM add-in context. The event is the next step mentioned above. In that event handler, you are free to use any object model calls

Do you have any code examples, please VB to do the following.

run a XLL UDF and if the result is outside of a certain range:

invoke ADXAddinModule.SendMessage() - How do you do this and how to pass the Workbook/Sheet/range as well as the action required(change background colour)What function/sub is called by the "hidden Window"

which will result in a Com AddIn function to run and change the Background colour of the "caller" cell.

So a simple example will help greatly like:

XLL UDF

Function isWhat() as Double

Dim caller As ADXExcelRef = _Module.
CallWorksheetFunction(ADXExcelWorksheetFunction.Caller)
Dim AddressNow As String
AddressNow = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller).ConvertToA1Style
Dim range As Excel.Range = _Module.ExcelApp.Range(AddressNow)
isWhat = range.offset(1,1).Value *100

the above works without problems

How to use SendMessage method to:
If isWhat > 100 make the cell background colour = red

End Function

I am not sure if I expressed myself correctly. Code example will help.

Thank you & kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 17785
Joined: 2006-05-11

Hello Michael,

That topic is closed after 30 days of inactivity.

You need to call a method defined on the add-in module of your COM add-in:

MyProject.AddinModule.CurrentInstance.MyMethod(someValue)

That method should save the value passed e.g. to a class-level variable. The next step is to call SendMessage:

dim theCurrentRange as Excel.Range
const MyMessageId as integer = 1
Public Sub MyMethod(aRange as Excel.Range) 'or pass a string and construct the Range object using the string
theCurrentRange = aRange
Me.SendMessage(1024+MyMessageId
end sub

In the event handler of the OnSendMessage event you get theCurrentRange and set the background color as required. Record a VBA macro while modifying the background color in the Excel UI to find the objects/members involved in the process; use these objects/members in your code.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Posts: 306
Joined: 2017-11-15

Dear Andrei,

thank you, in principal this works good.

However, when I have more (200) UDF's which will be called by "Calculate all" introduced by

SendKeys.Send("^%" & "{F9}")

then only one cell will be changed. If I put a MsgBox in the UDF code Function so that I have to acknowledge every call to the UDFs it works fine with SendKeys.Send("^%" & "{F9}")

However a sleep(1000)does not help, i.e only one cell is changed again.

By the way, what does the MyMessageID stand for, incrementing it did not solve the above behaviour.

thank you & kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 17785
Joined: 2006-05-11

Michael,

Using a message box to debug an Office extension may spoil the picture because it generates extra events. I recommend that you use System.Diagnostics.Debug.Print to get debug info.

Michael Kaden writes:
By the way, what does the MyMessageID stand for, incrementing it did not solve the above behaviour.

This is a message identifier. Check section Wait a Little at https://www.add-in-express.com/docs/net-office-tips.php#wait-a-little. Make sure your OnSendMessage filters out your message as shown on that page.

Michael Kaden writes:
then only one cell will be changed.

I suppose this occurs because the code above stores one cell only. I suggest that you modify it so that it stores cells to modify in a queue.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Posts: 306
Joined: 2017-11-15

Dear Andrei,

I read through your recommendations, but did not succeed. So I created a sample project to show what the issue is. I mail the sample project to the support email address.

The sample project TestSend.xlsm works as follow:

I have 17 lines "column D" where the XLL UDF alera3 is. With Column C, I can choose if the SendMessage is used. If Column C = 1 then it is used otherwise not. So first at all I set it to 0.

The alera3 UDF has 2 arguments from which it is initiated. Each UDF can be called singular by changing the cell left to it in Column B or all UDF's together by changing B3. the UDF will then increase its own cell value by 1. This works well. either by changing a value in the B column or changing B3 which will change the Value of all UDF's.

If I enable SendMessage by setting C5 = 1 and so all C column values to 1. then I can increment the UDF's own value and also the corresponding E Column value which is the Sendmesag Result by one if I change a single argument in Column B. But if I change the common argument B3 then all the direct XLL UDF results are increased by one, but the outcome of the SendMessage (Column E) shows 17 increases in one UDF and none in all the others. So it seems that the new caller range of each UDF is not transported to the SendMessage method.

I tried all but could not find a solution to this. I assume that it only need minor adjustment but knowing how exactly SendMessage works I cannot find the solution.

Thank you for your support & kind regards

Michael

Andrei Smolin

Add-in Express team

Posts: 17785
Joined: 2006-05-11

Hello Michael,

Michael Kaden writes:
So it seems that the new caller range of each UDF is not transported to the SendMessage method.

It is. But every call passes a new value overwriting the existing one. When the message is received (by the hidden window that Add-in Express creates for your add-in) and the OnSendMessage event is raised, your code only finds the last value. To avoid this, you should put every incoming range (range address! See below.) to a list/queue.

As explained, you shouldn't access the Excel object model in your XLL code. I suppose in the code you've sent me, you do this to demonstrate the issue only.

All code that invokes objects/members of the Excel object model should be executed outside of the XLL call context (i.e. after the delay; to create a delay you invoke the SendMessage method; the OnSendMessage event occurs *after* the delay). In the code you've sent me, you create an Excel.Range object right in the code of your UDF i.e. in the context of the UDF call. To avoid potential problems that may show itself in different Excel versions and/or different scenarios, you should pass a string (not a Range) to your CheckValid method:


Public Shared Function qqqqq(TR1, TR2) As Double
    On Error GoTo Errorhandler
    Dim caller As ADXExcelRef = _Module.
    CallWorksheetFunction(ADXExcelWorksheetFunction.Caller)
    Dim AddressNow As String
    AddressNow = _Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller).ConvertToA1Style
    Call AddinModule.CurrentInstance.CheckValid(AddressNow)
    ...
    Return {some value}
    Exit Function
Errorhandler:
    MsgBox(Err.Number & " " & Err.Description, Err.Source & " alera3")
    Resume Next
End Function

The CheckValid method should put the string in a System.Collections.Generic.Queue(Of String), System.Collections.Generic.List(Of String), or System.Collections.Specialized.StringCollection; in C# these are Queue<> and List<>. StringCollection is probably the best:


Imports System.Collections.Specialized
Private sc As StringCollection = New StringCollection()
' you declare sc Public only if there's some code accessing it and the code is external to the add-in module
Private Const MyMessage2 As Integer = 1024 + 2

Public Sub CheckValid(strRangeAddress As String)
    On Error GoTo Errorhandler
    'Invoke send Message
    sc.Add(strRangeAddress)
    Me.SendMessage(MyMessage2)
    Exit Sub
Errorhandler:
    Call MsgBox(Err.Number & " " & Err.Description & " " & Err.Source & " CheckValid")
    Resume Next
End Sub

In the OnSendMessage event you must check for your message. Currently, you react to *every* message that the hidden window receives; it receives a lot of them; the Windows itself is built on these messages.

If (e.Message = MyMessage2) Then
' here you retrieve all strings (one by one) from the collection and, for each string
' (it represents a range address), you create a new Excel.Range object and
' modify it as required; then you delete the string from the collection. That is,
' you scan the collection to modify all ranges, the addresses of which
' the collection contains, and remove all strings from the collection.
End If

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Posts: 306
Joined: 2017-11-15

Dear Andrei,

thank you very much for your prompt and detailed reply.

I think I have done it right now in my TestSend sample project. I send you the project to the support mail address.

However I have 17 UDFs in my sample project but the OnSend Event with e.Message = 1026 is run 2 x 17 =34 times. Should I do something different here?

I guess with this exercise, you finally have convinced me to keep all Excel Objects out of the XLL Module

thank you and best regards

Michael

Andrei Smolin

Add-in Express team

Posts: 17785
Joined: 2006-05-11

Michael,

You need to filter out your message in the OnSendMessage event:

If (e.Message = MyMessage2) Then
' here you retrieve all strings (one by one) from the collection and, for each string
' (it represents a range address), you create a new Excel.Range object and
' modify it as required; then you delete the string from the collection. That is,
' you scan the collection to modify all ranges, the addresses of which
' the collection contains, and remove all strings from the collection.
End If

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader

Michael Kaden

Posts: 306
Joined: 2017-11-15

Dear Andrei,

yes I do have the "If (e.Message = MyMessage2) Then" Filter. To explain the question better,please change the OnSend eventhandler to:

on class level

    Public MyMessage2 As Integer = 1024 + 2
    Public MessageRuns, MessageLoops As Integer

Private Sub AddinModule_OnSendMessage(sender As Object, e As ADXSendMessageEventArgs) Handles Me.OnSendMessage
        On Error GoTo Errorhandler
        Dim k As Integer
        If (e.Message = MyMessage2) Then
            MessageLoops = MessageLoops + 1
            For k = 0 To sc.Count - 1
                MessageRuns = MessageRuns + 1
                xlRange = ExcelApp.Range(sc.Item(k))
                xlRange.Offset(0, 3).Value = MessageRuns
                xlRange.Offset(0, 4).Value = MessageLoops
                sc.Remove(k)
            Next k
        End If
        sc.Clear()
        Exit Sub
Errorhandler:
        Call MsgBox(Err.Number & "  " & Err.Description & "  " & Err.Source & "  OnSendMessage")
        Resume Next
    End Sub

and you run it the first time by changing B3, column G shows the k value of above loop as one cell is calculated after each other, and column H show all "1" as the "If (e.Message = MyMessage2) Then" is used once and then the program works through the collection item 1-17 in the k loop.

If you then run the UDFs again by changing B3 then the values in column G are incremented by 17, which I expect as the For k = 0 To sc.Count - 1 is run 17 times.

But at the same time Column H values are incremented by 17 also. I would have expected an increment of + 1?

sorry if I do not see the obvious here

Thank you and regards

Michael

Andrei Smolin

Add-in Express team

Posts: 17785
Joined: 2006-05-11

Hello Michael,

The AddinModule_OnSendMessage method is called 17 times. That's why you get MessageLoops = 17.

Michael Kaden writes:
column H show all "1" as the "If (e.Message = MyMessage2) Then" is used once

That is, all the cells were assigned their values in one go.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader