Excel VBA ヒント集

私の勝手な備忘録です。

▼セルをロックする
' ロックする
Range("A1").Locked = True

' ロックしない
Range("A1").Locked = False

▼数字を列に変換
ExcelにはADDRESS関数という関数がある。行と列の位置を数字で指定すれば、その位置にあるセルのアドレスを返す関数だ。たとえば「1行目3列目」なら「ADDRESS（1,3）」と指定する。結果は「$C$1」という絶対参照形式の文字列が返る。

▼選択する場合のロジック

select.vba

Private Sub btn選択_Click()
  On Error GoTo subError
  Dim w作業日ST As String, w作業日ED As String
  Dim w報告日ST As String, w報告日ED As String
  Dim wLotNoST  As String, wLotNoED  As String
  Dim wID出荷   As Long
  Dim wID自社   As Long
  Dim wID仕入   As Long
  
  subCond = ""
  sCondMei = ""
  gdEdit2 = -1
  
  '--- 作業日 ---
  w作業日ST = Nz(Me.cond作業日ST.Value, "")
  w作業日ED = Nz(Me.cond作業日ED.Value, "")
  Select Case True
    Case w作業日ST <> "" And w作業日ED <> ""
      subCond = subCond & "[作業日]>= #" & w作業日ST & "# AND [作業日]<= #" & w作業日ED & "# AND "
    Case w作業日ST <> ""
      subCond = subCond & "[作業日]>= #" & w作業日ST & "# AND "
    Case w作業日ED <> ""
      subCond = subCond & "[作業日]<= #" & w作業日ED & "# AND "
  End Select
  
  '--- 報告日 ---
  w報告日ST = Nz(Me.cond報告日ST.Value, "")
  w報告日ED = Nz(Me.cond報告日ED.Value, "")
  
  If w報告日ST = "" And w報告日ED = "" Then
    gw表題 = "入出庫明細表"
  Else
    gw表題 = w報告日ST & " ～ " & w報告日ED & " 入出庫明細表"
  End If
  Select Case True
    Case w報告日ST <> "" And w報告日ED <> ""
      subCond = subCond & "[報告日]>= #" & w報告日ST & "# AND [報告日]<= #" & w報告日ED & "# AND "
    Case w報告日ST <> ""
      subCond = subCond & "[報告日]>= #" & w報告日ST & "# AND "
    Case w報告日ED <> ""
      subCond = subCond & "[報告日]<= #" & w報告日ED & "# AND "
  End Select
  
  '--- ロット番号 ---
  wLotNoST = Nz(Me.condLotNoST.Value, "")
  wLotNoED = Nz(Me.condLotNoED.Value, "")
  Select Case True
    Case wLotNoST <> "" And wLotNoED <> ""
      subCond = subCond & "[LotNoB]>= '" & wLotNoST & "' AND [LotNoB]<= '" & wLotNoED & "' AND "
    Case wLotNoST <> ""
      subCond = subCond & "[LotNoB]>= '" & wLotNoST & "' AND "
    Case wLotNoED <> ""
      subCond = subCond & "[LotNoB]<= '" & wLotNoED & "' AND "
  End Select
  
  '--- 出荷先 ---
  wID出荷 = Nz(Me.condID出荷.Value, 0)
  If wID出荷 > 0 Then
    subCond = subCond & "[ID出荷] = " & Str(wID出荷) & " AND "
  End If
  
  '--- 自社製品 ---
  wID自社 = Nz(Me.condID自社.Value, 0)
  If wID自社 > 0 Then
    subCond = subCond & "[ID自社] = " & Str(wID自社) & " AND "
  End If
  
  '--- 仕入 ---
  wID仕入 = Nz(Me.condID仕入.Value, 0)
  If wID仕入 > 0 Then
    sCondMei = sCondMei & "[ID仕入] = " & Str(wID仕入) & " AND "
  End If
  
  '=== 明細選択 ===
  If sCondMei <> "" Then
    Call s_MeisaiFilter
    subCond = subCond & "[FL01] = -1 AND "
  End If
  
  '=== OR選択 ===
  'If sFLOR = 1 Then
  '    subCond = subCond & "[FLOR] = -1 AND "
  'End If
  
  '条件式
  If subCond <> "" Then
    subFilter = Mid(subCond, 1, Len(subCond) - 5)
    Me.btn解除.Enabled = True
    If InStr(subFilter, "[ID自社]") > 0 Then
      Me.btn入出庫明細.Enabled = True
    End If
  Else
    Call s_条件初期化
    Exit Sub
  End If
  
  Me.frmJisyaList.Form.Filter = subFilter
  Me.frmJisyaList.Form.FilterOn = True
  
  Call cond整列_AfterUpdate
  
  If Me.frmJisyaList.Form.Recordset.RecordCount = 0 Then
    MsgBox "該当するレコードが、見つかりません。", vbOKOnly, "確認"
    
    Call btn解除_Click
  Else
    If SysCmd(acSysCmdGetObjectState, acForm, "F_BdJisya") <> 0 Then
      gIDSeisan = Nz(Me.frmJisyaList.Form.Recordset![IDCard], 0)
    
      gdEdit4 = 1
      Call p_DispJisya(gIDSeisan, "F_BdJisya")
    Else
      gdEdit4 = 1
    End If
  End If

subExit:
  Exit Sub
  
subError:
  MsgBox Error$ & "(#" & Trim(Trim(Err.Number)) & ")", vbOKOnly + vbExclamation _
  , "確認 : 選択"
  Resume subExit
End Sub

▼わたしのブログ：エクセル職人
http://excel-databace.hatenablog.com/