dim ws as excel.worksheet not defined

[Rich Russell]

A beginner here.  I'm not able to get a few items defined and I'm stumped.  In the statement highlighted in yellow, Excel.worksheet is declared as undefined.  This also happens (undefined) if I attempt to dim a workbook or any other Excel object.  What am I doing incorrectly?

Imports ExcelDna.Integration
Imports ExcelDna.Integration.ExcelDnaUtil
Imports ExcelDna.IntelliSense
Imports Microsoft.Office.Interop.Excel

Public Module TestPIA

    Public Function TestExcel() As Double ' test access to Excel

        Dim ws As Excel.worksheet
        Application.ACTIVEWorkbook.sheets.Add(After:=Application.ActiveWorkbook.Worksheets(Application.ActiveWorkbook.Worksheets.Count))
        Application.ACTIVEWorkbook.sheets(Application.ActiveWorkbook.Worksheets.Count).Name = "ll"

        Return 0
    End Function
End Module

[Rich Russell]

I'm additionally having difficulty writing to a worksheet.  I've converted several UDFs from VBA to VB.net with ExcelDNA.  A few of the UDFs perform complex mathematical computations. To help a user verify correct parameter inputs and results, the user has an option of entering a parameter with a worksheet name which will instruct the UDF to place detailed trace calculations on the worksheet to allow the user see the computation step by step.  I'm having difficulty assigning values to cells in the new worksheet.   Any help would be much appreciated! 

[Govert van Drimmelen]

--------------------------------------------------

Excel-DNA is now registered on GitHub Sponsors.

Your monthly contribution helps the project thrive.

--------------------------------------------------

There is some confusion between the Imports and the types in your code.

What I have below seems to compile and work.

Note that I use a call to ExcelDnaUtil.Application to get hold of the root Application object.

Excel does not allow you to write to or modify the worksheet from inside a function / calculation context.

The best you can do is to schedule a macro which will run as soon as possible, typically after the calculation is complete.

When setting up the macro run you need to save or pass in any context needed, because the 'ActiveSheet' or the 'Selection' might not be what it was when the function was called.

In addition, if you have this function called from different cells, you can't be sure of the order in which the macros will run.

So it will be tricky to make reliable, and you might consider other mechanisms too which might be less interfering with the user's workbook.

For example, you can keep the detailed log in memory and display in a custom task pane.

Anyway, here's some code that will hopefully get you started.

-Govert

Imports Microsoft.Office.Interop.Excel
Imports ExcelDna.Integration

Public Module TestPIA

    Public Function TestExcel() As Double ' test access to Excel

        ' Set up a macro run when Excel is ready again
        ' - after calculations are complete and the user is not interacting with the sheet.

        ExcelAsyncUtil.QueueAsMacro(Sub() WriteStuffToSheet())

        Return 0
    End Function

    Private Sub WriteStuffToSheet()
        Dim Application As Application
        Dim ws As Worksheet

        Application = ExcelDnaUtil.Application

        Application.ActiveWorkbook.Sheets.Add(After:=Application.ActiveWorkbook.Worksheets(Application.ActiveWorkbook.Worksheets.Count))
        Application.ActiveWorkbook.Sheets(Application.ActiveWorkbook.Worksheets.Count).Name = "ll"

    End Sub
End Module

[Rich Russell]

Thanks Govert - much appreciated!