Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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 "James Buist" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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. "James Buist" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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 "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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. "James Buist" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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" 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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 "Peter T" wrote: 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" 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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. "James Buist" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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" wrote in message ... 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. "James Buist" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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 "Jim Cone" wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use Implicit Intersection in a VBA Function
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 "Jim Cone" wrote in message ... 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 "Jim Cone" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
intersection function | Excel Discussion (Misc queries) | |||
implicit file reference | Excel Programming | |||
I NEED A FUNCTION TO FIND IF THERE IS AN INTERSECTION BETWEEN 2 DATE RANGES | Excel Worksheet Functions | |||
I NEED A FUNCTION TO FIND IF THERE IS AN INTERSECTION BETWEEN 2 DATE RANGES | Excel Programming | |||
implicit activation | Excel Programming |