Updated cell value not yet available

Mar 21, 2016 at 1:54 PM
I created a function to generate sequential number based on previous values (upper range) in column B. So first one will be 1 if no other values are available. The next one will be to 2 and so on.

If I repeat the formula lets say 10 times by dragging it below, calculated values are wrong. I suspect that some values are not yet available in Excel.

Here's the results (at first) : 1, 2, 3, 4, 5, 6, 7, 8, 9, 2, 3, 4

If I refresh (F9) : 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 (everything is fine)

With more values I have to refresh several times to get the correct answer.

Here's the code
<ExcelFunction(IsMacroType:=True, Isvolatile:=True)>
Public Shared Function PLIGNE(name As String) As Integer
    Dim reference As ExcelReference = XlCall.Excel(XlCall.xlfCaller)
    Dim r As New ExcelReference(0, reference.RowFirst - 1, 1, 1)

    Dim m As Integer = 0

    For Each cell As Object In ReferenceToRange(r).Cells
        If cell.Value IsNot Nothing Then
            If IsNumeric(cell.Value) Then
                m = cell.Value
            End If
        End If
    Next

    Return m + 1
End Function
Thanks for you help.
Coordinator
Mar 21, 2016 at 2:03 PM
I think this is expected when you use IsMacroType=true and read from the sheet inside your function.

A better plan would be for your function to take as an input all the values to consider, and use the OFFSET function to pass it the right input range.

-Govert
Mar 21, 2016 at 5:49 PM
Edited Mar 21, 2016 at 5:55 PM
Thanks Govert for your input.

I came to your Library after having problems, not with the inner working of my function, but to distribute my VBA AddIn among my user base since as you know that each UDF is reference to the absolute path of the AddIn file.

Looking for a solution, I found that XLL code base will solve this particular problem.

First, I tried to figure out what you proposed me. And I end up with a non-ExcelDNA solution :
IF(ROW()=1;0;MAX(INDIRECT(ADDRESS(1;COLUMN())):OFFSET(INDIRECT(ADDRESS(ROW();COLUMN()));IF(ROW()=1;0;-1);0)))+1
Working but damn too long. Or maybe I didn't figure out what you mean at first.

What I would like to have is something like :

=PLIGNE()
=PLIGNE()
=PLIGNE()

without reference to any range (that's should be to job of my function to figure it out).

If I remove a reference, everything else should recalculate accordingly.

So my function in VBA is working as expected :
Function PLIGNE() As Integer
    Application.Volatile True
    
    Dim r As Range
    Set r = Application.Caller
    
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng As Range
    Dim cell As Range
    
    Set rng1 = r.Worksheet.Cells(1, r.Column)
    Set rng2 = r.Worksheet.Cells(r.Row - 1, r.Column)
    Set rng = r.Worksheet.Range(rng1.Address, rng2.Address)
    
    Dim m As Integer
    m = 0
    
    For Each cell In rng.Cells
        If Not IsEmpty(cell.Value) Then
            If IsNumeric(cell.Value) Then
                m = cell.Value
            End If
        End If
    Next
        
    PLIGNE = m + 1
End Function
I thing I should understand that Excel KNOWS in which order to call the references based on the context so that values are computed is the RIGHT order.

And that's something that is happening differently when my function is called through ExcelDNA ?!?

Thanks
Coordinator
Mar 21, 2016 at 6:25 PM
I don't know how to emulate the VBA behaviour in an XLL.

I'm not surprised at the way the XLL function behaves.

I would recommend some way of taking the input as part of the function. In this case, your function looks a lot like MAX(...) + 1. In R1C1 format, the formula can be the same for all the cells - it be =MAX(R1C2:R[-1]C2)+1. In A1 format, the formula is internally the same, but won't display the same in every cell, e.g in row 6 you'd have =MAX($B$1:$B5)+1.

I'm not really an expert in Excel, but keeping the dependencies explicit in your sheet is normally suggested as good advice. That means for a function like this, you would not want it to look the same for every cell, because it will effectively have different inputs (the whole range up to there) for every cell. You could make your own function (instead of MAX), but pass then in the values that it must check explicitly, e.g. =PLIGNE($B$1:$B5).

-Govert
Coordinator
Mar 21, 2016 at 7:14 PM
OK, so you get the VBA behaviour by setting IsMacroType:=False. With True you are able to read uncalculated cells, but that's not what you want. With False the GetValue() call will throw an exception (internally it is an error of type xlretUncalced) and that is what tells Excel to reconstruct the dependency tree.

I would still recommend the more explicit dependencies, though. It's a very confusing function.

This worked for me:
Imports ExcelDna.Integration
Imports ExcelDna.Integration.XlCall

Public Class Class1

    <ExcelFunction(IsMacroType:=False, IsVolatile:=True)>
    Public Shared Function PLIGNE() As Integer
        Dim caller As ExcelReference = Excel(xlfCaller)
        If caller.RowFirst = 0 Then
            Return 1
        End If

        Dim column As ExcelReference = New ExcelReference(0, caller.RowFirst - 1, caller.ColumnFirst, caller.ColumnFirst)
        Return Excel(xlfMax, column) + 1
    End Function
End Class
-Govert
Mar 21, 2016 at 7:17 PM
Sorry to ear that.

I can not afford to provide range in the function.

I am doing a form designer. So every time a user wants to have the line number displayed, he just have to type =PLIGNE() in the cell. Don't want a line number ? Remove the formula and all the following will adjust.

Currently it is fixed to B column by design but will evolve (current column).

In any context, as I understand, I have to specify IsMacroType=true to access other cells. But everytime I'm doing so, I have no warranty that those cells are up-to-date ? Am I right ?
Coordinator
Mar 21, 2016 at 7:26 PM
I see you can make a "Defined Name" like "LINENUM" with the R1C1 formula =MAX(R1C2:R[-1]C2)+1 (temporarily change the book to R1C1, then add the name, then change back). Now you can just put =LINENUM into the cells, and it seems to work right.

-Govert
Mar 21, 2016 at 7:58 PM
Edited Mar 21, 2016 at 7:59 PM
Good to go, sir!

Nice trick. Tried it. Found one problem. It seems doing that way the references are all based from the sheet that I was on.

I tried my previous formula (no sheet reference)
IF(ROW()=1;0;MAX(INDIRECT(ADDRESS(1;COLUMN())):OFFSET(INDIRECT(ADDRESS(ROW();COLUMN()));IF(ROW()=1;0;-1);0)))+1
my name is now sheet valid on each sheet. First IF is for row one.

Even this is not a ExcelDNA solution, I really appreciate your help.

And we are not Excel Expert but it take 2 programmers and you are near to have an Excel Expert (or sort of).

Thanks

Eric
Coordinator
Mar 21, 2016 at 8:03 PM
Hi Eric,

OK cool - I'm glad you found a plan.

Did you see my IsMacroType:=False comment as well?
(The sequence of messages went wrong somewhere.)

-Govert


Coordinator
Mar 21, 2016 at 8:10 PM
You can add an extra bang in the formula for the name - that seems to sort out the sheet:

=MAX(!R1C2:R[-1]C2)+1

-Govert
Mar 21, 2016 at 8:49 PM
The extra bang is working. Still have to insert an IF (0 or MAX) for first row. Tried to replace the -1 with the IF, but does not work. And change C2 to C[0] so it's now working on every column.
=IF(ROW()=1;0;MAX(!R1C[0]:R[-1]C[0]))+1
Any thought for a better solution for row 1 ?

And you're right. I did not see at first your IsMacroType:=False.

Working as expected (and as I expect at first).

A bit slow (running from debugger) since every instances are recalculated. I will try it in RELEASE tomorrow.

Prefer ExcelDNA solution since I won't have to create Defined Names. Speed could be a factor for final solution.

If you're still confused, I could post a screencap of what I'm doing.

Eric
Coordinator
Mar 21, 2016 at 9:03 PM
I haven't been able to get the first row working with the defined name either.

The UDF is indeed faster when the debugger is not attached, but still quite slow on the initial drag-fill. However, it's seems fast enough to update after the first time.

-Govert
Mar 22, 2016 at 2:03 PM
Edited Mar 22, 2016 at 2:04 PM
Either
=SI(ROW()=1;0;MAX(INDIRECT(ADDRESS(1;COLUMN())):OFFSET(INDIRECT(ADDRESS(ROW();COLUMN()));SI(ROW()=1;0;-1);0)))+1
or
=IFERROR(MAX(INDIRECT(ADDRESS(1;COLUMN())):OFFSET(INDIRECT(ADDRESS(ROW();COLUMN()));-1;0));0)+1
are working on row one.

Don't understand why I don't get a circular reference error as with explicit RC formula.

Will probably go with the UDF since it's a more manageable solution. But still have some tests to do. I am able to catch Excel events for now. Looking to add options to context menu.

-Eric
Coordinator
Mar 22, 2016 at 2:29 PM
Yes - that looks a bit awkward.

Please ask if you have any more questions - the best place for Excel-DNA questions and discussion is at the Google group: https://groups.google.com/forum/#!forum/exceldna
(CodePlex is slowly closing down.)

-Govert