Posts by TheGlovner

    Bit of context first.

    So I have data held at three levels (Scheme Level, Policy Level, Increment Level).

    I've got three collections to represent each of the levels. Each one of these collections holds a number of class objects created to represent the data, I need, found at each level.

    First stage of the process collects the data from a number of Binary workbooks, so it opens the sheet, extracts the row into the class object, adds the class object to the collection and moves to the next row, on finishing with a sheet it will close the workbook and open the next binary workbook and repeat the process until all workbooks have been fully extracted into the collection.

    This part of the process appears to work exactly as intended with no discernible issues.

    There will be some functional steps processes next to calculate various outputs and pass these to some other collections in order to output to the sheet in one go. This section hasn't been coded yet but I was more interested in handling the data correctly first.

    Finally after all the functional process has completed I would just end things.

    When I first tried running it against a single external sheet everything was fine. However, when I ramped the volumes up I was getting excel hanging.

    Performed some debugging and the hang was occurring after the final End Sub command (strange I thought).

    Had a thought that it may have something to do with the size of the collections.

    Tried just setting the collection to = Nothing before the end sub but it appears to hang here too. I assume the Collection = Nothing command is probably the same action that occurs at the end sub line so no great surprise that the same thing seems to be happening. Unfortunately as there is no way to track the process of it clearing the collection in this way I couldn't be sure this is what was happening.

    Just to prove it to myself I created a For loop to move through the collection and remove the items one at a time and update a statusbar message to track the process. This seems to validate my thoughts as once it cleared everything out it ended the process with no issues.

    However, we are in the region of 7 million items in a collection so it's taking a good bit of time to finish the process when a single item is removed at a time.

    Just wondered if anyone had any way I hadn't thought of to quickly clear out the collections that I hadn't thought of, or a more efficient way to handle the data than a collection?

    NB: I appreciate that using Access to hold the raw data would probably be better all round given the amount of data, but that option is out due to company constraints.

    Re: VBA Macro to save file in a variable date folder and name file

    To explain the changes I've made:

    So I've moved everything (components of your FilePath/FileName) into Variables and used these throughout the code, just a bit cleaner to write and read this way (also less typing).

    I removed the ".xlsm" from your Filepath in the SaveAs Method (you don't need to dictate this, this has already been explicitly done by the command "FileFormat:=xlOpenXMLWorkbookMacroEnabled" in the SaveAs Method)

    I also changed your folder format for the day part due to the fact that you need to go through the Year & Month folder to get there, so at the point of you reaching the day folders the Year & Month are irrelevant to the actual name.

    If there is a good reason for formatting it the way you have you can always alter the strDay variable with a single line to be prefixed with year and month using something like:

    strDay = strYear & "." & strMonth & "." strDay

    I also added a variable to represent the name of your file.

    Your SaveAs command doesn't actually give a file name, it gets as far as the month folder and interprets your day as the name of the file followed by .xlsm.

    What you want to do is dictate the full path right down to the day, then provide the actual filename. This will be why you were saving in the Month folder before (I'd wager with a filename something like Year.Month.Day.xlsm

    Re: VBA Macro to save file in a variable date folder and name file

    Give this a try:

    Re: Que regarding conditional formatting in ms excel

    I think when you are writing your conditional formatting formula (I'm assuming that's how you've done it) that excel automatically creates absolute references in your formula, You'd probably want to remove these in the appropriate place so when you fill your conditional formatting the references in the formula move appropriately.

    I'll lay out the context first.

    Basically I have a number of processes making up the functionality of a data analysis tool I've been building to churn through the data we have been sent.

    Makes sense at this stage while I'm in the guts of it to be working on the individual steps one at a time.

    However, when I hand this over for use on the project I don't really want the users to have to worry about stuff down in the guts (all the various data that has to be set/extracted for it to work right).

    So I'm summarising the process steps on a front page, I am then trying to add ActiveX Command Buttons on that page that when pressed will pass a range name (as a string to be set in the called routine) to the following piece of incomplete code:

    So the idea is that the Range passed in (and set on arrival) contains the steps needed to complete that particular process in sequential order.

    So the For/Next section is running through the range getting each step and then looking for the appropriate button that controls access to that step. The reason I'm not going straight to the actual processes rather than utilising the ActiveX button Clicks is because I have a group of other Admin Type processes in each of the button click routines (toggling screenupdating, recording process start/stop/elapsed times etc), example below:

    So really what I'm trying to achieve is to press the ActiveX Command Button which I have the name for held as a string and have the associated code for that button run before returning control to the Routine shown about.

    Re: Using regEx to validate a password?

    It does, what a handy little object it is once you get your head round it.

    Which you've managed to get me to do in 10 minutes after an hour of trawling other online resources got me no closer.

    So big thanks to S O and StephenR for their help on this one.

    Re: Using regEx to validate a password?

    Okay the more efficient UDF makes perfect sense, thanks muchly for that one very much appreciated.

    Just the logic of that pattern that's throwing me.

    As I'm reading it:

    ^ start of the string
    {8,} 8 characters in the string
    $ end of the string

    So, yeah, big bit in the middle making no sense at all.

    Re: Using regEx to validate a password?

    "aaaaaaaa" = False
    "5aaaaaaa" = False
    "Aaaaaaaa" = False
    "5aaaaaaA" = True
    "Aaaaaaa5" = True

    Looks like that's cracked it. If you have the time could you possibly explain what the hell is going on in that pattern, obviously it's a fair jump from my [A-Z][0-9] which I now understand to have been explicitly looking for an Upper Case Letter followed immediately by a Digit.

    Re: Using regEx to validate a password?

    Just noticed you've incorporated the 8 Character Rule into the regExp where I was handling that outside just with len(string) = 8.

    Give me a moment and I'll retry the above suggestions with the 8 characters in the string.

    Re: Using regEx to validate a password?

    Done some more testing with it.

    When I pass through the following strings for the above pattern I get the following results:

    "a" = False
    "A" = False
    "5" = False
    "A5" = True
    "5A" = False
    "Aa5" = False

    The location of these characters within the string is unimportant, I just need to know that there is at least one instance of each within the string. But it looks like the way I've expressed the pattern must take into account the order since when it's an Upper Case Letter followed by a number it passes back true (as this reflects the [A-Z][0-9] from the string) but when they are flipped it gives a false. Also it doesn't seem to ignore the lower case letters that may also be in the string.

    Starting to think regExp isn't the solution to my problem.

    I think the regular expressions may be what I need to validate a user password but not entirely sure what I'm doing with regards to creating the patterns.

    Anyone with a bit more experience that can perhaps put me on the right track?

    Here is the code I have currently. It sits inside a userform module so the txtPassword it's referencing is one of the text boxes on the userform.

    So what I'm trying to do is establish if the string entered into the text box for passwords contains at least 1 upper case character and at least 1 digit and is 8 characters long.

    But I'm getting inconsistent results for the regExp.Test Method. Which is leading me to believe I don't understand completely how it's working and whether my pattern will return what I expect.

    Anybody know how this works?

    Re: SUMIF 2 Critera

    I've found in the past that any time you are about to consider using Sumif(s) or Countiif(s) you can probably use a Pivot Table to solve the issue a lot quicker.

    Is this possible does someone know?

    I figure it must be considering the various built in objects that also contain collections, just can't figure out how to pull it all together.

    So to explain the issue:

    I'm moving data between sheets, I have a Dashboard that sits between an Estimation Spreadsheet (Estimates) and a Task Tracking Spreadsheet (Actuals). The dashboard controls the creation of tasks and pulls various data from the other sheets. So for this reason it needs to push or pull data with either the Estimation or Task Tracker.

    Difficulty can come when it's not the initial export to these two sheets but an update.

    In these instances I need to also grab the data that is already on the Target Workbook and amalgamate this with the data coming over from the dashboard.

    I can do this fine on the estimation side of things, but on the Task Tracker their is an undefined number of columns representing the actual Weeks that the Actual Times were spent (week ending format so Friday's date represented as dd/mm/yyyy). So there could be 1 there could be 500 depending on how long the project runs.

    So to get round the unknown element of the Task Tracker I thought about holding each populated column within a collection inside the class.

    So I have a class (clsTaskTrackingData) to represent all data on a row, each of these objects are placed in a collection to record all the rows of data.

    But within this class I also have a collection as one of the properties:

    What I have then done is created another class to represent the WeekEnding Date (which is was going to use as an index to get the data back from the class) and the Number of days spend on that task (which the clsTaskTrackingData represents, the class that this collection is a property of).

    I just can't get my head round the syntax of putting this "sub" class into the collection in the larger class object and then trying to retrieve the data again.

    I'm guessing the let must be something more like:

    Public Property Let colWeeksTasks(ByRef clsTimeSpendForWeek As TimeSpendForWeek)
        pColWeeksTasks.Add clsTimeSpendForWeek, clsTimeSpendForWeek.strWeekRefKey
    End Property

    Any tips/ideas/even half a clue about the subject I'm slavering my way through?