Posts by drnaphtali

    Hi guys,

    I am busy building a design sheet which will be used for different projects.

    I have two different sheets. On the first sheet I insert data (Room number, Room name, Room Area, etc)
    I then have a second sheet which contains alot of forumulas for information on each room.

    Is there a more simple way than using macro's, where if I add (or delete!) data on table one the workbook automatically updates the table on sheet two (inserting rows, and automatically put in the formulas in the approproate cells)?

    Please let me know if I should clarify.

    Thank you and regards,

    Re: Sum of elements of a collection class method

    I think this is exactly what I need!

    Now I can just write a method for cRooms to return a specific property of a desired room (ie objSystem.GetRoomProperty(eProperties.SupplyAir, "Foyer")) where eProperties is an enum with all the properties listed.

    Thank you so much for your time and effort vwankerl! It is very very much appreciated.

    Re: Sum of elements of a collection class method

    My other question is say I have a class cRoomCooling with SupplyAir and ReturnAir as properties.

    Dim objRoom as cRoomCooling
    Dim objAllRooms as Collection

    Now when I write a function where I add say 3 rooms to the collection; if I watch the objAllRooms I see 3 items, each with their two properties, ie SupplyAir and ReturnAir. Isnt there someway to access just a certain property of a

    for each item in objAllrooms.SupplyAir

    or what I would like more is isnt there a way to pass through one property of all the items in a collection and then maby have this as an input to the cSystemCooling method??

    dim objSystem as cSystemCooling

    Re: Sum of elements of a collection class method

    Hi Vwankerl,

    Thanks for the great reply! Defining the collection within the cSystemCooling class does make sense and helps somewhat.

    You are correct in saying that cSystemCooling is an aggregate, but first prize would be however if I can define and access the Collection withine the function itself.

    The function I am writing is a long and complicated affair, and it will be difficult to explain exactly what I want to do without writing a thesis. But to give you the just of it, I will calculate an initial flow for each room (and several other properties), get the sum of it, analize each property with a for each loop, update it if necesary, get the sum, analize, update, etc untill I achieve a 'neutral' value. The output would then be the flow of ONE of the rooms.

    Going to play around with your suggestions to try and wrap my brain around the problem more, but like I said, at the moment I think first prize is if I can define the collection in my test function and send the collection through as an input to the cSystemcooling method.

    Hi guys,

    Not sure if the title makes sense; I did not really know how to describe my problem is so few words.

    I have a system with some rooms. Each room has various properties such as airflows. So I created a class module for the rooms, named cRoomCooling.

    I also then created a class describing the system data, named cSystemCooling

    (Both the room class and system class have more properties, i just simplified it)

    I have created the following TEST function:

    The objSystem.SumOfSupplyAir(objSupplyAir) is described in the following method, under the cSystemCoolingclass, which is used to calculate the sum of all the flowrates of all the rooms:

    Public Sub SumOfSupplyAir(SupplyAir As Collection)
        pSupplyAir = 0
        Dim Item As Object
        For Each Item In SupplyAir
            pSupplyAir = pSupplyAir + Item.SupplyAir
        Next Item
    End Sub

    I will need to write quote a hectic function so I want to create a method to get the Sum, so I do not type "For Each" everytime I want to calculate a sum.

    My first question is, is it bad coding style to dim Item as an object in the method? When I type in


    I do not get the dropdown showing me all the class members.

    My second question is, I do not want to create a collection for each property. How do I pass certain members of an item in a collection through to a sub or function?

    I hope I make sense!


    Re: Excel crashes when opening multiple documents

    It might be worth noting that, the design documents where the summary workbook gets its information from, is quite large with several complex calculations each. I do not have a problem opening each individual document though. Its just that if I have a few open at the same time, my computer becomes extremely slow.


    Re: Excel crashes when opening multiple documents

    Hey Rob,

    Thanks again for your time. I did as you suggested. The code did not break anywhere.

    What I did do though, is to put a break on the line

    Workbooks.Open ThisWorkbook.Path & "\" & Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 1).Value

    and when i press "F8" (the 6th time) it doesnt move to the next line, it just CRASHES.

    Mind boggling stuff...


    Re: Excel crashes when opening multiple documents

    In the insertData sub I set the ranges equal to nothing. Maybe its best to ignore the update_click sub, since I only updated my code in the button for debuggin purposes.

    That is all the code I have on this workbook. The problem has to be there somewhere. Thank you for all the help Rob.


    Re: Excel crashes when opening multiple documents

    Maby the leak is in the class methods which loops through the columns of the external sheets? I do not know why though. I know for a fact the program crashes AS SOON AS THE command is run. It does not even try to enter the rest of the code.


    Re: Excel crashes when opening multiple documents

    Hello Rob, thank you for the reply mate.

    Here is my class

    The program physically crashes "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience ..."
    It then closes, opens back up and displayes a recovered document.

    I forgot to add, when I run the button 5 times, and restart the document, I can add another 5 sets of documents.

    Thank you so much for the help, this proplem is very frustrating.

    Hello all,

    I wrote a macro in a "Summary" workbook, which loops through design documents (where the name of the design document is written in a column of the summary workbook, and each document is denoted by

    Workbooks(workbookname).Worksheets("Sheet1").Cells(i, 1).Value

    in my code) and fetch some data from each workbook and inserts the value in a cell. Each of the design documents have the same format.

    My problem is that when the macro tries to open the 6th document, excel crashes. I've tried doing this on a seperate machine and the same happens (i think on the other machine it crashed after the 8th document). I then tried adding a button which replaces the loop. Instead of looping, each time the button is pressed it adds the information of the next document, but to no avail it still crashed.

    I tried looking into methods of clearing memory since I suspect that this is the problem.

    My code to follow. The code for the button is the same, less the for loop. I created a class which fetches the relevant data from each document, please let me know if you would like this included.

    Below is the code for the button which includes an attempt to clear the memory:

    Re: Conditional formatting, Highlighting of row A to row F when of active cell

    Hi steph,
    Your question is very unclear for me. Do you want a macro which will loop through, say column A, to see to check the due dates, and if the date is late, highlite the row (columns A to F)? Does the code which you provided not work? I see you added the macro to add the conditional format column B.