Highline Excel Class 18: Subtotals

hello,
i have a list of data which i've formatted as a pivot table.
i have grouped the data into 4 sections and subtotalled them:
sales, material, labour, expenses.

problem 1: for the costs (material, labour, expenses), i want to keep their subtotals, Plus group them together to get a 'total costs' line. So i'll have subtotal lines for total material/labour/expenses, plus a line at the bottom called 'total costs' which is the total of material/labour/expenses subtotals.

problem 2: i want another line, under total costs, called 'margin'. This would be the sales subtotal Minus the Total costs line ,or if we can't solve problem 1, sales subtotal minus material/labour/expenses subtotals.

ive fiddled for ages and just can't seem to do it. any ideas? i will attach the spreadsheet when i get home.

I some but not all spreadsheets, my nested subtotal (slsperson) is placed
below the primary subtotal (branch). It has only happened to me since
upgrading to Excel 2003.

I sort by Branch, slsperson, customer. Then Primary subtotal is branch
(checks on replace current subtotals and summary below) and all is fine. Then
when I do subtotal on Slsperson it will place (above items unchecked) the
slsperson subtotal BELOW the branch. And also the levels (1,2,3) are not
displaying properly.

Does anyone have a suggestion for me?

Hi all

I'm trying to improve my application by using class modules for a more object-oriented approach...
So far, it went quite well, but I'm trying some more complicated stuff now, and there is some behaviour I don't understand. I hope someone can point out my mistakes...

I have a class module A, which has as private members (a.o.) two instances of class B objects. That all works fine. Then I added class module C, also with two private instances of class D objects.
In class C, I created a method that uses an instance of class A as a parameter. I found out that passing such an instance as parameter can be done only as Variant, and then re-setting it internally to the correct type.
So far so good, here's the code of the method:
Code:

 
'METHOD Compare Structured Boms
Public Sub Compare(UploadBoms As Variant)
    Set m_Uploads = UploadBoms
    Stop
    If m_Uploads.m_Bom1.LastLine > m_Uploads.m_Bom2.LastLine Then
        m_UploadsLastLine = m_Uploads.m_Bom1.LastLine
    Else
        m_UploadsLastLine = m_Uploads.m_Bom2.LastLine
    End If
End Sub

m_Uploads is the private member in class C that contains the passed instance UploadBoms of class A. The m_Bom1 and m_Bom2 are the private instances of class D.
If I comment out the If-Then-Else lines, this method works. At the stop, I can inspect the locals-window for the active variables/objects. The m_Uploads object exists and has all expected members. In the Immediate window I can enter for instance:
?m_Uploads.m_Bom1.LastLine
and it gives me the expected result.

But when I don't comment out the If-Then-Else lines, I get an error at the If-line, "Compile Error: Method or data member not found", with the m_Bom1 highlighted. In the locals window, I can see that the m_Uploads is not yet initialized...
This member however worked perfectly when I commented out the lines and inspected the locals-window???

What am I doing wrong here?

Thanks in advance for any help...

I am having a problem when using the automatic subtotal tool. I am
subtotaling twice, but on the second subtotal, some of the subtotals for the
second item are showing up below the subtotals for the first item. This
happens when there is only one second item for the first item. It then
affects all highest subtotals from then on.

For example:
Sports Football NFC
Sports Football AFC
Sports Total Count 2
Football Total Count 2

(rather than)
Sports Football NFC
Sports Football AFC
Football Total Count 2
Sports Total Count 2

Please advise if there's a workaround or if this is an Excel bug.

Thanks,
Rob

Hi folks,
How do I create a "child" class within a user class?
I have created a class object, called "CompClass", which has a property called "frequency".

Code:

Public Property Get frequency() As Double
 
    frequency = pFrequency
 
End Property
 
Public Property Let frequency(value As Double)
 
    pFrequency = value
           
End Property

This is used in a forecasting model. I now want to refine the model by assigning different frequencies based on region. So I want to create a child class (not sure if child class is the correct term here) of the CompClass with its own set of properties.

What I eventually want to end up being able to do is this:

Code:

CompClass.frequency.London = 12
CompClass.frequency.Glasgow  = 5

All help is appreciated and if anyone has any links to any tutorials covering this sort of thing they would be much appreciated.

Working in Excel 2000 on a rather large database.

I know that upon creating the class instance one could assign it to a public variable so it can be seen from the Class module and could be set to Nothing from there. However , I want to make this foolproof and make it work for private or public Class instances.

Regards.

I've just started to learn how to use classes, so before I explain my problem here, does anyone know of any online resources to learn more about classes in VBA? I read this already: http://www.cpearson.com/excel/Classes.aspx.


Anyway, here's my problem. I've created a class called CStock. It has 5 standard properties (with Get/Let methods) as explained in that article above. Nothing fancy here.

I also want to create a CPortfolio class that has 3 properties: pName, pNumberofPositions, and pHoldings. the first 2 are stardard, but the last one is different. I want it to somehow keep a collection of stocks. I tried putting:

Code:

Private pHoldings As Collection
Public Property Get Holdings() As Collection
    Holdings = pHoldings
End Property

Public Property Let Holdings(Value As Collection)
    pHoldings = Value
End Property

in the CPortfolio module. and then i tried calling:

Code:

port.Holdings.Add stk
port.Holdings.Add stk2

in a regular sub, but the sub goes into the Get instead of the Let and i get "Complie Error: Argument Not Optional"


Anyone know what I'm trying to do here? Cuz I'm not sure I even know. Also, after I get this working, I want to create a CBook class that holds a bunch of portfolios in the same manner. am i going about this the wrong way?

Thanks,
Joe

How do I add subtotals to columns instead of rows

I am running EXCEL 2003 in Windows XP. When setting up and/or retrieving
subtotals, I am getting a very slow response, -it sometimes takes a few
minutes.
I alsi experience very slow response when inserting page breaks, and pageing
down