locked
Problems with WorksheetFunction.Days360 RRS feed

  • Question

  • Hi, I am having problems with WorksheetFunction.Days360.

    The macro as it is works fine, but I dont understand why days360 returns a 0 value when Nomina sheet is not selected,

    Thank you in advance.

    Sub CalculateDays() Dim Ingr As Date, Egr As Date, MesLiq As Date, Fbase As Date 'Determina la fecha del semestre MesLiq = Worksheets("Datos").Range("D3") If Month(MesLiq) <= 6 Then Fbase = DateSerial(Year(MesLiq), 1, 1) Else Fbase = DateSerial(Year(MesLiq), 7, 1) End If Worksheets("Nomina").Select 'IF IT IS NOT SELECTED, Cantidad = 0

    Ingr = Worksheets("Nomina").Range("D6").Offset(1, 0).Value 'Ingreso Egr = Worksheets("Nomina").Range("E6").Offset(1, 0).Value 'Egreso If Fbase > Ingr Then Cantidad = Application.WorksheetFunction.Days360(Fbase, Egr) Else Cantidad = Application.WorksheetFunction.Days360(Ingr, Egr) End If Liq.Select End Sub

    Saturday, October 12, 2019 3:33 PM

Answers

  • I found the culprit: the Mes, Recibo and PDF sheets have "Transition formula evaluation" set in File > Options > Advanced. The Lotus compatibility Settings section is at the bottom of the right-hand pane.

    If you clear the check box, the Days360 will return the correct result even if the Mes sheet is active.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Deus Irae Sunday, October 13, 2019 9:59 PM
    Sunday, October 13, 2019 6:06 PM

All replies

  • I cannot reproduce the problem - I created a test workbook with sheets Datos and Nomina, with dates in Datos!D3, Nomina!D7 and Nomina!E7.

    At the end of the macro, Cantidad had the same value whether the line Worksheets("Nomina").Select was present or not.

    Remark: you don't do anything with Cantidad in the macro...

    Could you make a sample workbook available through Dropbox, OneDrive or Google Drive?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, October 12, 2019 6:50 PM
  • Thank you Hans

    At first I was surprised by your answer so I made my own test as you did. The code worked ok, but not in my original workbook.

    Remark: In Worksheets("Datos").Range("D3") there is an eomonth function. The link:

    https://drive.google.com/drive/folders/1dmR3FBNMadvllvxaL73FVwVnQZ6QLlkc?usp=sharing

    Note: you have to run the macro with the button because is called by another macro.

    Regards
    Jorge

    Sunday, October 13, 2019 12:04 AM
  • I found the culprit: the Mes, Recibo and PDF sheets have "Transition formula evaluation" set in File > Options > Advanced. The Lotus compatibility Settings section is at the bottom of the right-hand pane.

    If you clear the check box, the Days360 will return the correct result even if the Mes sheet is active.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Deus Irae Sunday, October 13, 2019 9:59 PM
    Sunday, October 13, 2019 6:06 PM
  • I am shocked and you are a Genius!

    I even didn't know that option could affect the macro. 

    Why was it turned on in some sheets if it's off by default?

    Sunday, October 13, 2019 9:37 PM
  • Could those sheets have been copied or imported from an old workbook?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, October 13, 2019 9:39 PM
  • Maybe, but the PDF sheets was new.

    So, the macro ran ok with Nomina selected because "Transition formula evaluation" was not checked?

    Sunday, October 13, 2019 9:52 PM
  • Yes, indeed. The code ran OK with all sheets that didn't have "Transition formula evaluation" checked - I tested it

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, October 13, 2019 9:56 PM
  • I'm sure you solved this problem for many of my macros. 

    Thank you again!

    Sunday, October 13, 2019 10:02 PM