Highline Excel Class 37: SUMPRODUCT function

I'm trying to use a class for defining a set of data and also to get values from two different ways, a database and a spreadsheet. Let me explain with an example.

I created a class called clsData with the following code:

Public conn As ADODB.Connection
Private p_isin As String
Private p_tick As String

Public Property Let isin(myvalue As String)
p_isin = myvalue
End Property
Public Property Get isin() As String
isin = p_isin
End Property
Public Property Let tick(myvalue As String)
p_tick = myvalue
End Property
Public Property Get tick() As String
tick = p_tick
End Property


I inserted a public function inside that class, in order to retrieve the data in the two mentioned ways:

Public Function GetData(myvalue As String, DB As Boolean) As clsData
Dim rs As ADODB.Recordset
Dim sql As String
Dim c As Range
Dim myData As clsData
Set myData = New clsData

If DB Then
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
sql = "SELECT * FROM dbData WHERE isin='" & myvalue & "'"
rs.Open sql, conn

If Not rs.EOF Then
myData.isin = rs("isin")
myData.tick = rs("tick")
End If
rs.Close
Else
With ActiveSheet
Set c = .Range("A:A").Find(myvalue, LookAt:=xlWhole)
If Not c Is Nothing Then
myData.isin = .Cells(c.Row, 1)
myData.tick = .Cells(c.Row, 2)
End If
End With
End If
GetData = myData
End Function

All that code goes into clsData. Now I add a standard mode with this code:

Public Function GetClassData() As clsData
Set GetClassData = New clsData
End Function

Sub myTest()
Dim AllData As clsData
Set AllData = New clsData
AllData = GetClassData.GetData("FI0009000681", False)
End Sub

The first function is in order to access the class and the function inside
The routine myTest is trying to retrieve the data I need

I get an error in last line of the function: "GetData = myData". Error message window says "Object variable or block With not established" (actually it is a translation of my current Excel version)

Anybody knows what is wrong with this code?
I can send the whole code to anybody who can help

JC

Hi everybody,
I am fairly new to Excel and I am certain my question might sound ridiculous to some of you. However, I would really appreciate some support
Oh and my name is Seb by the way.

I am working with a Xl to calculate a final account balance.
Due amounts are listed in a column below one another and as soon amounts have been paid they are being "ticked off" and thus impact upon the final account balance.
Say column "F" displays all due amounts and column "E" shows if they have been paid (0 or 1) the final account would be:

= Initial account balance - (F1*G1) - (F2*G2) - (F3*G3) and so on up until F200....

Now I need a formula that saves me all the typing
How do I do that?


Cheers everyone
Seb

Greetings All,

I need help with a really slow spreadsheet. It consist of a database
of 6 columns and 1400 rows that feeds about 6 other sheets that contain
5500 instances of the SumProduct function. Each instance of the
SumProduct function checks values in three columns of the datasheet.
Recalculation takes about 10 to 15 seconds on a pretty fast machine with
lots of memory. I've tried setting the recalculation options to ignore
data tables but that's not an option when using the spreadsheet for its
intended purpose.

Any ideas or help would be greatly appreciated.

I am attempting to develop a actual vs. budget worksheet that will aggregate for the user the number of months activity based upon the month of the fiscal year entered. I believe this can be accomplished using a combination of the SUMPRODUCT and INDEX functions; however, I can't seem to get the context correct. I'm attaching a boiled-down version of the workbook here for your reference. The full worksheet contains over 1,000 account numbers and additional calculation (margins, rate of return, etc.) based upon the YTD actual and budget numbers returned. The worksheets in the file a

CONTROL - where the user will input the fiscal year month (1 - 12) for the data desired. "1" represents the 1st month of the fiscal year, June, "2" represents the 2nd month of the fiscal year, July, etc.
BUDGET - Budget data
ACTUAL - Actual month's results
YTD VARIANCE - Worksheet that accumulates # of months budget and actual based upon users' input in CONTROL

The formulas I'm trying to fix are in YTD VARIANCE, columns C and D. They currently contain a SUMPRODUCT to look up a specific cell reference. I would like these to pull from the respective ACTUAL or BUDGET sheets based upon the user-entered value in CONTROL, cell B3 (e.g. 1=June, 2=July fiscal YTD, 12=May fiscal YTD, etc.).

Any suggestions how to make this work with a SUMPRODUCT/INDEX combination? Also, any suggestions for this same result using defined names would be appreciated.

Thank you,
John Countzler

I have a sumproduct formula that I need to use a wildcard on. However, from other posts I see that sumproduct does not like wildcards.

In column D, I need the formula to look for multiple cells that all begin with the letter "A", (such as A052, AR36, AT26,...). Then anything that begins with "A" in column D, needs to see if "Yes" is in column X. If so, total the costs from Column W.


=SUMPRODUCT(($D$2:$D$1252="H380")*($X$2:$X$1252="Yes"), $W$2:$W$1252)

Hope my rambling makes sense. Thanks for any help!

I am attempting to develop a actual vs. budget worksheet that will aggregate for the user the number of months activity based upon the month of the fiscal year entered. I believe this can be accomplished using a combination of the SUMPRODUCT and INDEX functions; however, I can't seem to get the context correct. I'm attaching a boiled-down version of the workbook here for your reference. The full worksheet contains over 1,000 account numbers and additional calculation (margins, rate of return, etc.) based upon the YTD actual and budget numbers returned. The worksheets in the file a

CONTROL - where the user will input the fiscal year month (1 - 12) for the data desired. "1" represents the 1st month of the fiscal year, June, "2" represents the 2nd month of the fiscal year, July, etc.
BUDGET - Budget data
ACTUAL - Actual month's results
YTD VARIANCE - Worksheet that accumulates # of months budget and actual based upon users' input in CONTROL

The formulas I'm trying to fix are in YTD VARIANCE, columns C and D. They currently contain a SUMPRODUCT to look up a specific cell reference. I would like these to pull from the respective ACTUAL or BUDGET sheets based upon the user-entered value in CONTROL, cell B3 (e.g. 1=June, 2=July fiscal YTD, 12=May fiscal YTD, etc.).

Any suggestions how to make this work with a SUMPRODUCT/INDEX combination? Also, any suggestions for this same result using defined names would be appreciated.

Thank you,
John Countzler

I have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date - purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below.

One cell should contain the aggregate security purchase amount whose term is 1-90 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.

Any ideas? Thank you.

Any suggestions to work around the sum error?

Quote:

Runtime Error '1004'
unable to locate the sum property of the worksheetfunction class

Code:

Public Function CalcSheet()
Dim NoSales(0 To 11) As Range
Dim DriveOffs(0 To 11) As Range
Dim Voids(0 To 11) As Range
Dim Shortages(0 To 11) As Range
Dim tNoSales As Integer
Dim tDriveOffs As Currency
Dim tVoids As Currency
Dim tShortages As Currency
Dim X As Integer
Dim NSc As Integer
Dim DOc As Integer
Dim VOc As Integer
Dim SHc As Integer
Dim aNoSales As Integer
Dim aDriveOffs As Currency
Dim aVoids As Currency
Dim aShortages As Currency

    'Initialize Total and Average Variables
    aNoSales = 0
    aDriveOffs = 0
    aVoids = 0
    aShortages = 0
    tNoSales = 0
    tDriveOffs = 0
    tVoids = 0
    tShortages = 0

    'Populate Object Arrays
    Set NoSales(0) = Worksheets(1).Range("B3:B33")
    Set NoSales(1) = Worksheets(1).Range("F3:F33")
    Set NoSales(2) = Worksheets(1).Range("J3:J33")
    Set NoSales(3) = Worksheets(1).Range("N3:N33")
    Set NoSales(4) = Worksheets(1).Range("R3:R33")
    Set NoSales(5) = Worksheets(1).Range("V3: V33")
    Set NoSales(6) = Worksheets(1).Range("Z3:Z33")
    Set NoSales(7) = Worksheets(1).Range("AD3:AD33")
    Set NoSales(8) = Worksheets(1).Range("AH3:AH33")
    Set NoSales(9) = Worksheets(1).Range("AL3:AL33")
    Set NoSales(10) = Worksheets(1).Range("AP3:AP33")
    Set NoSales(11) = Worksheets(1).Range("AT3:AT33")
    
    Set DriveOffs(0) = Worksheets(1).Range("C3:C33")
    Set DriveOffs(1) = Worksheets(1).Range("G3:G33")
    Set DriveOffs(2) = Worksheets(1).Range("K3:K33")
    Set DriveOffs(3) = Worksheets(1).Range("O3:O33")
    Set DriveOffs(4) = Worksheets(1).Range("S3:S33")
    Set DriveOffs(5) = Worksheets(1).Range("W3:W33")
    Set DriveOffs(6) = Worksheets(1).Range("AA3:AA33")
    Set DriveOffs(7) = Worksheets(1).Range("AE3:AE33")
    Set DriveOffs(8) = Worksheets(1).Range("AI3:AI33")
    Set DriveOffs(9) = Worksheets(1).Range("AM3:AM33")
    Set DriveOffs(10) = Worksheets(1).Range("AQ3:AQ33")
    Set DriveOffs(11) = Worksheets(1).Range("AU3:AU33")
    
    Set Voids(0) = Worksheets(1).Range("D3:D33")
    Set Voids(1) = Worksheets(1).Range("H3:H33")
    Set Voids(2) = Worksheets(1).Range("L3:L33")
    Set Voids(3) = Worksheets(1).Range("P3:P33")
    Set Voids(4) = Worksheets(1).Range("T3:T33")
    Set Voids(5) = Worksheets(1).Range("X3:X33")
    Set Voids(6) = Worksheets(1).Range("AB3:AB33")
    Set Voids(7) = Worksheets(1).Range("AF3:AF33")
    Set Voids(8) = Worksheets(1).Range("AJ3:AJ33")
    Set Voids(9) = Worksheets(1).Range("AN3:AN33")
    Set Voids(10) = Worksheets(1).Range("AR3:AR33")
    Set Voids(11) = Worksheets(1).Range("AV3:AV33")
        
    Set Shortages(0) = Worksheets(1).Range("E3:E33")
    Set Shortages(1) = Worksheets(1).Range("I3:I33")
    Set Shortages(2) = Worksheets(1).Range("M3:M33")
    Set Shortages(3) = Worksheets(1).Range("Q3:Q33")
    Set Shortages(4) = Worksheets(1).Range("U3:U33")
    Set Shortages(5) = Worksheets(1).Range("Y3:Y33")
    Set Shortages(6) = Worksheets(1).Range("AC3:AC33")
    Set Shortages(7) = Worksheets(1).Range("AG3:AG33")
    Set Shortages(8) = Worksheets(1).Range("AK3:AK33")
    Set Shortages(9) = Worksheets(1).Range("AO3:AO33")
    Set Shortages(10) = Worksheets(1).Range("AS3:AS33")
    Set Shortages(11) = Worksheets(1).Range("AW3:AW33")
    
    For X = 0 To 11 Step 1

        If Application.WorksheetFunction.Sum(NoSales(X)) > 0 Then 'Check for no data
            NSc = NSc + Application.WorksheetFunction.Count(NoSales(X)) 'Count number of data entries
            tNoSales = tNoSales + Application.WorksheetFunction.Sum(NoSales(X)) 'Sum data
        End If
    
    Next X

    For X = 0 To 11 Step 1
        
        If Application.WorksheetFunction.Sum(DriveOffs(X)) > 0 Then 'Check for no data
            DOc = DOc + Application.WorksheetFunction.Count(DriveOffs(X)) 'Count number of data entries
            tDriveOffs = tDriveOffs + Application.WorksheetFunction.Sum(DriveOffs(X)) 'Sum data
        End If
    
    Next X

    For X = 0 To 11 Step 1

        If Application.WorksheetFunction.Sum(Voids(X)) > 0 Then 'Check for no data
            VOc = VOc + Application.WorksheetFunction.Count(Voids(X)) 'Count number of data entries
            tVoids = tVoids + Application.WorksheetFunction.Sum(Voids(X)) 'Sum data
        End If
    
    Next X

    For X = 0 To 11 Step 1

        If Application.WorksheetFunction.Sum(Shortages(X)) > 0 Then 'Check for no data
            SHc = SHc + Application.WorksheetFunction.Count(Shortages(X)) 'Count number of data entries
            tShortages = tShortages + Application.WorksheetFunction.Sum(Shortages(X)) 'Sum data
        End If
    
    Next X

    'Error Check before Dividing for Average
    If (tNoSales > 0) And (NSc > 0) Then aNoSales = tNoSales / NSc
    If (tDriveOffs > 0) And (DOc > 0) Then aDriveOffs = tDriveOffs / DOc
    If (tVoids > 0) And (VOc > 0) Then aVoids = tVoids / VOc
    If (tShortages > 0) And (SHc > 0) Then aShortages = tShortages / SHc

    'Insert Averages
    Worksheets(1).Range("B37").Value = aNoSales
    Worksheets(1).Range("C37").Value = aDriveOffs
    Worksheets(1).Range("D37").Value = aVoids
    Worksheets(1).Range("E37").Value = aShortages
    
    'Insert Totals
    Worksheets(1).Range("B38").Value = tNoSales
    Worksheets(1).Range("C38").Value = tDriveOffs
    Worksheets(1).Range("D38").Value = tVoids
    Worksheets(1).Range("E38").Value = tShortages
    
    'Release Objects
    Set NoSales(0) = Nothing
    Set NoSales(1) = Nothing
    Set NoSales(2) = Nothing
    Set NoSales(3) = Nothing
    Set NoSales(4) = Nothing
    Set NoSales(5) = Nothing
    Set NoSales(6) = Nothing
    Set NoSales(7) = Nothing
    Set NoSales(8) = Nothing
    Set NoSales(9) = Nothing
    Set NoSales(10) = Nothing
    Set NoSales(11) = Nothing
    
    Set DriveOffs(0) = Nothing
    Set DriveOffs(1) = Nothing
    Set DriveOffs(2) = Nothing
    Set DriveOffs(3) = Nothing
    Set DriveOffs(4) = Nothing
    Set DriveOffs(5) = Nothing
    Set DriveOffs(6) = Nothing
    Set DriveOffs(7) = Nothing
    Set DriveOffs(8) = Nothing
    Set DriveOffs(9) = Nothing
    Set DriveOffs(10) = Nothing
    Set DriveOffs(11) = Nothing
    
    Set Voids(0) = Nothing
    Set Voids(1) = Nothing
    Set Voids(2) = Nothing
    Set Voids(3) = Nothing
    Set Voids(4) = Nothing
    Set Voids(5) = Nothing
    Set Voids(6) = Nothing
    Set Voids(7) = Nothing
    Set Voids(8) = Nothing
    Set Voids(9) = Nothing
    Set Voids(10) = Nothing
    Set Voids(11) = Nothing
        
    Set Shortages(0) = Nothing
    Set Shortages(1) = Nothing
    Set Shortages(2) = Nothing
    Set Shortages(3) = Nothing
    Set Shortages(4) = Nothing
    Set Shortages(5) = Nothing
    Set Shortages(6) = Nothing
    Set Shortages(7) = Nothing
    Set Shortages(8) = Nothing
    Set Shortages(9) = Nothing
    Set Shortages(10) = Nothing
    Set Shortages(11) = Nothing

End Function

Hello,

How do you pass an object to a class or function.

I've tried this:

Class

Code:

Private objListBox As listbox
Private intY As Long

Public Function items()
    For intY = 0 To objListBox.ListCount - 1
        If objListBox.Select(intY) = True Then
            MsgBox objListBox.List(intY)
        End If
    Next intY
End Function

Public Property Set SetObj(ByVal NewObj As listbox)
    Set objListBox = NewObj
End Property

Module

Code:

Option Explicit

Private Sub cmdRow_Click()

    Dim GetList As Class1
    
    GetList.SetObj = lstpvtdata ' This is a listbox in a from
    
    GetList.items
    
End Sub

But get an invalid use of property error.

Hello,

is it possible to add a description/summary to functions that you write?

I want to make a re-usable Class and want the intellisense to show a brief description of the functions that i wrap up in the class.

I can add comments within the class but that wouln't really help whilst using the class.

Thanks