CODEWITHSUNDEEP
vbaexceloffice365
lolveley
lolveley

Reputation: 1719

error using a personal VBA function

I created a sheet containing some values; it is a calendar that displays some informations for each day of the year. for each day, I have an information given as a letter. e.g. February, 3rd 2016 --> "M"

I also have a VBA function that computes the sum of the cells of a month equal to - say - "M".

My first try was to modify in VBA the value of the cell receiving the sum, with a VBA function; but my problem is that users could manually edit some cells of the calendar, so I would like to the sum cell to be recomputed, automatically, and now it doesn't.

here is my function, located in a module named "abc":

Function nombreCellulesDansEtat2() As Integer
Application.Volatile
Dim moisSub
Dim équipeSub
Dim étatSub
moisSub = 1
équipeSub = 1
étatSub = "M"

Dim ligneDépart As Integer
ligneDépart = 5 + (moisSub - 1) * 9
Dim ligneEnCours As Integer
ligneEnCours = ligneDépart + équipeSub

Dim cellule
Dim retour As Integer
For Each cellule In sh.Range("B" & ligneEnCours & ":AF" & ligneEnCours)
    retour = retour + IIf(cellule.Value = étatSub, 1, 0)
Next cellule
nombreCellulesDansEtat = retour


End Function

please don't focus on the team number("équipeSub" in french), it's not linked to the problem.

The cell containing the sum has this formula : 

=nombreCellulesDansEtat2()

and the option of the automatic computing of formulas is enabled(I use excel 365 with windows 10)

the error is that the sum cell contains :

#VALUE!

and not the required sum.

thank you for having supported my bad english!

Upvotes: 2

Views: 77

Answers (1)

litelite
litelite

Reputation: 2849

Two problems with your code.

1) sh is undefined which cause your code to crash and causes the #VALUE!.

2) The name you used for the return is wrong, which means that your function is returning nothing which evaluates to 0 for excel (0 is the default value for Integer).

To avoid such problem in the future, I would recommend to add Option Explicit to the beginning of every file. That instruction will cause excel to report errors like these one.

Function nombreCellulesDansEtat2() As Integer
Application.Volatile
Dim moisSub
Dim équipeSub
Dim étatSub
moisSub = 1
équipeSub = 1
étatSub = "M"

Dim ligneDépart As Integer
ligneDépart = 5 + (moisSub - 1) * 9
Dim ligneEnCours As Integer
ligneEnCours = ligneDépart + équipeSub

Dim cellule
Dim retour As Integer
'sh is undefined. which means .Range() is called on Nothing
For Each cellule In sh.Range("B" & ligneEnCours & ":AF" & ligneEnCours)
    retour = retour + IIf(cellule.Value = étatSub, 1, 0)
Next cellule

'was nombreCellulesDansEtat
nombreCellulesDansEtat2 = retour


End Function

Upvotes: 4

Related Questions