Parallel Cell in Column or Row

Sep 12, 2010 at 3:30 AM
Edited Sep 12, 2010 at 3:35 AM

The value that is returned by the ExcelReference GetValue method is the 1st cell in the range. If the range in the argument is a column, how do I return the cell in the same row as the caller? The same also applies if the argument is a row, where I want to return the cell in the same column.

For example, MYFUNCS.SCALAR(B:B) called from cell A3 should return B3. This is how most Excel built-in functions (e.g. VLOOKUP) behave. However, the GetValue method returns B1.

I have the code below to achieve this. However, it's slow because the ExcelReference is converted to an Excel Range. Is there a way to speed this up (i.e. by changing the return value of GetValue itself so we avoid the extra conversion step) ?

Imports Excel = Microsoft.Office.Interop.Excel
Imports ExcelDna.Contrib.Library
Imports ExcelDna.Integration

    <ExcelFunction(Description:="Caller", Name:="MYFUNCS.SCALAR", Category:="MYFUNCS", IsMacroType:=True, IsVolatile:=False)> _
    Public Function Scalar2(<ExcelArgument(AllowReference:=True, Description:="Arg")> ByVal TheRange As Object) As Object
        Return Scalar(TheRange)
    End Function
    Public Function Scalar(ByRef v As Object) As Object
        Dim CallRow As Integer
        Dim CallCol As Integer
        Dim rngInp As Excel.Range

        Dim ws As Excel.Worksheet

        Dim mTypeName As String

        If Utilities.IsExcelReference(v) Then
            mTypeName = "ExcelReference"
            mTypeName = TypeName(v)
        End If

        If mTypeName = "Range" Or mTypeName = "ExcelReference" Then

            'Range argument
            Dim XlDnaRange As ExcelRange = New ExcelRange(v)

            'If singlecell, then return the value and don't do anything else
            If mTypeName = "ExcelReference" Then
                If XlDnaRange.SingleCell Then
                    Return IfNull(XlDnaRange.Value, "")
                    'Convert to Excel Range
                    rngInp = gxlApp.Range(New ExcelRange(v).ExternalAddress)
                End If
            Else 'Excel Range
                rngInp = v
            End If

            ws = rngInp.Parent

            Dim rngCaller As Excel.Range = XlDnaCaller()
            CallRow = rngCaller.Row
            CallCol = rngCaller.Column

            If rngInp.Rows.Count = 1 And rngInp.Columns.Count = 1 Then
                'Return value of cell
                Return IfNull(rngInp.Value, "")
            ElseIf rngInp.Rows.Count = 1 Then
                'Return value of cell in same column
                Return IfNull(ws.Cells(rngInp.Row, CallCol).Value, "")
            ElseIf rngInp.Columns.Count = 1 Then
                'Return value of cell in same row
                Return IfNull(ws.Cells(CallRow, rngInp.Column).Value, "")
                Return ""
            End If

            Return IfNull(v, "")
        End If

    End Function

Public Function XlDnaCallerString() As String Dim reference As ExcelReference = XlCall.Excel(XlCall.xlfCaller) Dim cellReference As String = XlCall.Excel(XlCall.xlfAddress, 1 + reference.RowFirst, 1 + reference.ColumnFirst) Dim sheetName As String = XlCall.Excel(XlCall.xlSheetNm, reference) cellReference = sheetName + "!" + cellReference Return cellReference End Function Public Function XlDnaCaller() As Excel.Range If MyGlobals.IsCalculateEvent Then MyGlobals.MyStopWatch.Start() Return gxlApp.Range(XlDnaCallerString) If MyGlobals.IsCalculateEvent Then MyGlobals.MyStopWatch.Stop() End Function
Public Function IfNull(ByRef X As Object, ByRef Y As Object) As Object If X Is Nothing Or TypeName(X) = "ExcelEmpty" Then Return Y Else Return X End If End Function



Sep 12, 2010 at 8:55 AM
Edited Sep 12, 2010 at 8:59 AM


I don't think you need to convert to a Range in this case. You can just work with the caller, and set up a new ExcelReference to the single cell you want to read, then return the value.

Try this code, which just looks up the value in the same column as the argument, and same row as the caller. You'd need to fill it out to deal with rows, and with LookupRanges that have offsets...



Public Module TheFunctions

    <ExcelFunction(IsMacroType:=True)> _
    Public Function GetCorrespondingEntry(<ExcelArgument(AllowReference:=True)> ByVal LookupRange As Object) As Object
	Dim xlrefCaller as ExcelReference
	Dim xlrefLookupCell as ExcelReference

	If Not TypeOf LookupRange Is ExcelReference Then
		GetCorrespondingEntry = LookupRange
		Exit Function
	End If

	' Check for single row lookup range - just return it in this case.
	If LookupRange.RowLast = LookupRange.RowFirst Then
		GetCorrespondingEntry = LookupRange.GetValue()
		Exit Function
	End If

	' We have some rows in the LookupRange - get Caller for lookup.
	xlrefCaller = XlCall.Excel(XlCall.xlfCaller)

	' Make a new reference that is in the same row as the caller, but the first column of the LookupRange
	xlrefLookupCell = new ExcelReference(xlrefCaller.RowFirst, xlRefCaller.RowLast, LookupRange.ColumnFirst, LookupRange.ColumnFirst, LookupRange.SheetId)
	return xlrefLookupCell.GetValue()
    End Function

End Module



Note that ExcelReference.GetValue() returns the value of the whole range, not just the first cell; a reference like 'B:B', might marshal the whole column to a large array in managed code.

Let me know if anything is not clear.



Sep 12, 2010 at 10:07 AM

Thanks Govert! It works quite fast and the code is very simple to understand!