I am having difficulty in accessing the Excel ActiveSheet object in a COM AddIn developed in VB.NET (2008 Express edition)
For this test I have two simple functions in the AddIn:
One function THANKS() just returns a string - I use it to prove that the COM AddIn is working.
The Other function is meant to return the name of the Active Worksheet.
Here is the function in it's latest form:
Public Function ActName() As Object Implements ComIF.ActName
'returns active worksheet name
Dim _Workbook1 As _Workbook
Dim objAws As Object
Dim aws As Worksheet
objAws = _Workbook1.ActiveSheet
aws = CType(objAws, Worksheet)
ActName = aws.Name
I get a compile warning "Variable '_Workbook1' is used before it has been assigned a value. A null reference exception could result at runtime.", but it compiles OK, and the 'Thanks' function works OK.
The function =ActName() in a cell returns the #VALUE error
I would be most grateful if someone could suggest appropriate code to access the ActiveSheet object and get its name.
I have searched for many hours and tried various bits of code, but so far no luck.