VBA: Qu'arrive-t-il aux objets Range si l'utilisateur supprime des cellules?
supposons que j'ai un module dans vba avec une variable r de type Range . Supposons qu'à un moment donné, j'y stocke un objet Range (par exemple la cellule active). Maintenant, ma question: Qu'advient-il de la valeur de r si l'utilisateur supprime la cellule (la cellule, non seulement sa valeur)?
j'ai essayé de comprendre cela dans VBA, mais sans succès. Le résultat est étrange. r n'est pas Nothing , la valeur de r est de type Range , mais si j'essaie de regarder ses propriétés dans la fenêtre du débogueur, chaque valeur de la propriété est signalé comme "objet requis".
Comment puis-je, programmatiquement, déterminer si la variable r est dans cet état ou non?
puis-je le faire sans générer d'erreur et l'attraper?
4 réponses
bonne question! Je n'ai jamais pensé à cela avant, mais cette fonction va, je pense, identifier une gamme qui a été initialisé - n'est pas Rien - mais est maintenant dans l'état "Objet requis" parce que ses cellules ont été supprimées:
Function RangeWasDeclaredAndEntirelyDeleted(r As Range) As Boolean
Dim TestAddress As String
If r Is Nothing Then
Exit Function
End If
On Error Resume Next
TestAddress = r.Address
If Err.Number = 424 Then 'object required
RangeWasDeclaredAndEntirelyDeleted = True
End If
End Function
vous pouvez tester est comme ceci:
Sub test()
Dim r As Range
Debug.Print RangeWasDeclaredAndEntirelyDeleted(r)
Set r = ActiveSheet.Range("A1")
Debug.Print RangeWasDeclaredAndEntirelyDeleted(r)
r.EntireRow.Delete
Debug.Print RangeWasDeclaredAndEntirelyDeleted(r)
End Sub
je crois que lorsque vous utilisez le mot-clé Set dans VBA, il crée un pointeur en arrière-plan à L'objet de portée de la feuille de travail que vous avez spécifié (chaque cellule étant un objet dans la collection de cellules de la feuille de travail pour une portée donnée). Lorsque la plage est supprimée alors que vous la référencez encore en mémoire, la mémoire de l'objet que la variable Range pointait a été désallocée.
cependant, votre variable de gamme plus-probablement encore contient le pointeur vers L'objet Range récemment supprimé, c'est pourquoi il n'est pas rien, mais ce qu'il pointe vers n'existe plus, ce qui cause des problèmes quand vous essayez d'utiliser la variable à nouveau.
Regardez ce code pour voir ce que je veux dire:
Public Sub test2()
Dim r As Excel.Range
Debug.Print ObjPtr(r) ' 0
Set r = ActiveSheet.Range("A1")
Debug.Print ObjPtr(r) ' some address
r.Value = "Hello"
r.Delete
Debug.Print ObjPtr(r) ' same address as before
End Sub
consultez cet article pour plus d'informations sur ObjPtr(): http://support.microsoft.com/kb/199824
donc tant que vous avez une adresse valide à un objet, malheureusement, l'objet n'existe plus depuis qu'il a été supprimé. Et il semble que "Is Nothing" vérifie juste une adresse dans le pointeur (ce que je pense que VBA croit que la variable est "Set").
quant à la façon de contourner ce problème, malheureusement je ne vois pas de moyen propre de faire à l'instant (si quelqu'un trouve un moyen élégant pour gérer cela, s'il vous plaît poster!). Vous pouvez utiliser sur la reprise D'erreur comme suit:
Public Sub test3()
Dim r As Excel.Range
Debug.Print ObjPtr(r) ' 0
Set r = ActiveSheet.Range("A1")
Debug.Print ObjPtr(r) ' some address
r.Value = "Hello"
r.Delete
Debug.Print ObjPtr(r) ' same address as before
On Error Resume Next
Debug.Print r.Value
If (Err.Number <> 0) Then
Debug.Print "We have a problem here..."; Err.Number; Err.Description
End If
On Error GoTo 0
End Sub
Comment puis-je, programmatiquement, déterminer si la variable r est dans cette l'état ou pas?
puis-je le faire sans générer d'erreur et l'attraper?
Pas de.
à ma connaissance, vous ne pouvez pas tester cette condition de manière fiable: pas sans soulever et attraper une erreur.
votre question a été remarqué et discuté ailleurs : deux des grands noms dans Excel/VBA blogging (Dick Kusleika et Rob Bovey) ont regardé dans elle, et vous pouvez trouver quelque chose d'informatif là-bas. Mais la réponse est non.
en somme, une bonne question avec une réponse plutôt inquiétante.
pour tester si un objet range est invalide, j'utilise cette fonction:
Public Function InvalidRangeReference(r As Range) As Boolean
On Error Resume Next
If r.Count = 0 Then
InvalidRangeReference = Err
End If
End Function