werafa
01-28-2019, 02:38 AM
Hi all,
I'm working on a user defined function that selects and sums three months worth of data from an annual data entry spreadsheet
I have ID'd the data range of interest, but the function crashes when I attempt to interact with this range. one offending example is
Set sumRange = mySheet.Range(Cells(lRow, lCol1), Cells(lRow, lCol2))
processing this line causes the vbe to cease code execution entirely
my row and column variables do have valid data and I'm getting confabulated over why this is happening.
can anyone spot an error in my code, give any suggestions on how I might be able to trap the error or advise if there are any special rules with UDF's that I am breaking?
the complete module is:
Public Function SumResult(sOrg As String, sName As String) As Variant
' ----------------------------------------------------------------
' Purpose: sum quarter totals for results columns
' Parameter sOrg (String): ID Org data (start row)
' Parameter sName (String): ID row data to sum
' Return Type: Variant
' ----------------------------------------------------------------
Dim myFinMonth As cls_FinMonth
Dim lMonthFrom As Long
Dim lMonthTo As Long
Dim myResult As Variant
Dim lRow As Long
Dim lastRow As Long
Dim mySheet As Worksheet
Dim sumRange As Range
Dim lCol1 As Long
Dim lCol2 As Long
Set mySheet = ThisWorkbook.Worksheets("2018-19")
Set myFinMonth = New cls_FinMonth 'initialise class
myFinMonth.FinMonthName = ThisWorkbook.Worksheets("Admin").Range("nrMonthName")
lastRow = mySheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'ID month range
lMonthTo = myFinMonth.FinMonthNum
lMonthFrom = myFinMonth.FinQuarter * 3 - 2
'ID data Row
lRow = GetRow(sOrg, sName)
'sum range intersect
lCol1 = lMonthFrom + 3
lCol2 = lMonthTo + 3
Set sumRange = mySheet.Range(Cells(lRow, lCol1), Cells(lRow, lCol2))
'myResult = Application.WorksheetFunction.Sum(sumRange)
If myResult = 0 Then
SumResult = "Code Error"
Else
SumResult = myResult
End If
End Function
I'm working on a user defined function that selects and sums three months worth of data from an annual data entry spreadsheet
I have ID'd the data range of interest, but the function crashes when I attempt to interact with this range. one offending example is
Set sumRange = mySheet.Range(Cells(lRow, lCol1), Cells(lRow, lCol2))
processing this line causes the vbe to cease code execution entirely
my row and column variables do have valid data and I'm getting confabulated over why this is happening.
can anyone spot an error in my code, give any suggestions on how I might be able to trap the error or advise if there are any special rules with UDF's that I am breaking?
the complete module is:
Public Function SumResult(sOrg As String, sName As String) As Variant
' ----------------------------------------------------------------
' Purpose: sum quarter totals for results columns
' Parameter sOrg (String): ID Org data (start row)
' Parameter sName (String): ID row data to sum
' Return Type: Variant
' ----------------------------------------------------------------
Dim myFinMonth As cls_FinMonth
Dim lMonthFrom As Long
Dim lMonthTo As Long
Dim myResult As Variant
Dim lRow As Long
Dim lastRow As Long
Dim mySheet As Worksheet
Dim sumRange As Range
Dim lCol1 As Long
Dim lCol2 As Long
Set mySheet = ThisWorkbook.Worksheets("2018-19")
Set myFinMonth = New cls_FinMonth 'initialise class
myFinMonth.FinMonthName = ThisWorkbook.Worksheets("Admin").Range("nrMonthName")
lastRow = mySheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'ID month range
lMonthTo = myFinMonth.FinMonthNum
lMonthFrom = myFinMonth.FinQuarter * 3 - 2
'ID data Row
lRow = GetRow(sOrg, sName)
'sum range intersect
lCol1 = lMonthFrom + 3
lCol2 = lMonthTo + 3
Set sumRange = mySheet.Range(Cells(lRow, lCol1), Cells(lRow, lCol2))
'myResult = Application.WorksheetFunction.Sum(sumRange)
If myResult = 0 Then
SumResult = "Code Error"
Else
SumResult = myResult
End If
End Function