How to code this in Excel DNA

[andste]

Hi forum,

I am currently exploring the possibilities of ExcelDNA by converting
some of my VBA functions.
Question: How would I code something like this is ExcelDNA/VB.NET...

Function GetColumn(TopCell as Variant) as Variant
GetColumn= Range(TopCell.Offset(0, 0), TopCell.Offset(0,
0).End(xlDown))
End Function

Thank you for your assistance in advance.

Kind regards,
andste

[Govert van Drimmelen]

Hi andste,

Your function is an interesting one to convert. If I understand right,
your function would take a cell reference and would return as an array
the column of values below that cell.
The short answer would be:

<ExcelFunction(IsMacroType:=True)> _
Public Function GetColumn(<ExcelArgument(AllowReference:=True)>
TopCellRef)
Dim App = ExcelDnaUtil.Application
Dim TopCell = App.Range(XlCall.Excel(XlCall.xlfRefText,
TopCellRef, True))
Return App.Range(TopCell.Offset(0, 0), _
TopCell.Offset(0, 0).End(-4121)).Value
End Function

But I'll make a somewhat clearer example and explain a bit.

Dealing with the incoming reference is not hard - I just mark the
argument with the <ExcelArgument(AllowReference:=True)> attribute.
This would allow me to get a reference of the type
ExcelDna.Integration.ExcelReference, if a reference is put into the
formula. If an ExcelReference is not received, we can just return
#VALUE like your VBA function would.

There are several issues when converting functions that use the COM
objects like 'Range' from VBA to Excel-DNA. It might be instructive to
describe what I was thinking while converting this function.

One decision is whether to:
1. rewrite in terms of the C API (these are the
XlCall.Excel(XlCall.xlf....) bits you see in some of the examples and
posts, or
2. use the COM automation interface from within your Excel-DNA
function. This is not supported but mostly seems to work - though I'd
always mark such functions as IsMacroType:=True.

If you pick option 2, the next decision is whether to:
2-a. add a reference to the Excel Primary Interop Assembly (PIA),
called Microsoft.Office.Interop.Excel, which allows you to use the
Excel types in your code. The disadvantage is that there are different
PIAs for different versions of Excel, and that you might need to
redistribute the interop assembly with your add-in.
2-b. call the COM interface late-bound, with everything defined as
'Object'. You can do this if using VB (or C# 4 under .NET 4) but your
constants need to be translated to numbers which you find by using
some COM library viewer (like the Object Browser in Visual Studio).

Now, I first tried going down option 1. I always prefer to use the C
API, especially in UDFs. The main issue is how to do the equivalent of
the '.End' method. I check in the XLMACR8.HLP file which is the
reference to the C API Macros (you can find it here:
http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=94be9dfa-8a84-4155-b75f-f29b15d5629f).
Finding the SELECT.END command (xlcSelectEnd) I notice that it will
change the current selection, something I don't really want to do in a
function.

So I decide to try option 2. For option 2, we need to convert the
special Excel-DNA object of type ExcelReference to a Range object. I
add a helper that does this.
The rest is then a slightly more verbose version of your function. I
stick with the PIA reference in my .dna file - it should load the
latest version from the GAC. But to redistribute the add-in, you might
need to consider option 2-b, or at least make sure you test carefully.

The complete add-in is pasted below.

-Govert

<DnaLibrary Language="VB">
<Reference Name="Microsoft.Office.Interop.Excel" />
<![CDATA[
Imports Microsoft.Office.Interop.Excel

Public Module Test

<ExcelFunction(IsMacroType:=True)> _
Public Function GetColumn( _
<ExcelArgument(AllowReference:=True)> TopCellRef As Object) _
As Object

Dim App As Application
Dim TopCell As Range
Dim ResultRange As Range

If TypeOf(TopCellRef) Is ExcelReference Then

App = ExcelDnaUtil.Application
TopCell = ReferenceToRange(TopCellRef)
ResultRange = App.Range(TopCell.Offset(0, 0), _
TopCell.Offset(0, 0).End(XlDirection.xlDown))
' XlDirection.xlDown = -4121

' Can't just return the Range - need to return the value
Return ResultRange.Value
Else
' Explicitly return #Value
Return ExcelError.ExcelErrorValue
End If
End Function

Private Function ReferenceToRange(xlref as ExcelReference) as Range
Dim App As Application = ExcelDnaUtil.Application
Return App.Range(XlCall.Excel(XlCall.xlfRefText, xlref, True))
End Function

End Module
]]>
</DnaLibrary>

[andste]

> ...If I understand right,

> your function would take a cell reference and would return as an array
> the column of values below that cell.

Yes, exactly. But it would only return the "non-empty" cells below a
certain cell.
If I understand your example correctly, you would grab all cells
below.


I use this to create "dynamic time series data"; for example, given...

Function DoSomething(anArray as double(,)) as object

Function GetColumn(aTopCell as object) as double(,)

...the following cell formula in Excel would update "automatically"
if new observations are added added below aTopCell...

=DoSomething(GetColumn($A$1)

Maybe there are more elegant ways to implement this?

[Govert van Drimmelen]

Hi,

Have you tried the example?
I think this GetColumn function should do exactly what you want.

-Govert

[andste]

I tried several times, it doesn't work. All I get is #VALUE. Thank you
anyway for the very interesting explanations of your code. Rgrds,
Andreas

On Feb 7, 9:21 am, Govert van Drimmelen <gov...@icon.co.za> wrote:
> Hi,
>
> Have you tried the example?

> I think thisGetColumnfunction should do exactly what you want.

>
> -Govert
>
> On Feb 7, 10:14 am, andste <a321654...@gmail.com> wrote:
>
> > > ...If I understand right,
> > > your function would take a cell reference and would return as an array
> > > the column of values below that cell.
>
> > Yes, exactly. But it would only return the "non-empty" cells below a
> > certain cell.
> > If I understand your example correctly, you would grab all cells
> > below.
>
> > I use this to create "dynamic time series data"; for example, given...
>
> > Function DoSomething(anArray as double(,)) as object
>

> > FunctionGetColumn(aTopCell as object) as double(,)

[Govert van Drimmelen]

Strange....

Perhaps you can try this:
1. Take the <DnaLibrary>....</DnaLibrary> part above, and paste into a
text file called GetColumn.dna.
2. Copy the ExcelDna.xll from the version 0.28 distribution and call
it GetColumn.xll, next to the GetColumn.dna file.
3. Start Excel and File->Open the GetColumn.xll - you should see it
registering in the Status Bar.
4. In a new sheet, put 1, 2, 3, 4 down column A, from A1 to A4.
5. Select Range B1 to B10 and enter the array formula =GetColumn(A1)
with Ctrl+Shift+Enter so it displays as {=GetColumn(A1)}.
6. Check that B1 to B4 now says 1, 2, 3, 4, and from B5 onward you
have #N/A.
7. Enter a value into cell A5, say 5.
8. Check that the array has now updated to have the value 5 in cell
B5.

This worked on my Excel 2007 machine.

-Govert

[andste]

> Strange....
I did exactly as you said and still get #VALUE. I also tried to use
the code as a public class (required the functions to be shared) in a
compiled DLL from SharpeDevelop. No compiler messages, but still
#VALUE.
I am running Office Pro 10 32bit on Windows 7 SP1.
Will try to find out what triggers the excpetion later.

Rgrds,
Andreas

[Govert van Drimmelen]

Hi Andreas,

Could you try this late=bound version which does not reference the
Primary Interop Assembly?

-Govert

<DnaLibrary Language="VB">
<![CDATA[

Public Module Test
<ExcelFunction(IsMacroType:=True)> _
Public Function GetColumn( _
<ExcelArgument(AllowReference:=True)> TopCellRef As Object) _
As Object

Dim App As Object
Dim TopCell As Object
Dim ResultRange As Object

If TypeOf(TopCellRef) Is ExcelReference Then
App = ExcelDnaUtil.Application
TopCell = ReferenceToRange(TopCellRef)
ResultRange = App.Range(TopCell.Offset(0, 0), _

TopCell.Offset(0, 0).End(-4121))

' XlDirection.xlDown = -4121
' Can't just return the Range - need to return the value
Return ResultRange.Value
Else
' Explicitly return #Value
Return ExcelError.ExcelErrorValue
End If
End Function
Private Function ReferenceToRange(xlref as ExcelReference) as

Object
Dim App As Object = ExcelDnaUtil.Application

Return App.Range(XlCall.Excel(XlCall.xlfRefText, xlref, True))
End Function
End Module
]]>
</DnaLibrary>

[andste]

...the Log Display messages were related to copy/paste problem with
the code. They are solved, no more messages, but unfortunatly still
#VALUE!