Posts by filo65

    Re: Pass Element Of User-Defined Data Type

    Does something like this work for you?


    Re: Vlookup Causing Slow Calculations



    Native Excel Functions are ALWAYS much faster than a replacement Custom Function

    I don't agree with you; certainly they are more practical because they save you plenty of typing, but please take a look at this example:

    Custom vs Built-in: 14:1 Speed gain.


    Re: Vba Code To Enter Formula


    probably it is faster entering the formula without loop ( if you enter only twice ).

    anyway try:

    Sub Do_It() 
        ActiveCell.Value = "=If(ISERROR(INDIRECT("& cells(i,j) &"!F:F"")),0,  COUNTIF(INDIRECT("& cells(i,j) &""!F:F""),""A""))" 
    End Sub


    Often it is necessary to reserve one or more columns to holiday lists, and afterward one has to maintain them and keep them up-to-date or delete the past ones. When this has to be done on more than one spreadsheet can became a pretty unpleasant task.

    Here is a fast way to get the official holidays calculated from now ( actually from 1900 ) to 2500, including managing week-ends.

    The concept is pretty easy and works for any country even if with some changes :

    FORGET the year for the moment. Holiday fall on a specific calendar day ( Jan. 1st, Dec. 25th ) or on a specific day of the week ( President and Martin L King's day, etc. ); and we have to make the whole more tasteful some exception.

    Let's take for example:
    - Jan. 1: we can see it as 1 ( Jan. ) * 100 + 01 = 101;
    - Dec. 25: we can see it as 12 ( Dec. ) * 100 + 25 = 1225.

    indipendently from the year we can find out immediately if today is holiday or not; exception if 1225 is saturday or sunday ), etc.

    now consider Martin L King or Easter day:
    - MLK falls the third Monday in January but alway between Jan. 15 and Jan 22; that means
    between 115 and 122;
    - Easter ( here you need the year ) falls always on a Sunday between Mar. 3rd ( 322 ) and Apr. 25 ( 425 ) - Why? please refer to Wikipedia.

    The only "complex" part is the calculation of Easter ( I use the Gaussian formula ), afterward is just a question of checking if conditions are met.

    The code I propose is written for my own pourpose ( I need the Financial Market - not stock exchange - good business days ), for EUR and USD; anyone can easily adapt it to his own need.


    Re: UDF Recalculating Prematurely

    if you need the EUR Area official market holidays, that is pretty simple to calculate and probably faster then lookup a whole vector.

    In total are 5 fixed days plus 4 variables ( around easter )

    USD Area maybe sligthly complex because of some more variable dates but still faster than vectors.


    Re: YEARFRAC & Leap Years

    Thanks guys!

    it's probably true that 'Yearfrac' work just for period below/equal to one year. What is misleading is that it works for all date constellations for all method but Act/Act.


    It looks like a pretty silly problem but it's driving me mad.

    The yearfrac function takes a two dates and a basis. Now assume basis = 1 ( Act / Act ) and two cases:

    1) 06-Jan-06 -> 06-Jan-08 ( 730 days ) = 1.998175182 =>> divisor = 730/1.998175182 = 365.33333

    2) 06-Jan-05 -> 06-Jan-07 ( 730 days ) = 2 =>> divisor = 730/2 = 365

    In case 1) 2008 is a leap year ( but the "leap" has yet to come! ). Can anyone explain me the logic behind? I suppose the extra day is divided by 3 but where is the "Actuality" of the divisor?


    Re: Optimize VBA Code


    I changed a little bit your code; not sure everything works fine, because I don't have any possibility to check it. I think shouldn't be any problem to correct evtl error messages


    Re: Sort Matrix By Largest Value

    Have you tried to run your code for large "n"s? eventually try and adapt to your needs one of the common sorting algorithm like bubble sort, insert, shell, etc...


    Re: Shared File Growing Massively

    I thought as well it would be the same, but a quick check has shown me something different. Just in case, it is better to proof a couple of them.


    Re: Shared File Growing Massively

    Are you sure that all users have the same settings?

    I tried a share workbook on three machines and had the advanced option set differently in each PC.


    Re: Convert Fractional Odds To Decimals


    I'm fascinated by the english peculiar way of calculating.::D

    7/2 = 4.5? it's fantastic! It opens incredible business perspectives: you show a price, but you mean another ( at least here on the continent ).;)
    Which format category should I look into? I searched in my german and english PC but I couldn't find any.


    Re: Dates Convert To Numbers


    if I well understand what good2soft want to say is he's importing 7/2 and would like to have 7/2 or 3.5 in the cell and not the equivalent 7-Feb.

    It happened something similar to me but the other way round: entering 1/2, 1/4 and 3/4 for 1-Feb, 1/3-April I got exactly 1/2, 1/4, 3/4 instead of my dates.
    It's worth maybe to look under Tools->AutocorrectOptions ( Autocorrect Tab), to see if all setting are correct.