我创建了一些UDF来自动化我和一些同事经常使用的一些微积分。 为了简单起见，我粘贴了一个我有问题的 MWE，我的实际代码更长，但采用相同的输入，一个维度等于 1 的单元格范围(所以一行或一列(
Public Function Test(Donnees As Range)
Dim Nombre_Cellules, Temp As Double
Dim Format_Donnees As String
Temp = 0
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' '
' Parametres utiles generaux '
' '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Nb_Lignes = Donnees.Rows.Count
Nb_Colonnes = Donnees.Columns.Count
Premiere_Ligne = Donnees.Row
Premiere_Colonne = Donnees.Column
Derniere_Ligne = Donnees.Row + Nb_Lignes - 1
Derniere_Colonne = Donnees.Column + Nb_Colonnes - 1
'On definit la frequence et la taille associee
If Nb_Lignes = 1 Then
Format_Donnees = "Colonnes"
Nombre_Cellules = Nb_Colonnes
End If
If Nb_Colonnes = 1 Then
Format_Donnees = "Lignes"
Nombre_Cellules = Nb_Lignes
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' '
' Verifications des parametres et messages d'erreurs '
' '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'On verifie que la plage renseignée est soit sur une seule ligne soit sur une seule colonne
If (Nb_Lignes <> 1 And Nb_Colonnes <> 1) Then
MsgBox _
"La plage de données considérée est incorrecte, il ne peut s'agir que " & vbNewLine & _
Chr(149) & " de données sur une seule ligne ou " & vbNewLine & _
Chr(149) & " de données sur une seule colonne" _
, , "Parametres incorrects"
Test = CVErr(xlErrRef)
Exit Function
End If
'On verifie que toute la période qui sert au calcul contient bien des valeurs numériques et ne contient pas de valeurs vides
If Format_Donnees = "Lignes" Then
For i = 0 To Nombre_Cellules - 1
If Not IsNumeric(Cells(Premiere_Ligne + i, Premiere_Colonne).Value) Then
MsgBox _
"La plage de donnée considérée est incorrecte" & vbNewLine & _
"Toutes les cellules nécessaires au calcul dans la colonne ne sont pas numériques" _
, , "Parametres incorrects"
Test = CVErr(xlErrRef)
Exit Function
End If
If (Cells(Premiere_Ligne + i, Premiere_Colonne).Value = "") Then
MsgBox _
"La plage de donnée considérée est incorrecte" & vbNewLine & _
"Une cellule de la colonne considérée est vide et semble avoir une valeur manquante" _
, , "Parametres incorrects"
Test = CVErr(xlErrRef)
Exit Function
End If
Next
End If
If Format_Donnees = "Colonnes" Then
For i = 0 To Nombre_Cellules - 1
If Not IsNumeric(Cells(Premiere_Ligne, Premiere_Colonne + i).Value) Then
MsgBox _
"La plage de donnée considérée est incorrecte" & vbNewLine & _
"Toutes les cellules nécessaires au calcul dans la ligne ne sont pas numériques" _
, , "Parametres incorrects"
Test = CVErr(xlErrRef)
Exit Function
End If
If (Cells(Premiere_Ligne, Premiere_Colonne + i).Value = "") Then
MsgBox _
"La plage de donnée considérée est incorrecte" & vbNewLine & _
"Une cellule de la ligne considérée est vide et semble avoir une valeur manquante" _
, , "Parametres incorrects"
Test = CVErr(xlErrRef)
Exit Function
End If
Next
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' '
' Calculs a proprement parler '
' '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If Format_Donnees = "Lignes" Then
For i = 0 To Nombre_Cellules - 1
Temp = Temp + Cells(Premiere_Ligne + i, Premiere_Colonne).Value
Next
End If
If Format_Donnees = "Colonnes" Then
For i = 0 To Nombre_Cellules - 1
Temp = Temp + Cells(Premiere_Ligne, Premiere_Colonne + i).Value
Next
End If
Test = Temp
End Function
因此，由于我不会是此功能的唯一用户，因此我尝试包含几个检查和错误消息。 一种是检查所选范围是否有空值和任何非数值。
现在我的函数可以工作(至少它们计算出我想要它们的东西(，但我在它们如何更新方面遇到了一些麻烦。 请注意，我已经反对用户首选项，以便所有使用的值都包含在输入中传递的范围内。
我能够使用此代码重现的问题之一是，如果我在一个工作簿的几张纸上使用此功能(因此一个Test()工作表 1，一个Test()工作表 2，并且出于一个原因尝试更新整个工作簿(例如通过Ctrl+alt+shift+F9(， 然后我会得到一个警告，我在非活动工作表中设置("Une cellule de la ligne considérée est vide et semble avoir une valeur manquante"(。
有人可以给我解释一个吗？
您正在使用没有工作表限定的 Cells((。这意味着它指的是活动工作表碰巧是什么。因此，除非对 UDF 的所有调用都在当前活动的工作表上，否则它将无法正常工作
您需要将其更改为Donnees.Cells( (并更改单元格索引以引用Donnees中的单元格而不是整个工作表中的单元格