XlCall string formula with cell reference

[audeser servicios]

I get into some disfunctional behaviour... even if it seems a very basic code. I have this little function InsertFormulas:

<code>

Sub InsertFormulas(ByVal formula As Object, Optional ByVal firstRow As Integer = 1, Optional ByVal lastRow As Integer = 1, Optional ByVal firstCol As Integer = 1, Optional ByVal lastCol As Integer = 1)

XlCall.Excel(XlCall.xlcFormulaFill, New Object() {formula, New ExcelReference(firstRow, lastRow, firstCol, lastCol)})

End Sub

</code>

If formula = "=231+231" it will work -when called from VBA procedure-. If formula "=231+B10" it just simply will not work. Quite odd, I should be missing something

But, even worse is that when I call from ribbon it seems that the XlCall it will not work when feeding any formula as parameter, it does not matter "=231+231" or "=231+B10".

<code>

Public Sub Macro1(ByVal Control1 As IRibbonControl)

Call InsertFormulas("=231+231")

End Sub

</code>

Any help will be much appreciated. ExcelDNA seems fantastic but these two simple issues are ruining my expectations

[Govert van Drimmelen]

As a reference for the Excel macros (which you are calling through the C API with the xlcXXXX calls) you can find a link to the macro help file at the bottom of this page: Excel C API · Excel-DNA/ExcelDna Wiki (github.com)

 

The help for FORMULA.FILL refers to the FORMULA help which says:

“

If formula_text contains references, they must be R1C1-style references, such as "=RC[1]*(1+R1C1)". If you are recording a macro when you enter a formula, Microsoft Excel converts A1-style references to R1C1-style references. For example, if you enter the formula =B2*(1+$A$1) in cell C2 while recording, Microsoft Excel records that action as =FORMULA("=RC[-1]*(1+R1C1)").

“

 

So for your example, you’ll need to write references in the formula in R1C1 format.

 

Regarding the call to the macro (“Sub”) from a ribbon handler. The C API (anything called as XlCall.Excel) is not directly available in the ribbon callback context. To call the C API (or a method that calls the C API) you can transition to a macro context by calling the ExcelAsyncUtil.QueueAsMacro helper.

So this should work:

 

Public Sub Macro1(ByVal Control1 As IRibbonControl)

     ExcelAsyncUtil.QueueAsMacro(Sub() InsertFormulas("=231+231"))

End Sub

 

Having said all of that, I would recommend using the COM object model for these macro-style interactions with Excel.

The COM object model is more comprehensive than the C API and you’ll find help and examples more easily.

From inside you Excel-DNA add-in you call ExcelDnaUtil.Application to get hold of the root COM Application object, and from there it mostly works like VBA.

You do need to add a reference in your project to the COM interop assembly – this can be done easily by installing the ExcelDna.Interop package from NuGet.

 

Then you’d have something like this:

 

Imports Microsoft.Office.Interop

 

Public Module Macros

 

    Sub InsertFormulas()

 

        Dim app As Excel.Application = ExcelDnaUtil.Application

 

        app.Range("A1").Value = 7

        app.Range("A2").Formula = "=123+A1"

        app.Range("A2:A10").FillDown()

    End Sub

 

End Module

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/f7f3f822-e403-45f9-9910-0c922b988230n%40googlegroups.com.

[audeser servicios]

Hi Govert,

Thank you for your fast response. Issues SOLVED.

Little context and explanation:

I was introduced by this superb video (https://www.youtube.com/watch?v=z3cbAh7CzMo) from Pamai's VBA A2Z channel, that you already know. As it seems I can not attach project to this thread, here's an external link -on my blog- to my source code version of that project -with little modifications- as it would be of interest for any newbie on Excel-DNA, as I'm, and because the video is not continuous and has some code inserted during the edition that is not explained and could ruin the expectations. With project attached, any other could start with the very basic already configured to run...

The code procedure InsertFormulas() now works -from VBA call- with numerical formulas, even with arbitrary text inside (i.e. "=231+blablabla"), it will throw an obvious error unless text refers to a valid rangeName, but even if error, it'll work and will load the formula. As you have mentioned, "range references" should come on R1C1 notation. It took me a while to got it working (from Pamai's video what I call recall is that I have simplified the Import declarations to not get them redundant... but is not likely to be that the cause). My procedure, that it's called with second button on Ribbon ExcelDNA tab has an inputbox to fast test formulas.

For the issue with the ribbon, in my trials I was just trying to feed some variable to find some limitations of the technique. I have kept the Pamai's initial procedure renamed for any one interested, linked to other button, but it's of no point, I'll detail why: while on his video explanation of this procedure he ammended some declaration that was not showing the Ribbon, but after this he did not run the InsertFunction_NoParameters procedure again he was on... which would not had worked. Your suggestion for ExcelAsyncUtil.QueueAsMacro(Sub() InsertFormulas(AnyFormulaStringHere)), on the other hand, did the trick. When I went with the COM version of the procedure I have not needed the ExcelAsyncUtil configuration.

So all this calamities are caused by me not clearly understanding the C API for referring to references.

Again, would like to thank you for your EXCELent development. Would love to add more BASIC code to repository as, from my point of view, is hard to get into this not coming from IT careers. I have vast knowledge of VBA but very little on C or .Net languages, and samples looked as derived to solve complex stuff, not the typical macro.

Best regards