CODEWITHSUNDEEP
vbaexcel
user3061105
user3061105

Reputation: 19

Is my user-defined function correct and how to use it in Excel?

This VBA code is to determine either if a year is a leap year or not (giving boolean as a result). Here below the code :

Function ESTAnneeBixxstile(iAnne As Integer) As Boolean
'//////////////////////////////////////////////////////////////
' Cette fonction détermine si une année est bissextile
' Elle retourne True (vrai ou -1) si l'année compote 366 jours,
' False (faux ou 0) sinon

Dime bReponse As Boolean
bReponse = False

If iAnnee Mod 4 = 0 Then
'///////////////////////////////////////////////////////
' Les années divisibles par 4 sont bissextiles
'///////////////////////////////////////////////////////
bReponse = True

If iAnnee Mod 100 = 0 Then
'//////////////////////////////////////////////////////
' Les années divisibles par 100 ne sont pas bissextiles.
'//////////////////////////////////////////////////////

bReponse = False

    If iAnnee Mod 400 = 0 Then
    '///////////////////////////////////////////////////////
    ' Mais les années divisibles par 400 sont bissextiles
    '///////////////////////////////////////////////////////

    bReponse = True

    End If

End If

End If
ESTAnneeBissextile = bReponse

End Function

QUESTIONS :

  1. Is my code correct according to VBA current syntax (Office 2013) ? The compiler says there is a syntax error.

  2. How to run the VBA code as a formula in an Excel spreadsheet ?

For example: =ESTAnneeBissextile("Cell Name")

Upvotes: 1

Views: 122

Answers (2)

Gaijinhunter
Gaijinhunter

Reputation: 14685

You have a few issues in the example you provided.

  • "Dime" should be "Dim" (as mentioned above by ErinsMatthew)
  • Your return value is listed as "ESTAnneeBissextile" yet the function name is spelled differently as "ESTAnneeBixxstile". This will result in a FALSE result every time.
  • Your variable input is called "iAnne" but the variable you use in the code is "iAnnee". Make sue to use the same names or the results will not be accurate.
  • By adding the words Option Explicit at the top of your code you can catch some of these naming issues. :)
  • The logic is wrong in your code. If a year can be MOD 400 then is should be TRUE but in your code you only get to that check if it passes the previous 2 conditions. Make sure to separate that check as its own if statement at the end or use the examples I provide below.

Fixing those should make your function work properly. As suggested in the comments, it is arguably best to use:

Function leapCheck(year As Long) As Boolean

leapCheck = IsDate("2/29/" & year)

End Function

another fun way if you want to list out the logic is:

Function isLeapYear(year As Long) As Boolean

If year Mod 400 = 0 Or year Mod 4 = 0 And year Mod 100 <> 0 Then
    isLeapYear = True
Else
    isLeapYear = False
End If

End Function

Although I greatly prefer these two options, here is your code with the patches I mentioned above (personally I would check for the conditions you WANT versus the ones you don't want, so I would check if MOD 100 <> 0 as I did above. It's easier to read and easier to debug):

Function ESTAnneeBixxstile(iAnne As Long) As Boolean

Dim bReponse As Boolean

If iAnne Mod 4 = 0 Then
    bReponse = True
    If iAnne Mod 100 = 0 Then
        bReponse = False
    End If
End If

If iAnne Mod 400 = 0 Then
    bReponse = True
End If

ESTAnneeBixxstile = bReponse

End Function

Upvotes: 2

ErinsMatthew
ErinsMatthew

Reputation: 590

You do have a syntax error. Change Dime bReponse As Boolean to Dim bReponse As Boolean and you should be good to go. Once you do this, you will be able to call it exactly as you specify in (2), but leave off the quotes when referencing a cell.

Upvotes: 3

Related Questions