Excel VBA¦p¦óÀˬd_§t¦³µù¸Ñ®æ¼Æ¶q

Andy2483

¦U¦ì«e½ú¦n
1.½Ð±Ð Àˬd_§t¦³µù¸Ñ®æ¼Æ¶q ªºµ{¦¡½X
2.³oµ{¦¡ªº¥Øªº¬O¬°¤FÀˬd ¦]Àx¦s®æ¦C¤j¶q§R°£,¦Ó¨ä¥¦Àx¦s®æªºµù¸Ñ¹ê»Ú¦ì¸m¶]±o«Ü»·,«Ü¤£¤è«K½s¿èµù¸Ñ
2.1.¥H¤Uµ{¦¡½X¬O¦Û¤v·Qªº¤g¤èªk
3.¦³¨ä¥¦¤èªk¶Ü?
4.Andy·Q¶i¨B!½Ð«ü±Ð

½Ð¦U¦ì«e½ú«ü±Ð ÁÂÁÂ

Sub Àˬd_§t¦³µù¸Ñ®æ¼Æ¶q()
Dim uR As Range, hascomN&, hascomAD$, msg$, hascomRng As Range
Dim L&, T&, hascomERRN&
On Error GoTo 101
hascomN = 0
msg = "¨S¦³ µù¸Ñ®æ"
Set hascomRng = check_Area.SpecialCells(xlCellTypeComments)
On Error GoTo 0
On Error Resume Next
For Each uR In hascomRng
   If Not uR Is Nothing Then
      hascomN = hascomN + 1
      If hascomAD = "" Then
         hascomAD = uR.Address
         Else
            hascomAD = hascomAD & "," & uR.Address
      End If
      With uR.Comment.Shape
         L = .Left - (uR.Left + uR.Width + 200)
         T = .Top - (uR.Top + 50)
      End With
      If L > 0 Or T > 0 Then
         hascomERRN = hascomERRN + 1
      End If
   End If
Next
If hascomN > 20 Then
   If hascomERRN > 0 Then
      msg = "1.§t¦³µù¸Ñ®æ¤Ó¦h!¤£Åã¥Ü®æ¦ì" & Chr(10) & "2.µù¸Ñ¹ê»Ú¦ì¸m¶]±¼ªº®æ: " & hascomERRN & " ­Ó"
      Else
         msg = "1.§t¦³µù¸Ñ®æ¤Ó¦h!¤£Åã¥Ü®æ¦ì" & Chr(10) & "2.¨S¦³µù¸Ñ¹ê»Ú¦ì¸m¶]±¼ªº®æ"
   End If
   ElseIf hascomN <> 0 Then
      If hascomERRN > 0 Then
         msg = "1.¦³µù¸ÑÀx¦s®æ®æ¦ì: " & hascomAD & Chr(10) & "2.µù¸Ñ¹ê»Ú¦ì¸m¶]±¼ªº®æ: " & hascomERRN & " ­Ó"
         Else
            msg = "1.¦³µù¸ÑÀx¦s®æ®æ¦ì: " & hascomAD & Chr(10) & "2.¨S¦³µù¸Ñ¹ê»Ú¦ì¸m¶]±¼ªº®æ"
      End If
   Else
      msg = "¨S¦³ µù¸Ñ®æ"
End If

101
ck_K = "Àˬd_§t¦³µù¸Ñ®æ¼Æ¶q"
ck_Q = hascomN
ck_W = msg
If hascomERRN > 0 Then
   ck_AC = "µ{¦¡¥i¥H¦Û°Ê½Õ¾ãµù¸Ñ¦ì¸m"
   Call ³]²M³æ_IÄæ
   Call ·í¦C¤W¦â
   
   Else
      ck_AC = "NA"
End If
On Error GoTo 0
End Sub

ikboy

1. Sub zz()
   
2. Dim s$, c As Object
   
3. With ActiveSheet
   
4.     For i = 1 To .Comments.Count
   
5.         Set c = .Comments(i)
   
6.         s = s & Chr(10) & "Comment " & i & Chr(10) & "Location " & c.Parent.Address(0, 0) _
   
7.         & Chr(10) & "Contents " & Chr(10) & c.Text & Chr(10) & String(20, "*")
   
8.     Next
   
9. End With
   
10. Debug.Print s
    
11. End Sub

½Æ»s¥N½X

Andy2483

¦^´_ 2# ikboy
¦¬¨ì ÁÂÁ«e½ú«ü¾É
¬P´Á¤@¤~¦³¿ìªk¥ÎPC°µ´ú¸Õ ¾Ç²ß

Andy2483

¦^´_ 2# ikboy


ikboy«e½ú±z¦n
1.´ú¸Õµ²ªG¸ò·Q­nªºµ²ªG¤£¤@¼Ë,Andy¨S¦³ªí¹F²M·¡!©êºp
2±z´£¨Ñªºµ{¦¡½X¨S¬Ý¹L ·Pı«ÜÁ}²`!®£©È¤£¬OAndy³oµ¥¾Ç²Lªº¬Ý±oÀ´ªº
   ¦pªG±z¦³ªÅ ½Ð¦A«ü¾É ¼Ðµù¤@¤Uµ{¦¡½Xªº·N«ä ÁÂÁ±z
3.¤W¶Ç½d¨ÒÀɪí¹F±¡¹Ò,½Ð«e½ú­Ì¦A«ü¾É
20200810_1.rar (12.44 KB)

¤U¸ü¦¸¼Æ: 5

2020-8-10 07:57

n7822123

¦^´_ 4# Andy2483

1.´ú¸Õµ²ªG¸ò·Q­nªºµ²ªG¤£¤@¼Ë,Andy¨S¦³ªí¹F²M·¡!©êºp

¥L¦^¤F­«ÂI¼gªk¡A¦ý¬O¨S¦³®M¥Î¨ì§A­nªº¥\¯à

³o­Ó½×¾Â¦³¨Ç¤H¥u¦^"­«ÂI"¡A§A­n¯à¾AÀ³¡A¨Ã¥B­n¯à¬ÝÀ´§O¤Hªº¼gªk

§Ú¬O¤ñ¸ûÅo¶Ûªº¤H(­@¤ß¥´¦r)¡A¨ä¥L¤H¥i¤£¤@©w;P


2.¦pªG±z¦³ªÅ ½Ð¦A«ü¾É ¼Ðµù¤@¤Uµ{¦¡½Xªº·N«ä ÁÂÁ±z

¦³¤Wµù¸Ñ¤F¡A¬Ý¤£À´¦A°Ý!


3.¤W¶Ç½d¨ÒÀɪí¹F±¡¹Ò,½Ð«e½ú­Ì¦A«ü¾É

µ²¦Xikboy »P §A­ì¥»ªºµ{¦¡ µ¹§A

µ{¦¡¦p¤U

Sub Àˬd_§t¦³µù¸Ñ®æ¼Æ¶q_New()
Dim hascomN&, msg1$, msg2$, L&, T&, hascomERRN&
Dim uR As Range, com As Comment
msg1 = "1.¦³µù¸ÑÀx¦s®æ®æ¦ì: "
For Each com In ActiveSheet.Comments   '¦¹¬¡¤u§@ªí µù¸Ñ'¶°¦X'
  With com  '°w¹ï©Ò¦³µù¸Ñ
    hascomN = hascomN + 1  '­pºâ
    msg1 = msg1 & .Parent.Address  '¹ïÀ³ªºÀx¦s®æ¦ì¸m
    Set uR = Range(.Parent.Address)  '«ü©w¸ÓÀx¦s®æª«¥ó
    L = .Shape.Left - (uR.Left + uR.Width + 200)  '§A­ì¥»ªº³W«h
    T = .Shape.Top - (uR.Top + 50)                         '§A­ì¥»ªº³W«h
    If L > 0 Or T > 0 Then hascomERRN = hascomERRN + 1  '²Ö¥[¤£¦X³W«hªºµù¸Ñ¼Æ
  End With
Next
If hascomN > 20 Then msg1 = "1.§t¦³µù¸Ñ®æ¤Ó¦h!¤£Åã¥Ü®æ¦ì"     '§A­ì¥»ªº³W«h
msg2 = "2.µù¸Ñ¹ê»Ú¦ì¸m¶]±¼ªº®æ:" & hascomERRN & " ­Ó"
If hascomERRN = 0 Then msg2 = "2.¨S¦³µù¸Ñ¹ê»Ú¦ì¸m¶]±¼ªº®æ"  '§A­ì¥»ªº³W«h
MsgBox msg1 & Chr(10) & msg2
End Sub

Sub µù¸Ñ_½Õ¾ã¦Ü«ü©w¦ì¸m_New()
Dim com As Comment, SetLeft&, SetTop&
Const L = 20: Const T = 20   '§A­ì¥»ªº³W«h
For Each com In ActiveSheet.Comments  '¦¹¬¡¤u§@ªí µù¸Ñ'¶°¦X'
  With com   '°w¹ï©Ò¦³µù¸Ñ
    With Range(.Parent.Address)     '«ü©w¸ÓÀx¦s®æª«¥ó
      SetLeft = .Left + .Width + L   '§A­ì¥»ªº³W«h
      SetTop = .Top + T                   '§A­ì¥»ªº³W«h
    End With
    With .Shape: .Left = SetLeft: .Top = SetTop: End With  '³]©wµù¸Ñ¦ì¸m
  End With
Next
Application.DisplayCommentIndicator = 1   'Åã¥Üµù¸Ñ©M«ü¼Ð
End Sub

­×§ï§Aªº½d¨ÒÀÉ ¦p¤U

20200810_new.rar (22.59 KB)

¤U¸ü¦¸¼Æ: 9

2020-8-10 22:25

n7822123

¦^´_ 4# Andy2483


¸É¥R¤@¤U¡A§Aªº¼gªk¬O¥ÎÀx¦s®æ§äµù¸Ñ

ikboy ¬O¥Î µù¸Ñ§äÀx¦s®æ

Andy2483

¦^´_ 5# n7822123


    ÁÂÁ«e½ú«ü¾É
1.»{ÃѤF Parent ªº·§©À
MsgBox ActiveCell.Comment.Parent.Address
MsgBox ActiveCell.Parent.Name
MsgBox ActiveSheet.Parent.Name

2.Option Explicit    ¥H«á¤]·|¾i¦¨²ßºD

3.¦A½Ð±Ð«e½ú ºô¬d Const ¬O±`¼Æªº·N«ä,¦³¥Î¥¦¸ò¨S¥Î³£¥i¥H°õ¦æ
3.1.¦bµ{¦¡ªº³]­p¤W Const ¦³¬Æ»ò®É­Ô¬O«D¥Î¤£¥i©O? ½Ð±z¦A«ü¾É
Const L = 20: Const T = 20

Dim  L&, T&
L = 20:  T = 20

GBKEE

¦^´_ 7# Andy2483

3.¦A½Ð±Ð«e½ú ºô¬d Const ¬O±`¼Æªº·N«ä,¦³¥Î¥¦¸ò¨S¥Î³£¥i¥H°õ¦æ
3.1.¦bµ{¦¡ªº³]­p¤W Const ¦³¬Æ»ò®É­Ô¬O«D¥Î¤£¥i©O? ½Ð±z¦A«ü¾É
Const L = 20: Const T = 20

    Const «ü©wÅܼƩT©wªº¤º®e ³Ì¦n¥[µù»¡©ú ,·íµ{¦¡¼gªºÃe¤j®É¦p­×§ïÅܼƤº®e,¥u»Ý­×§ï³o¸Ì.
n7822123 , ikboy ªº¦^ÂЦh¥[½m²ß,¤£À´¥i¦h¬Ý»¡©ú,¦A´£°Ý·|¶i¨Bªº
¤U­±ªºµ{¦¡°Ñ¦Ò¬Ý¬Ý

1. Option Explicit
   
2. '365 ¤§«eª©¥»¥u¦³ Comment ª«¥ó ¬°[µù¸Ñ]
   
3. '365 ª©¥»¤¤             Comment  ª«¥ó ¬°[ªþµù]
   
4. '365 ª©¥»¤¤ ·s¼WCommentThreaded ª«¥ó ¬°[µù¸Ñ]
   
5. Sub Exµù¸Ñ()
   
6.     Dim Msg As String
   
7.     With ActiveSheet
   
8.         If .Comments.Count > 0 Then Msg = "[ªþµù]   ¦³" & .Comments.Count & "­Ó " & µù¸Ñ°Æµ{¦¡(.Comments)
   
9.         If Application.Version >= 16 Then   'ª©¥»365¥H¤W
   
10.              If Msg <> "" Then Msg = Msg & vbLf & vbLf
    
11.             If .CommentsThreaded.Count >= 0 Then Msg = Msg & "[µù¸Ñ]   ¦³" & .CommentsThreaded.Count & "­Ó " & µù¸Ñ°Æµ{¦¡(.CommentsThreaded)
    
12.         End If
    
13.         If Msg = "" Then Msg = .Name & " ¨S¦³¥ô¦ó µù¸Ñ...."
    
14.         MsgBox Msg
    
15. End With
    
16. End Sub
    
17. Function µù¸Ñ°Æµ{¦¡(comm As Object) As String
    
18.     Dim e As Object, µù¸Ñ As String
    
19.     For Each e In comm
    
20.         If TypeName(e) = "CommentThreaded" Then   'ª©¥»365¥H¤W
    
21.             µù¸Ñ = µù¸Ñ & IIf(µù¸Ñ <> "", vbLf, "") & e.Parent.Cells.Address & "  " & e.Text
    
22.         Else ' ª©¥»365¥H¤U
    
23.             µù¸Ñ = µù¸Ñ & IIf(µù¸Ñ <> "", vbLf, "") & e.Shape.TopLeftCell.Address & "  " & e.Text
    
24.         End If
    
25.     Next
    
26.     µù¸Ñ°Æµ{¦¡ = vbLf & µù¸Ñ
    
27. End Function

½Æ»s¥N½X

ikboy

¦^´_ 4# Andy2483
@Andy2483
´X¤Ñ¨S¨Ó, ¨S·Q¨ì¦³¨º»ò¦h°T®§, ­º¥ý§Ú¦b 2#¦^©«®É, §Ú°ò¥»¨S¦³­@¤ß¥h¸ÑŪ§Aªº¥N½X, ­ì¥»¬O·Q«ö¥DÃD -  Excel VBA¦p¦óÀˬd_§t¦³µù¸Ñ®æ¼Æ¶q, ¦^ÂФ@¥y¥N½X

1. Debug.print Activesheet.comments.count

½Æ»s¥N½X

¦ý¦ü¥G¹ï§A¹ê»Ú»Ý­n¨S¬ÆÀ°§U, ¤£¦p±N¤§¥Hª«¥ó§Î¦¡ªí¥Ü¥X¨äÄÝ©Ê, ³o´N¬O§Ú¦b2/F¦^©«¥Øªº¡C
@n7822123
¦hÁ¤ΨتA§A¦b5/Fªº²´¥ú
³Ì«á¦hÁÂGBKEEª©¤jªº¦U¶µ´£ÂI¡C

Andy2483

¦^´_ 8# GBKEE


    ÁÂÁ«e½ú«ü¾É
1.Const «ü©wÅܼƩT©wªº¤º®e
1.1.¥H«e³£¥Î°Æµ{¦¡ªº¤è¦¡©I¥s³o±`¼Æ
1.2.¹B¥ÎConst ©ñ¦b¼Ò²Õ¤W¤è
1.2.1.°µ¬°¥þ°ì±`¼Æ
1.2.2.¤]¥i¦bCode¸Ì,¦ý·N¸q¤£¦P
¥H¤W¾Ç²ß¤ß±o ¦p¦³«e½ú¥i«ü¾É ½Ð¤£§[«ü¾É

¥H«eªº¤g¤èªk
Option Explicit
Public ¦s´Ú§Q²v As Double
Sub ©w¸q¦s´Ú§Q²v()
¦s´Ú§Q²v = 0.03
End Sub

Sub ´¶³q¦s´Ú§Q²v()
Call ©w¸q¦s´Ú§Q²v
MsgBox ¦s´Ú§Q²v
End Sub

Sub Àu´f¦s´Ú§Q²v()
Call ©w¸q¦s´Ú§Q²v
MsgBox ¦s´Ú§Q²v * 1.1
End Sub

Sub ¤jÃB¦s´Ú¶WÀu§Q²v()
Call ©w¸q¦s´Ú§Q²v
MsgBox ¦s´Ú§Q²v * 1.5
End Sub

Sub ¸³¨Æ¯S¶WÀu§Q²v§O()
Dim ¦s´Ú§Q²v As Double
¦s´Ú§Q²v = 0.06
MsgBox ¦s´Ú§Q²v
Call ´¶³q¦s´Ú§Q²v
Call Àu´f¦s´Ú§Q²v
Call ¤jÃB¦s´Ú¶WÀu§Q²v
End Sub

'¹B¥ÎConst
Option Explicit
Const ¦s´Ú§Q²v = 0.03
Sub ´¶³q¦s´Ú§Q²v()
MsgBox ¦s´Ú§Q²v
End Sub

Sub Àu´f¦s´Ú§Q²v()
MsgBox ¦s´Ú§Q²v * 1.1
End Sub

Sub ¤jÃB¦s´Ú¶WÀu§Q²v()
MsgBox ¦s´Ú§Q²v * 1.5
End Sub

Sub ¸³¨Æ¯S¶WÀu§Q²v§O()
Const ¦s´Ú§Q²v = 0.06 '¥i¥H¥t¥~©w¸q¦¹±`¼Æ
MsgBox ¦s´Ú§Q²v
Call ´¶³q¦s´Ú§Q²v '¦ý¤£¼vÅT¥þ°ìªº ¦s´Ú§Q²v = 0.03
Call Àu´f¦s´Ú§Q²v
Call ¤jÃB¦s´Ú¶WÀu§Q²v
End Sub

2.Function¤@ª½¦¡¸ó¤£¹L¥hªºªùÂe
2.1.Andy ·M¶w»Ý­n¸ûªø®É¶¡¬ã²ß
2.2.«e½ú´£¨Ñªº½d¨Ò¬O¦Û¤vªºÃD§÷,§Æ±æÂǦ¹¯à¸ó¹L

ÁÂÁ¦U¦ì«e½ú«ü¾É
±zªº«ü¾É¦pªG¨S¦³§Y®É¦^À³: ¬ã²ß¤¤©Î¤£¤è«K¦^À³(¤u§@¦£.¨S¦³PC¥i´ú¸Õ....)