ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VBA ªí³æ¥\¯àµo°Ý

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-1-17 10:23 ½s¿è

¦^´_ 1# v03586


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
ÁÂÁ ­ã´£³¡ªL«e½ú«ü¾É
«á¾Ç¤À¬q¾Ç²ß8#½d¨Ò¤ß±o¦p¤U,½Ð«e½ú°Ñ¦Ò

Option Explicit
Dim Xrr(1 To 7), Yrr(1 To 7), Zrr(1 To 7), zD As Object
Dim zT$, zK$, zType$, TS$, Un%, z%, ZZ%, zMode%, zDay
Const W1 = 257, W2 = 423
'¡ô«Å§i¦Û­qªí³æ¼Ò²Õ¸Ì¦@¥ÎÅܼÆ:(Xrr,Yrr,Zrr)¬O¤@ºû°}¦C,zD¬Oª«¥ó
'(zT,zK,zType,TS)¬O¦r¦êÅܼÆ,(Un,z,ZZ,zMode)¬Oµu¾ã¼ÆÅܼÆ,zDay¬O³q¥Î«¬ÅܼÆ
'W1¬O©T©wÅܼÆ257, W2¬O©T©wÅܼÆ423


Private Sub Cmd_1_Click() '·s¼W«ö¶s
'¡ô«ö¤F [·s¼W]«ö¶s«á·|°õ¦æ¤U¦Cµ{§Ç
Cmd_1.Enabled = False: Cmd_2.Enabled = False: Cmd_3.Enabled = False
'¡ô¥O³o3­Ó«ö¶s¥¢®Ä
Cmd_A1.Enabled = True: Cmd_A2.Enabled = True
'¡ô¥O³o2­Ó«ö¶s·|¦³®Ä
Erase Xrr, Yrr, Zrr
'¡ô²MªÅ3­Ó¤@ºû°}¦C¦@¥ÎÅܼÆ
Call ª«¥ó_UnLocked: zMode = 1
'¡ô°õ¦æ ª«¥ó_UnLocked°Æµ{¦¡ :¥OzMode³oµu¾ã¼Æ¬O1
End Sub

Private Sub Cmd_2_Click() '¬d¸ß«ö¶s
'¡ô«ö¤F [¬d¸ß]«ö¶s«á·|°õ¦æ¤U¦Cµ{§Ç
Frame1.Visible = True: TextBox5.SetFocus
'¡ô¥OFrame1³oªí³æÅã¥Ü :¥O´å¼Ð¸¨¦b TextBox5¿é¤Jµ¡¤W
Cmd_1.Enabled = False: Cmd_2.Enabled = False: Cmd_3.Enabled = False
'¡ô¥O³o3­Ó«ö¶s¥¢®Ä
Lab_A1.Visible = True: Lab_Date.Visible = True
'¡ô¥O³o¨â­Ó¼ÐÅÒÅã¥Ü
Erase Xrr, Yrr, Zrr: Set zD = Nothing
'¡ô¥O³o¨Ç®e¾¹²MªÅ
«Ø¥ß¸ê®Æ.Width = W2: Call ¨ú±o±ø½X²M³æ: zMode = 3
'¡ô¥O«Ø¥ß¸ê®Æ³oªí³æªº¼eÅܬ° W2ÅܼƼe423
End Sub

Private Sub Cmd_3_Click()
If zMode <> 3 Then Exit Sub
'¡ô¦pªGzMode³o¼Ò²Õ¦@¥ÎµuÅܼƤ£¬O 3!´Nµ²§ôµ{§Ç
«Ø¥ß¸ê®Æ.Width = W1: Frame1.Visible = False: Cmd_3.Enabled = False
'¡ô¥O«Ø¥ß¸ê®Æ³oªí³æªº¼eÅܬ° W1ÅܼƼe257
Cmd_A1.Enabled = True: Cmd_A2.Enabled = True: TextBox1.SetFocus
'¡ô¥O2­Ó«ö¶s¥¢®Ä :¥O´å¼Ð¸¨¦b TextBox1¿é¤Jµ¡¤W
Call ª«¥ó_UnLocked: zMode = 2
'¡ô°õ¦æ ª«¥ó_UnLocked°Æµ{¦¡ :¥OzMode³oµu¾ã¼Æ¬O2
End Sub

Sub ª«¥ó_UnLocked()
Dim LK, CL
'¡ô«Å§iLK,CL¬O³q¥Î«¬ÅܼÆ
LK = False: CL = &HFFFFFF
'¡ô¥OLK³o³q¥Î«¬ÅܼƬO¥¬ªL­È False,¥OCL³o³q¥Î«¬ÅܼƬO«G¥Õ¦âªº¥N½X
TextBox1.Locked = LK: TextBox2.Locked = LK: TextBox3.Locked = LK: TextBox4.Locked = LK
'¡ô¥O³o4­Ó¿é¤Jµ¡Âê©w¥¢®Ä(¥i½s¿è)
TextBox1.BackColor = CL: TextBox2.BackColor = CL: TextBox3.BackColor = CL: TextBox4.BackColor = CL
'¡ô¥O³o4­Ó¿é¤Jµ¡©³¦â¬OCLÅܼÆ(«G¥Õ¦â)
OptionButton1.Locked = LK: OptionButton2.Locked = LK: OptionButton3.Locked = LK
'¡ô¥O³o3­Ó¿ï¾Ü¶sÂê©w¥¢®Ä(¥i½s¿è)
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 8# ­ã´£³¡ªL


    ÁÂÁ«e½ú
¤À¬q¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É

Private Sub Cmd_A1_Click() '«Ø¥ß±ø½X¼g¤J«ö¶s
Dim TT$, T$, V%, VV%, R&, i&, Arr, xF As Range
'¡ô«Å§iÅܼÆ:(TT,T)¬O¦r¦êÅܼÆ,(V,VV)¬Oµu¾ã¼ÆÅܼÆ,(R,i)¬Oªø¾ã¼ÆÅܼÆ,
'Arr¬O³q¥Î«¬ÅܼÆ,xF¬OÀx¦s®æÅܼÆ

Un = 0: Call Àˬd¿é¤J­È
'¡ô¥OUn³oµu¾ã¼ÆÅܼƬO 0 :°õ¦æ Àˬd¿é¤J­È °Æµ{¦¡
If zK = "A00000" Then MsgBox "¡¯©|¥¼¿é¤J¥ô¦ó¸ê®Æ!  ": Exit Sub
'¡ô¦pªGzK³o¦r¦êÅܼƬO "A00000"!´N¸õ¥X´£¥Üµ¡~ :µ²§ôµ{¦¡°õ¦æ
If zK = "A22222" Then MsgBox "¡¯½s¿è¤º®e»P­ì¸ê®Æ¬Û¦P¡A¥¼Åܧó¡I  ": Exit Sub
'¡ô¦pªGzK³o¦r¦êÅܼƬO "A22222"!´N¸õ¥X´£¥Üµ¡~ :µ²§ôµ{¦¡°õ¦æ
If InStr(zK, "0") Then MsgBox "¡¯¸ê®Æ¿é¤J¤£§¹¥þ©Î¿ù»~¡A½ÐÀˬd!  ": Exit Sub
'¡ô¦pªGzK³o¦r¦êÅܼƸ̦³¥]§t"0"¦r¤¸!´N¸õ¥X´£¥Üµ¡~ :µ²§ôµ{¦¡°õ¦æ
zDay = Now: If zMode = 2 Then zDay = Lab_Date.Caption
'¡ô¥OzDay³o³q¥Î«¬ÅܼƬO²{¦b®É¶¡ :¦pªGzModeÅܼƬO 2!´N¥OzDayÅܼƬO Lab_Date¼ÐÅÒ¦^¶Ç­È
TT = Format(zDay, "mmdd") & Join(Yrr, "")
'¡ô¥OTT³o¦r¦êÅܼƬO zDayÅܼÆÂà´«¦r¦ê¨ú¤ë¤ë¤é¤é4½X,³s±µ(YrrÅܼƥHªÅ¦r¤¸µ²¦X¦¨ªº¦r¦ê)
'---------------------------
If zMode = 2 Then '½s¿è§ó§ï--§R°£­ì¸ê®Æ
'¡ô¦pªGzModeÅܼƬO 2?
   Set xF = Sheets("Database").Range("G:G").Find(TextBox5.Text, Lookat:=xlWhole)
   '¡ô¥OxF³oÀx¦s®æÅܼƬO¥H Find()¤èªk¦^¶ÇªºÀx¦s®æª«¥ó:
   '¿é¤Jµ¡TextBox5¤å¦r¬O ­n·j´Mªº¸ê®Æ,·j´M½d³ò¬O "Database"¤u§@ªíGÄæ,±ø¥ó:­È¥þ³¡¬Û¦P
   If Not xF Is Nothing Then xF.EntireRow.Delete
   '¡ô¦pªGxFÅܼƦ³·j´M¨ì!´N¥OxFÅܼƩҦbªº¦C§R°£,Àx¦s®æ·|©¹¤W»¼¸É
End If
With Sheets("Database")
'¡ô¥H¤U¬OÃö©ó "Database"¤u§@ªí ªºµ{§Ç
     R = .Cells(Rows.Count, 1).End(3).Row
     '¡ô¥OR³oªø¾ã¼ÆÅܼƬO¸ÓªíAÄæ³Ì«á¤@­Ó¦³¤º®eÀx¦s®æ ¦C¸¹
     Arr = .Range("A1:G" & R)
     '¡ô¥OArr¬O¤Gºû°}¦C!¥H¸Óªí[A1]¨ìGÄæRÅܼƦC½d³òÀx¦s®æ­È­Ë¤J
     For i = R To 2 Step -1
     '¡ô³]°f°j°é!!i±qRÅܼƨì2,³v¦¸´î 1
         T = Arr(i, 7)
         '¡ô¥OT³o¦r¦êÅܼƬOi°j°é¦C7ÄæArr°}¦C­È
         If Left(T, 14) = TT Then
         '¡ô¦pªGTÅܼƨú¥ª°¼14­Ó¦r¤¸­È­è¦n¬O TT¦r¦êÅܼƭÈ?
            V = Val(Mid(T, 15, 2))
            '¡ô¥OV³oµu¾ã¼ÆÅܼƬO Val()¨ç¼ÆÂà¤Æ¼Æ¦r¦^¶Çªº­È,
            'Val(TÅܼƱq²Ä15¦r¨ú2¦r¤¸)

            If V > VV Then VV = V
            '¡ô¦pªGVÅܼƤj©ó ³oVVµu¾ã¼ÆÅܼÆ!´N¥OVVÅÜ¼Æ = VÅܼÆ
         End If
     Next i
     Xrr(1) = zDay: Xrr(7) = TT & Format(VV + 1, "00")
     '¡ô¥OXrr³o¤@ºû°}¦C1¯Á¤Þ¸¹°}¦C­È¬O zDayÅܼÆ
     '¡ô¥OXrr³o¤@ºû°}¦C7¯Á¤Þ¸¹°}¦C­È¬O TTÅܼƳs±µ VVÅܼÆ+1«á¥H¨â½X¦^¶Çªº¦r¦ê

     .Range("A" & R + 1).Resize(1, 7) = Xrr
     '¡ô¥O¸ÓªíAÄæRÅܼÆ+1¦C¦ì¸mÀx¦s®æÂX®i¦V¥k7Äæ½d³òÀx¦s®æ­È ¥HXrr¤@ºû°}¦C­È±a¤J
     With .Range("A1:G" & R + 1)
     '¡ô¥H¤U¬OÃö©ó¸Óªí[A1]¨ì GÄæRÅܼÆ+1¦C½d³òÀx¦s®æªºµ{§Ç
        .Sort Key1:=.Item(1), Order1:=xlAscending, _
              Key2:=.Item(7), Order2:=xlAscending, Header:=xlYes
     '¡ô¥O¸Ó½d³òÀx¦s®æ°µ±Æ§Ç²Ä¤@¼h¥HAÄ涶±Æ§Ç,²Ä¤G¼h¥HGÄæ°µ¶¶±Æ§Ç,¦³¼ÐÃD¦C
     End With
End With
MsgBox "¡¯¸ê®Æ«Ø¥ß§¹¦¨!  "
'¡ô¸õ¥X´£¥Üµ¡~~
If zMode = 2 Then Call ª«¥ó_ªì©lª¬ºA: zMode = 0 Else Call ²M°£¸ê®Æ: TextBox1.SetFocus
'¡ô¦pªGzModeÅܼƬO 2!´N°õ¦æ°Æµ{¦¡ Sub ª«¥ó_ªì©lª¬ºA(),¥OzModeÅܼƬO 0,
'§_«h´N°õ¦æ°Æµ{¦¡ Sub ²M°£¸ê®Æ(),¥OÁä½L´å¼Ð¸¨¦b TextBox1¿é¤Jµ¡¤W

End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 8# ­ã´£³¡ªL


    ÁÂÁ«e½ú

Option Explicit
Sub Mid¯S§O¥Îªk_¦r¦êªº«ü©w¦r¤¸¼Æ¸m´«()
Dim Y$, Z$
Y = "ABCDEFG": Z = Y
Mid(Y, 2, 10) = "bcdefghijk"
MsgBox Y
Mid(Z, 2, 10) = "b"
MsgBox Z
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤Ó¶§¥ú¤j¡B¤÷¥À®¦¤j¡B§g¤l¶q¤j¡A¤p¤H®ð¤j¡C
ªð¦^¦Cªí ¤W¤@¥DÃD