# Posts by TheGlovner

• ## Algorithm / Assign Max Value

Re: Algorithm / Assign Max Value

Couple of different ways to evaluate for blank cells.

For one one I wouldn't ever use "". Instead use the keyword vbnullstring.

Alternatively you couod check the actual actual length of the contents of the field.

So something like:

Code
``if len(cellReference) = 0 then TRUE``

I believe checking the length of the field is more efficient that actual trying to evaluate the field itself.

You basically need to rewrite the function function to deal with the blanks or evaluate the blanks using a different function/method.

Tge current function only handles the cell having some contents.

• ## Algorithm / Assign Max Value

Re: Algorithm / Assign Max Value

Quote from Sp0ns;790862

The problem is that more combinations of the criteria can exist; for instance:

Sy in Col I, D in Col D and SI in Col G would trigger cnt + 2 and cnt +3
But it should only trigger cnt + 3 because for each row it should only count the highest value possible

Thats why I believe that the select case statement would be a better fit.

It evaluates the value and accepts the first case that is considered true. The only thing you need to ensure is that the cases are ordered correctly so your highest priority is evaluated first.

• ## Algorithm / Assign Max Value

Re: Algorithm / Assign Max Value

I can't access the book just now as at work, but first thing I'd consider doing is dropping your multiple "ifs" since it seems that only one combination of the criteria can exist so there is little point evaluating it so many times.

I'd move to a case statement to achieve the same thing, also makes it easier to read, something like:

• ## Weighted Average with ReDim preserve

Re: Weighted Average with ReDim preserve

I can't access the worksheets just now as I'm at work.

But for starters I'd be more inclined to use a combination of =sumproduct and =sum to do the calculation.

Assuming the data is actually on the sheet somewhere:

[TABLE="class: grid, width: 500"]

[tr]

[td]

Row/Columns

[/td]

[td]

A

[/td]

[td]

B

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

33

[/td]

[td]

5.00

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

23

[/td]

[td]

3.00

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

25

[/td]

[td]

3.00

[/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

27

[/td]

[td]

5.00

[/td]

[/tr]

[/TABLE]

=SUMPRODUCT(A1:A4,B1:B4)/SUM(B1:B4)

Will return the same as:

=((33 * 5,00) + (23 * 3,00) + (25 * 3,00) + (27 * 5,00)) / (5,00 + 3,00 + 3,00 + 5,00)

Or in a parameterised format:

=((A1* B1) + (A2 * B2) + (A3 * B3) + (A4 * B4)) / (B1 + B2 + B3 + B4)

• ## Hide formulas without locking sheet -- formulas are arrays though

Re: Hide formulas without locking sheet -- formulas are arrays though

I think you just need to change your Range (Cell) property to .FormulaArray, rather than .Formula.

• ## Ramdomize columns in each row

Re: Ramdomize columns in each row

Could try this code, making a few assumptions in it, it outputs the results 5 columns to the right of the original piece of data. Also assumes that there could be many rows but only four columns and all your data is found on sheet1.

• ## Save a file as a pdf to folders dependent on the current date

Re: Save a file as a pdf to folders dependent on the current date

I'd probably just go with something like:

Code
``replace(cstr(date+2), "/", ".")``

At the end of your file path/location part.

This will give you the current date that code is being fired, add two days to it, switch it into a string representation of that date and replace the "/" with "." (since windows won't allow "/" to make up part of a filename).

So something like:

Code
``\2017\04 APR\09 APR & replace(cstr(date+2), "/", ".") & .xlsx``

Although the location part of the file path may well be a variable value rather than a hardcoded text string in your code.

• ## Values populating all Items in a collection of class objects

Re: Values populating all Items in a collection of class objects

And if it helps at all here is all the code from that module (there are a lot of other modules that come before it but the problem is occurring in the routine posted above so unlikely that anything else within other modules is affecting things):

• ## Values populating all Items in a collection of class objects

Re: Values populating all Items in a collection of class objects

Fund Extract Class Instances:

[TABLE="width: 500"]

[tr]

[td]

Fund Code

[/td]

[td]

Fund Series

[/td]

[/tr]

[tr]

[td]

ET

[/td]

[td]

2

[/td]

[/tr]

[tr]

[td]

ET

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td]

WB

[/td]

[td]

5

[/td]

[/tr]

[/TABLE]

Price Instances within a collection in the (ET, 2) Fund Extract Class

[TABLE="width: 500"]

[tr]

[td]

Date

[/td]

[td]

Example Price Data

[/td]

[/tr]

[tr]

[td]

01/01/2017

[/td]

[td]

100.00

[/td]

[/tr]

[tr]

[td]

02/01/2017

[/td]

[td]

101.00

[/td]

[/tr]

[/TABLE]

Price Instances within a collection in the (WB, 5) Fund Extract Class

[TABLE="width: 500"]

[tr]

[td]

Date

[/td]

[td]

Example Price Data

[/td]

[/tr]

[tr]

[td]

01/01/2017

[/td]

[td]

200.00

[/td]

[/tr]

[tr]

[td]

02/01/2017

[/td]

[td]

201.00

[/td]

[/tr]

[/TABLE]

So in the outer loop we start with the Output Template for the ET, 2 Fund/Series:

Code
``For Each clsFundOutput In colRegionalOutputTemplate``

We set an object to represent the extract version of that data:

Code
``Set clsFundExtract = clsRegionalData.FundCollection.Item(clsFundOutput.FundCode & clsFundOutput.FundSeries)``

Then the secondary loop starts which should move through the different instances of the date level data:

Code
``For Each clsPriceOutput In clsFundOutput.DateLevelCollection``

We pass the price from the extract class into the output class for that particular date for that particular Fund/Series:

Code
``clsPriceOutput.Actual6DPUnsmoothed = clsPriceExtract.Unsmoothed6dp``

So once all the routine has completed it's two loops I would be expecting to see the price data in the table above aligned to the appropriate Fund/Series in the output classes.

But the actual result I'm getting is:

[TABLE="width: 500"]

[tr]

[td]

Date

[/td]

[td]

Example Price Data

[/td]

[/tr]

[tr]

[td]

01/01/2017

[/td]

[td]

200.00

[/td]

[/tr]

[tr]

[td]

02/01/2017

[/td]

[td]

201.00

[/td]

[/tr]

[/TABLE]

For everything, Which comes from the WB, 5 data, as this is the last one in the collection to be processed it would seem everything is being overwritten in the colRegionalOutputTemplate rather than the current instance from the colRegionalOutputTemplate represented by clsFundOutput.

• ## Values populating all Items in a collection of class objects

I'm not sure if someone will be able to help me here but I'm tearing my hair out so just trying to see if someone else can see the trees (the wood is restricting my vision of them it seems).

Problem is though, the full code is huge (and all working as expected) except for what's going on in this subroutine, I can't post the full thing due to company sensitive data. So I'll explain the issue drop the bit of code where the issue is happening and see if anyone can spot where I'm going wrong. Appreciate this may be difficult though given the limited amount I can put up of a much larger application.

So here is the code in question:

So essentially what is happening is that earlier I collected a load of data through various SQL calls, the data is held at the date that applies from and is then superseded by any data held at a later date.

Where the output template that it is being passed to is holding data on a daily basis. So I'm trying to create a template where all the daily data is held daily (obviously) like the prices, but then any data that is held over a range greater than a day is output to the daily template this is to make the subsequent calculations that will be coming easier to perform in a loop as all the data needed in each instance of the class should be held within it (without having to call it based on different keys from a number of different levels in the hierarchy of Classes/Collections.

I am expecting the outer for loop to pass through each Fund/Fund Series which it appears to be doing. On moving through each Fund/Fund Series instance of the Level3_RegionalFundOutput Class I set an instance of the clsFundExtract object (which is an instance of the Level4_RegionalFundExract class, which holds the data extracted from the DB2 tables).

So far so good.

The next for loop should be moving through each of the dates in the range for the current Level3_RegionalFundOutput which clsFundOutput is representing. So Level3_RegionalFundOutput class contains a collection of Level4_RegionalPriceOutput class objects. Which hold prices (and some other stuff) which is differentiated by date.

Same idea as the previous loop, I set the clsPriceExtract class as the appropriate instance of Level5_RegionalPricesExtract.

Within that loop the data extracted from the DB2 tables (Level5_RegionalPricesExtract) is passed into the Level4_RegionalPriceOutput class.

However, when that data is being passed from the extract class to the output class it's passing it to every instance of the price output class for all funds in the colRegionalOutputTemplate.

Hopefully that makes sense, I can try and pass in a worked example if that would help further.

• ## Save a file as a pdf to folders dependent on the current date

Re: Save a file as a pdf to folders dependent on the current date

If I'm understanding your problem correctly (i.e. you want the file name to start with the current date + 2 days and then be followed by something in Worksheets("ATO Days").Range("D2").Value).

Unfortunately I'm in work just now and can't download the file to confirm if this what you are trying to do, the description is a bit sketchy as to why you have two dates in your example (2017\04 Apr\09 Apr).

First thing I'd say is that including backslashes in your file name is interpreted as child folders by Windows so "2017\04 Apr\09 Apr" isn't the name of a file, it's saying in the 2017 folder, then in the 04 Apr folder, then in the 09 Apr folder.

Generally when using full dates in a file name people will use periods as the delimiter as windows doesn't take this as an instruction of something else (like the backspace).

The other common method is to flip the date into a big endian format with no delimiters as this acts as a serial number than can easily be ordered (unlike using the period), big endian meaning for example 20170411 for today's date. Anyway, I digress. to get today's date + 2 days you just need to use:

Code
``(Date + 2)``

"Date" returns the current system date and "+ 2" adds two days to it.

I suspect after that you want to format it a particular style but I'd need to understand if you are actually putting this in a folder structure of Year > Day & Month as your example suggests or not.

• ## Trying to write "2nd highest" score in addition to MaxScore.

Re: Trying to write &quot;2nd highest&quot; score in addition to MaxScore.

Quote from wittonline;790238

Humm...very interesting.

I'm trying to incorporate it into a macro though. I've never been able to properly use functions in a macro.

If it's a function you would use on the actual spreadsheet you usually need to inform the VB that's what you intend to use, for some functions on the spreadsheet there is sometimes a VBA equivalent, but it can also somehow be subtly different from the sheet version.

To instruct the VB that you are using the worksheet version of a function prefix it with:

Code
``Application.WorksheetFunction.``

So for the Large example given above it would be

Code
``VariableToPassValueInto = Application.WorksheetFunction.Large(YourRange, 2)``
• ## problems with internet explorer 10 and vba for frames in a intranet site

Re: problems with internet explorer 10 and vba for frames in a intranet site

Difficult to say without having access to the site, but my guess would be that the site no longer has "frame1".

Can you get access to the HTML code for the site? This may possibly show you the correct reference for the part of the site that contains the data you are after (try accessing it through the "Data>Get External Data>From Web" from the main Excel ribbon, this may give clues to what the new alias of the frame/table is).

Or alternatively does the data you are trying to extract from the site live somewhere else (i.e. a database that feeds the intranet site) as it may be more efficient and less open to the possibility of your "reference material" (the IE site) being altered by forces outwith your control.

We have a similar problem with a lot of tools in my work and as I've been going through them trying to future proof things I'm more often than not finding that the original code never targets the actual source of the data, just where the original coder was aware that the data existed, this is always open to people changing the screens which will in turn mess up your code. Going back to the original source usually stops this from happening, or certainly makes it less likely.

Or another thing to look at is what kind of object is "HTMLDoc" being set up as when the variables/objects are being dimensioned? Has this changed between IE8 and IE10? Do you possibly need a new object/reference to recognise IE10?

• ## Code Procedure Calls/Arguement print out

Re: Code Procedure Calls/Arguement print out

It's been a while, but I've had a little stint in hospital and been away from all this for a bit, so I'll say a belated thank you for this info now.

Cheers!

• ## Copy and Transfer Data from Dynamic Query Workbook to another workbook

Re: Copy and Transfer Data from Dynamic Query Workbook to another workbook

I'd like to help, but my American in-laws and Cubs supporters may never forgive me for helping someone with the screen name whtesox.

Only joking, well not really, but I'll try and take a look when I get home, can't download the files on the work PC to get a better idea of the issue.

• ## calculate range without looping

Re: calculate range without looping

Taken a bash at throwing a quick example of the loop together:

• ## calculate range without looping

Re: calculate range without looping

I think this probably needs a bit more explanation but I'll take a bash.

I assume your UDF is called CountTitles and requires inputs of:

(W as range, X as range, Y as range, Z as range)

So the opening line of your UDF probably looks something like:

Code
``Public Function CountTitles(ByRef W as range, ByRef X as range, ByRef Y as range, ByRef Z as range) as Long``

I'm not sure what your output data format is from your UDF so I'm guessing long.

So you have a UDF set up to accept those values and obviously apply some processing based on what's passed in. But you have the values hardcoded in your call of the UDF. So it's always going to produce the same answer based on those values.

I'm not sure why you are so against a loop, if this is a misunderstanding on your part then you would create a loop which would move through the different values in some way (say setting up a collection of ranges and looping through this so every pass through the loop it would move to the next range and this would be passed to the UDF instead of the hardcoded ranges you currently have).

If you are totally against using a loop though and you want to continue down the hardcoding route (which I'd usually recommend against unless there was some solid foundation for the decision) you would have to repeat the line of code over all the different range permutations you wanted the UDF to calculate.

Based on my understanding though it really feels like the best solution lies with a loop and switching your hardcoded values into variables.

• ## to have multiple countdown timer at th same time

Re: to have multiple countdown timer at th same time

These threads are for VBA/Macro solutions, if it's explicitly a worksheet function solution you are looking for you may want to move the thread to the appropriate area.

http://www.ozgrid.com/forum/forumdisplay.php?f=160

• ## What's the best way to prevent users from changing margins?

Re: What's the best way to prevent users from changing margins?

I believe something along the following lines may work:

Code
``````Dim MyPrint As CommandBar
Set MyPrint = Application.CommandBars("Worksheet Menu bar")
MyPrint.Controls("&File").Controls("Print Pre&view").Enabled = False``````
• ## Large Collections causing issues on ending the subroutine.

Re: Large Collections causing issues on ending the subroutine.

I think I've managed to get to the bottom of this myself.

Looks like there is probably some memory limitations going on, as it was starting to fail to open the external workbooks when the full population was put in.

So to address the time out issues being caused as well as the issue with opening the later workbooks in the process I've switched the way it was being processed so a single external workbook for the Contract and the Increment Level data matches with it's content. The functional steps are then carried out and the collections cleared before moving to the next external Contract and Increment Level Workbooks.

Obviously adds a little bit of overhead with processing time since the output to the sheet is being performed multiple times instead of just once at the end but it seems to fix all the other issues I was encountering. But I'm probably saving a bit by clearing the collections after each external workbook is processed as the clear collection process appeared to take a lot longer when it contained all the data as opposed to the amalgamated time of clearing it in stages.