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

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

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

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´ú¸Õ....)

Andy2483

¦^´_ 9# ikboy


    ÁÂÁ«e½ú«ü¾É
Andy·M¶w »Ý­n¦AªáÂI®É¶¡¬ã²ß±zªº«ü¾É

Andy2483

¦^´_ 9# ikboy


    ÁÂÁ¦U¦ì«e½ú«ü¾É
1.Andy¨S¦³ÁB±¡!­n©Ó»{¤µ¤Ñ¤~À´Debug.Print: ¦b§Y®É¹Bºâµ¡Åã¥Üµ²ªG
1.1.¹ïikboy«e½ú«Ü©êºp!¦b4¼Ó ´£¨ì ´ú¸Õµ²ªG¸ò·Q­nªºµ²ªG¤£¤@¼Ë¬O ¦]¬°¤£·|¥Î Debug.Print
1.2.ÁÙ³Q¤@ªø¦êªºµ{¦¡½XÀ~¨ì,·í¤U¥H¬°¬O«e½ú·|¿ù·N
1.3.­l¦ù«á¨Ó¦U¦ì«e½úªº¦A«ü¾É
2.ÁÂÁ¦U¦ì«e½ú­@¤ß«ü¾É

Andy2483

¦^´_ 6# n7822123


    «e½úªº«ü¾É«Ü«È®ð ÁÂÁÂ
1.ÀË°Q¤F¦Û¤vªºÆ[©À°ÝÃD
1.1.ª½±µ§äµù¸Ñ´N¥i¥H¤F¡A¦ó¥²¤@®æ®æªº§ä¡H
1.2.¥²³º¾ãªíµù¸Ñªº¼Æ¶q©w¤ñÀx¦s®æ¾ð¶q¤Ö
1.3.­n§äµù¸Ñ¼Æ¶q©Îµù¸Ñ¦ì¸m¶]±¼ªº¼Æ¶q¡I´N¾ãªí³£§ä´N¦n¤F¡A¦ó¥²§½­­¦b¬Y°Ï°ì
2.ÁÂÁ«e½ú«ü¾É