Excel VBA¦p¦óÀˬd_§t¦³µù¸Ñ®æ¼Æ¶q
- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
Excel VBA¦p¦óÀˬd_§t¦³µù¸Ñ®æ¼Æ¶q
¦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 |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|
- ©«¤l
- 262
- ¥DÃD
- 8
- ºëµØ
- 0
- ¿n¤À
- 280
- ÂI¦W
- 16
- §@·~¨t²Î
- xp
- ³nÅ骩¥»
- Office 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- HK
- µù¥U®É¶¡
- 2015-8-11
- ³Ì«áµn¿ý
- 2024-10-26
|
- Sub zz()
- Dim s$, c As Object
- With ActiveSheet
- For i = 1 To .Comments.Count
- Set c = .Comments(i)
- s = s & Chr(10) & "Comment " & i & Chr(10) & "Location " & c.Parent.Address(0, 0) _
- & Chr(10) & "Contents " & Chr(10) & c.Text & Chr(10) & String(20, "*")
- Next
- End With
- Debug.Print s
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¦^´_ 2# ikboy
¦¬¨ì ÁÂÁ«e½ú«ü¾É
¬P´Á¤@¤~¦³¿ìªk¥ÎPC°µ´ú¸Õ ¾Ç²ß |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|
- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¥»©«³Ì«á¥Ñ Andy2483 ©ó 2020-8-10 07:59 ½s¿è
¦^´_ 2# ikboy
ikboy«e½ú±z¦n
1.´ú¸Õµ²ªG¸ò·Qnªºµ²ª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)
|
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|
- ©«¤l
- 406
- ¥DÃD
- 8
- ºëµØ
- 0
- ¿n¤À
- 453
- ÂI¦W
- 0
- §@·~¨t²Î
- WINDOWS 7
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2015-2-7
- ³Ì«áµn¿ý
- 2021-7-31
|
¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-10 22:26 ½s¿è
¦^´_ 4# Andy2483
1.´ú¸Õµ²ªG¸ò·Qnªºµ²ªG¤£¤@¼Ë,Andy¨S¦³ªí¹F²M·¡!©êºp
¥L¦^¤F«ÂI¼gªk¡A¦ý¬O¨S¦³®M¥Î¨ì§Anªº¥\¯à
³oӽ׾¦³¨Ç¤H¥u¦^"«ÂI"¡A§An¯à¾AÀ³¡A¨Ã¥Bn¯à¬ÝÀ´§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)
|
|
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U
|
|
|
|
|
- ©«¤l
- 406
- ¥DÃD
- 8
- ºëµØ
- 0
- ¿n¤À
- 453
- ÂI¦W
- 0
- §@·~¨t²Î
- WINDOWS 7
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2015-2-7
- ³Ì«áµn¿ý
- 2021-7-31
|
¦^´_ 4# Andy2483
¸É¥R¤@¤U¡A§Aªº¼gªk¬O¥ÎÀx¦s®æ§äµù¸Ñ
ikboy ¬O¥Î µù¸Ñ§äÀx¦s®æ |
|
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U
|
|
|
|
|
- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¦^´_ 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 |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|
- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 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±ªºµ{¦¡°Ñ¦Ò¬Ý¬Ý- Option Explicit
- '365 ¤§«eª©¥»¥u¦³ Comment ª«¥ó ¬°[µù¸Ñ]
- '365 ª©¥»¤¤ Comment ª«¥ó ¬°[ªþµù]
- '365 ª©¥»¤¤ ·s¼WCommentThreaded ª«¥ó ¬°[µù¸Ñ]
- Sub Exµù¸Ñ()
- Dim Msg As String
- With ActiveSheet
- If .Comments.Count > 0 Then Msg = "[ªþµù] ¦³" & .Comments.Count & "Ó " & µù¸Ñ°Æµ{¦¡(.Comments)
- If Application.Version >= 16 Then 'ª©¥»365¥H¤W
- If Msg <> "" Then Msg = Msg & vbLf & vbLf
- If .CommentsThreaded.Count >= 0 Then Msg = Msg & "[µù¸Ñ] ¦³" & .CommentsThreaded.Count & "Ó " & µù¸Ñ°Æµ{¦¡(.CommentsThreaded)
- End If
- If Msg = "" Then Msg = .Name & " ¨S¦³¥ô¦ó µù¸Ñ...."
- MsgBox Msg
- End With
- End Sub
- Function µù¸Ñ°Æµ{¦¡(comm As Object) As String
- Dim e As Object, µù¸Ñ As String
- For Each e In comm
- If TypeName(e) = "CommentThreaded" Then 'ª©¥»365¥H¤W
- µù¸Ñ = µù¸Ñ & IIf(µù¸Ñ <> "", vbLf, "") & e.Parent.Cells.Address & " " & e.Text
- Else ' ª©¥»365¥H¤U
- µù¸Ñ = µù¸Ñ & IIf(µù¸Ñ <> "", vbLf, "") & e.Shape.TopLeftCell.Address & " " & e.Text
- End If
- Next
- µù¸Ñ°Æµ{¦¡ = vbLf & µù¸Ñ
- End Function
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 262
- ¥DÃD
- 8
- ºëµØ
- 0
- ¿n¤À
- 280
- ÂI¦W
- 16
- §@·~¨t²Î
- xp
- ³nÅ骩¥»
- Office 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- HK
- µù¥U®É¶¡
- 2015-8-11
- ³Ì«áµn¿ý
- 2024-10-26
|
¦^´_ 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- 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 |
|
|
|
|
|
|
- ©«¤l
- 1446
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1470
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-10-21
|
¦^´_ 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´ú¸Õ....) |
|
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
|
|
|
|
|