How to use Implicit Intersection in a VBA Function

[James Buist]

I use defined names in all my workbooks. Sometimes i need to pass a cell
value to a vba function. To do this to an excel function I would just pass a
one dimensional range like an entire column or row and excel will compute for
the value in the same row or column as the function like Gross Profit = Sales
- Cost of Sales and each of those is defined as an entire row.

Now if I do this to a vba function it will not interpret the range as a
cell. I deed to pass it the exact cell reference. I do this just now by using
the index function. However, I'd like to do it entirely in vba and can't find
a simple way to return just the value (not the reference) in the cell in the
same row as the application caller. I'd have to use the application caller
function to return the address of the cell containing the vba function, then
compute the address of the cell in the adjacent row to get its value and used
that. Surely there is an easier way.

Any help would be much appreciated. An example like the one I used above
would be great, i.e. just add two numbers together using add in a vba
function accepting the sales and costs as range inputs.

JMB

 

[Barb Reinhardt]

Post the code you have so far and we can help. It sounds like you've already
made a stab at it and had errors.

Barb Reinhardt

 

[JLatham]

See if this might not help some, goes into a regular code module as a Sub:

Sub ComputeGrossProfits()
'this based on knowing the layout of the sheet:
'GrossProfits is a row that is
' CostOfSales is a known "base" row
' 1 row below CostOfSales is/are SalesValues
' 2 rows below CostOfSales is the GrossProfits row
'
Dim CostOfSalesRange As Range
Dim anyCostOfSaleEntry As Range

Set CostOfSalesRange = Range("CostOfSales")
For Each anyCostOfSaleEntry In CostOfSalesRange
'assume that an empty cell signifies end of the data on the row
If IsEmpty(anyCostOfSaleEntry) Then
Exit For ' all done
End If
'make sure it's numeric and not a text label
If IsNumeric(anyCostOfSaleEntry) Then
'gross profit = sale value - cost of sale
anyCostOfSaleEntry.Offset(2, 0) = _
anyCostOfSaleEntry.Offset(1, 0) - anyCostOfSaleEntry
End If
Next
End Sub

In this case, Row 6 on the sheet was named CostOfSales and row 7 was named
SaleValues (but we really don't need to know that), and row 8 was named
GrossProfits (again, we really don't need that information).

The Set statement makes CostOfSalesRange refer to row 7, and then in the For
Each loop, it loops through each individual cell in that row, examining it to
see if it is empty (end of data) or numeric (something to be used to
calculate gross profit with) and processes accordingly. But we could have
used those other two range names to calculate the row offsets if we'd needed
to. Here is the same thing, rewritten to calculate the offets from the cost
of sales row to the other two rows.
Sub ComputeGrossProfits()
'
Dim CostOfSalesRange As Range
Dim anyCostOfSaleEntry As Range

Dim offsetToGP As Long
Dim offsetToSV As Long

offsetToGP = Range("GrossProfits").Row - Range("CostOfSales").Row
offsetToSV = Range("SaleValues").Row - Range("CostOfSales").Row

Set CostOfSalesRange = Range("CostOfSales")
For Each anyCostOfSaleEntry In CostOfSalesRange
'assume that an empty cell signifies end of the data on the row
If IsEmpty(anyCostOfSaleEntry) Then
Exit For ' all done
End If
'make sure it's numeric and not a text label
If IsNumeric(anyCostOfSaleEntry) Then
'gross profit = sale value - cost of sale
anyCostOfSaleEntry.Offset(offsetToGP, 0) = _
anyCostOfSaleEntry.Offset(offsetToSV, 0) - anyCostOfSaleEntry
End If
Next
End Sub

Possibly a double-post, site being busy, my apologies if it is.

 

[Charles Williams]

Here are 2 UDFs.

The first one uses explicit intersection inside the function
The second is an array formula that calculates the entire column in one go
(this is a lot more efficient)

Option Explicit
Option Base 1

Public Function grossProft(theRev As Range, theCost As Range)

Dim oRow As Range

Set oRow = Application.Caller.EntireRow
grossProft = Intersect(oRow, theRev) - Intersect(oRow, theCost)
End Function

Public Function AGrossProft(theRev As Range, theCost As Range)

Dim oRow As Range

Dim vRev As Variant
Dim vCost As Variant
Dim vAnsa() As Variant
Dim j As Long

vRev = theRev.Value2
vCost = theCost.Value2
ReDim vAnsa(UBound(vCost), 1)

For j = 1 To UBound(vCost)
vAnsa(j, 1) = vRev(j, 1) - vCost(j, 1)
Next j
AGrossProft = vAnsa

End Function

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Jim Cone]

Another way...
'--
Function Gross() As Double
Dim arr As Variant
arr = [Sales - Costs]
Gross = arr(Application.Caller.Column)
End Function
--
Jim Cone
Portland, Oregon USA



"James Buist"
wrote in message
I use defined names in all my workbooks. Sometimes i need to pass a cell
value to a vba function. To do this to an excel function I would just pass a
one dimensional range like an entire column or row and excel will compute for
the value in the same row or column as the function like Gross Profit = Sales
- Cost of Sales and each of those is defined as an entire row.

Now if I do this to a vba function it will not interpret the range as a
cell. I deed to pass it the exact cell reference. I do this just now by using
the index function. However, I'd like to do it entirely in vba and can't find
a simple way to return just the value (not the reference) in the cell in the
same row as the application caller. I'd have to use the application caller
function to return the address of the cell containing the vba function, then
compute the address of the cell in the adjacent row to get its value and used
that. Surely there is an easier way.
Any help would be much appreciated. An example like the one I used above
would be great, i.e. just add two numbers together using add in a vba
function accepting the sales and costs as range inputs.
JMB

 

[James Buist]

Many thanks for the replies.

Charles Williams nailed it. Very simple. My example was just so I could see
how to do it and bears no relation to my actual problem which is now fully
solved. I just couldn't see a 2 line way of getting the actual column of a
defined range that is passed as a parameter.

Jim Cone. Looks clever but I couldn't quite figure it out. Unless the sales
and costs are defined in the function, how can you get them without passing
them as function parameters? I can easily define them in the function but
that limits the function. I wanted a generic way of getting returning the
value from a row in the same column as the cell containing the function -
mimicking the implicit intersection in Excel functions.

In general, the other answer was far too complex and requires the location
of the rows to be set. What if the sales is on Row 20 and the costs is on row
50. Who knows where they are and who cares. The key is that they are in the
same column and that is what Charles returned.

Many thanks to all contributors though. Although there are always multiple
ways of achieving the same goal, the simple generic two liner is most
probably the best solution. I already have a complex way and needed a simple
way.

 

[Peter T]

Just in case of any use, the Intersect of two ranges can be returned
directly in a cell formula

=SUM(A1:C10 2:2)
=(2:2 A1:A3) - (2:2 C1:C3)
etc

Regards,
Peter T

 

[James Buist]

Thanks but I know that and use it heavily. As Excel handles intersection
implicitly, I thought there would be a way for a VBA function to do it to but
couldnt'find the syntax to make it work. My example wouild never have needed
VBA. It was just to illustrate the point

J

 

[Jim Cone]

You asked:
"Unless the sales and costs are defined in the function,
how can you get them without passing them as function parameters?

Answer...
In your original post, you said...
"I use defined names in all my workbooks...and each of those is defined as an entire row"
--
Jim Cone
Portland, Oregon USA



"James Buist"
wrote in message
Many thanks for the replies.

Charles Williams nailed it. Very simple. My example was just so I could see
how to do it and bears no relation to my actual problem which is now fully
solved. I just couldn't see a 2 line way of getting the actual column of a
defined range that is passed as a parameter.

Jim Cone. Looks clever but I couldn't quite figure it out. Unless the sales
and costs are defined in the function, how can you get them without passing
them as function parameters? I can easily define them in the function but
that limits the function. I wanted a generic way of getting returning the
value from a row in the same column as the cell containing the function -
mimicking the implicit intersection in Excel functions.

In general, the other answer was far too complex and requires the location
of the rows to be set. What if the sales is on Row 20 and the costs is on row
50. Who knows where they are and who cares. The key is that they are in the
same column and that is what Charles returned.

Many thanks to all contributors though. Although there are always multiple
ways of achieving the same goal, the simple generic two liner is most
probably the best solution. I already have a complex way and needed a simple
way.

 

[Charles Williams]

Jim,

I like your use of evaluate, but I think you would have to make the function
volatile otherwise it will sometimes not recalculate properly, because it is
using cells via the names which are not being passed as parameters.

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Jim Cone]

Charles,
Noted and thanks.
--
Jim Cone
Portland, Oregon USA



"Charles Williams"
wrote in message
Jim,
I like your use of evaluate, but I think you would have to make the function
volatile otherwise it will sometimes not recalculate properly, because it is
using cells via the names which are not being passed as parameters.
regards
Charles
The Excel Calculation Site
http://www.decisionmodels.com

 

[James Buist]

Jim
Thats Brilliant.
I had no idea you could refernce names in VB by just using them. I have been
explicitly defining the names i need in VB by declaring a range variable and
setting its value with a Range Name from the workbook.

Many thanks for that!!

James

 

[Jim Cone]

James,
You are welcome.
'--
Using range shortcut notation brackets may be the only instance
where you can use range names without enclosing them in quote marks.
Also, using that method may be slower than other types of range references.
If you have a sheet with 10,000 of those functions you might be unhappy.
In addition, please note Charles Williams' post about function calculation.
--
Jim Cone
Portland, Oregon USA



"James Buist"
wrote in message
Jim
Thats Brilliant.
I had no idea you could refernce names in VB by just using them. I have been
explicitly defining the names i need in VB by declaring a range variable and
setting its value with a Range Name from the workbook.
Many thanks for that!!
James

 

[Charles Williams]

The [] bracket notation is actually a shortcut for Application.Evaluate, so
you can use it to evaluate formulae as well as Range Names.
Its also interesting to note that it evaluates Formulae as though they were
array formulae.

So Jim's function is evaluating
arr = [Sales - Costs]
as an array formula returning all the results to a variant containing an
array.

See also
http://www.decisionmodels.com/calcsecretsh.htm

for some details of the limitations of the Evaluate method.

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com