Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help required with UDF
I have written a UDF as below:
Function Sum_PayYr(YearEnd As Range) 'Sums the numbers from all quarters belonging to a given year Dim sum_Value As Double Dim temp_Array As Range Dim num_Cols As Integer Dim i As Integer Dim k As Range num_Cols = Year(YearEnd.Value) - 1975 sum_Value = 0 For i = 0 To num_Cols temp_Array = Range(YearEnd.Offset(1 + 4 * i, -i), YearEnd.Offset(4 + 4 * i, -i)) For Each k In temp_Array sum_Value = sum_Value + k.Value Next k Next i Sum_PayYr = sum_Value End Function The idea is that this will grab 4 cells from the column directly underneath the starting cell (containing a date value), add their values to the return value, then offset by -1 columns and 4 rows and repeat the process, looping through num_Cols times. Everything initialises ok until temp_Array, at which point execution appears to stop without any exception notice for some reason. Does anyone have any idea why this is happening? -- There are 10 types of people in the world - those who understand binary and those who don't. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help required with UDF
I've managed to get this running now, addressed the omission of the 'Set'
statement and modified the function to use number of columns rather than number of years. Cheers :) -- There are 10 types of people in the world - those who understand binary and those who don't. "Geoff" wrote: I have written a UDF as below: Function Sum_PayYr(YearEnd As Range) 'Sums the numbers from all quarters belonging to a given year Dim sum_Value As Double Dim temp_Array As Range Dim num_Cols As Integer Dim i As Integer Dim k As Range num_Cols = Year(YearEnd.Value) - 1975 sum_Value = 0 For i = 0 To num_Cols temp_Array = Range(YearEnd.Offset(1 + 4 * i, -i), YearEnd.Offset(4 + 4 * i, -i)) For Each k In temp_Array sum_Value = sum_Value + k.Value Next k Next i Sum_PayYr = sum_Value End Function The idea is that this will grab 4 cells from the column directly underneath the starting cell (containing a date value), add their values to the return value, then offset by -1 columns and 4 rows and repeat the process, looping through num_Cols times. Everything initialises ok until temp_Array, at which point execution appears to stop without any exception notice for some reason. Does anyone have any idea why this is happening? -- There are 10 types of people in the world - those who understand binary and those who don't. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help required with UDF
Since temp_array is a range, you'll need to use "Set"
Set Temp_array = range(.... Geoff wrote: I have written a UDF as below: Function Sum_PayYr(YearEnd As Range) 'Sums the numbers from all quarters belonging to a given year Dim sum_Value As Double Dim temp_Array As Range Dim num_Cols As Integer Dim i As Integer Dim k As Range num_Cols = Year(YearEnd.Value) - 1975 sum_Value = 0 For i = 0 To num_Cols temp_Array = Range(YearEnd.Offset(1 + 4 * i, -i), YearEnd.Offset(4 + 4 * i, -i)) For Each k In temp_Array sum_Value = sum_Value + k.Value Next k Next i Sum_PayYr = sum_Value End Function The idea is that this will grab 4 cells from the column directly underneath the starting cell (containing a date value), add their values to the return value, then offset by -1 columns and 4 rows and repeat the process, looping through num_Cols times. Everything initialises ok until temp_Array, at which point execution appears to stop without any exception notice for some reason. Does anyone have any idea why this is happening? -- There are 10 types of people in the world - those who understand binary and those who don't. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help required with UDF
Oops. Didn't see you found the answer until after I posted.
Geoff wrote: I've managed to get this running now, addressed the omission of the 'Set' statement and modified the function to use number of columns rather than number of years. Cheers :) -- There are 10 types of people in the world - those who understand binary and those who don't. "Geoff" wrote: I have written a UDF as below: Function Sum_PayYr(YearEnd As Range) 'Sums the numbers from all quarters belonging to a given year Dim sum_Value As Double Dim temp_Array As Range Dim num_Cols As Integer Dim i As Integer Dim k As Range num_Cols = Year(YearEnd.Value) - 1975 sum_Value = 0 For i = 0 To num_Cols temp_Array = Range(YearEnd.Offset(1 + 4 * i, -i), YearEnd.Offset(4 + 4 * i, -i)) For Each k In temp_Array sum_Value = sum_Value + k.Value Next k Next i Sum_PayYr = sum_Value End Function The idea is that this will grab 4 cells from the column directly underneath the starting cell (containing a date value), add their values to the return value, then offset by -1 columns and 4 rows and repeat the process, looping through num_Cols times. Everything initialises ok until temp_Array, at which point execution appears to stop without any exception notice for some reason. Does anyone have any idea why this is happening? -- There are 10 types of people in the world - those who understand binary and those who don't. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB help required | Excel Discussion (Misc queries) | |||
Help required | Excel Worksheet Functions | |||
help required please | Excel Programming | |||
VBA Help required..........! | Excel Programming | |||
Help required...... | Excel Discussion (Misc queries) |