EXCELやACCESSの関数やVBA・SQL文等の忘備録まとめ!

スポンサーリンク
050-VBA

このページでは、管理人が良く使う次のソフト

MS-EXCEL(エクセル)

MS-ACCESS(アクセス)

Oracle(オラクル)SQL

について、良く使うけれどもすぐに忘れて、何度も調べ「関数やVBAや構文等」について「あいうえお順」でまとめています。

日々思い立ったことを追記していきます。

適宜(CTRL+F)で検索して利用してください。

なお各(関数・VBA・SQL構文)名称(タイトル)については、

やりたい事名 + 例(EXCEL-関数) 等と記載します。

  1. あ行
    1. 値を取得
    2. インラインビューを使ったUPDATE(SQL)
    3. ウエイト処理(EXCEL-VBA)
    4. N行毎に印(★)を付ける(EXCEL-VBA)
  2. か行
    1. 確認を表示させない(ACCESS-VBA)
    2. 確認を表示させない(EXCEL-VBA)
    3. 画面の更新を停止して処理を高速化(EXCEL-VBA)
    4. 期間の取得(EXCEL-関数)
    5. クエリにSQLを渡す(ACCESS-VBA)
    6. クエリにSQL文を渡す時に改行させる(ACCESS-VBA)
    7. 繰り返し処理(EXCEL-VBA)
    8. 検索して行番号・列番号を取得(EXCEL-VBA)
  3. さ行
    1. 最大行列の取得(EXCEL-VBA)
    2. 最大行列の取得(EXCEL-VBA)その2
    3. 最大行列の取得(EXCEL-VBA)その3
    4. シートを選択(EXCEL-VBA)
    5. シートを追加(EXCEL-VBA)
    6. シート名を変更(EXCLE-VBA)
    7. 重複行にフラグを付ける1(EXCEL-関数)
    8. 重複行にフラグを付ける2(EXCEL-関数)
    9. 処理の前に確認を促す(ACCESS-VBA)
    10. 処理の前に確認を促す(EXCEL-VBA)
    11. セルの値からフォルダを作成(EXCEL-VBA)
    12. セルの値をクリア(EXCEL-VBA)
    13. 全選択(CTRL+A)(EXCEL-VBA)
    14. セルの色取得・ユーザー定義関数(EXCEL-VBA)
  4. た行
    1. 直接日付を代入する(ACCESS-VBA)
    2. テキストボックスの文字列等をSQL文に代入する(ACCESS-VBA)
    3. テキストボックスを全てクリア(EXCEL-VBAのフォーム)
  5. な行
    1. ネットワークドライブ接続(CMD-COMMAND)
  6. は行
    1. 配列(EXCEL-VBA)
    2. 範囲(Range・CELL<セル>)の指定(EXCEL-VBA)
    3. 範囲をクリアする
    4. ファイルを開く(EXCEL-VBA)
    5. フォーム(自身)を閉じる(ACCESS-VBA)
    6. 複数のファイルの全てのシートを処理する例
    7. 複数のシートを処理(EXCEL-VBA)
    8. フォントの色を取得・ユーザー定義関数(EXCEL-VBA)
    9. ファイルを上書き保存(EXCEL-VBA)
    10. ファイルを閉じる(EXCEL-VBA)
  7. ま行
    1. メッセージ非表示・表示(MS-ACCESS)
  8. や行
  9. ら行
    1. リモートでWindowsをシャットダウン・再起動
    2. レコードをテキストに書き出し(ACCESS-VBA)
  10. わ行
    1. ワークシートの関数をVBAで使う(EXCEL-VBA)
  11. まとめ
  12. 日々更新・追記中です!

あ行

値を取得

 Range("A1").Value 'A1の値を取得

インラインビューを使ったUPDATE(SQL)

UPDATE
(
 SELECT
  T1.col1 AS name,
  T1.col2 AS age,
  T2.col1 AS prev_name,
  T2.col2 AS prev_age
 FROM
  TBL1 T1,
  TBL2 T2
 WHERE
  T1.col3 = T2.col3
)
SET
  name = prev_name,
  age = prev_age;

ウエイト処理(EXCEL-VBA)

Private Sub main()

    Application.Wait Now() + TimeValue("00:00:05")
    MsgBox "終了"

End Sub
Private Declare Sub sleep Lib "kernel32" Alias "Sleep" (ByVal ms As Long)


Private Sub main()

    sleep 5000
    MsgBox "終了"

End Sub

N行毎に印(★)を付ける(EXCEL-VBA)

Dim rowcounta As Long
Dim columncounta As Long
rowcounta = 2

'N行毎に★をつける
'B列の値で判断し、空白のA列に印(★)を付ける 
Do
    If Cells(rowcounta, 2) <> "" Then
    Cells(rowcounta, 1) = "★"
        rowcounta = rowcounta + N
    Cells(rowcounta, 1) = "★"
    Else: Exit Do
    End If
Loop

か行

確認を表示させない(ACCESS-VBA)

DoCmd.SetWarnings False
    処理
DoCmd.SetWarnings True

確認を表示させない(EXCEL-VBA)

Application.DisplayAlerts = False
 処理
Application.DisplayAlerts = True

画面の更新を停止して処理を高速化(EXCEL-VBA)

Application.ScreenUpdating = False
 処理
Application.ScreenUpdating = True

期間の取得(EXCEL-関数)

2つの日付の期間を求める(引数の単位を年にすることにより年齢を取得可能)

=DATEDIF("開始日","終了日","単位")

引数の「単位」は次のとおり
Y:期間内の満年数
M:期間内の満月数
D:期間内の日数

クエリにSQLを渡す(ACCESS-VBA)

Dim db1 As Database
Dim query1 As QueryDef
Dim sql1 As String
Set db1 = CurrentDb
Set query1 = db1.QueryDefs("クエリ名称")

sql1 = sql1 & " SQL文 "
sql1 = sql1 & " SQL文 "

query1.SQL = sql1
'注:番号は揃える事

クエリにSQL文を渡す時に改行させる(ACCESS-VBA)

sql1 = sql1 & " SQL文 " & vbNewLine

繰り返し処理(EXCEL-VBA)

Do While 繰返カウンタ < 繰返回数
    If 条件式1 Then
       変数 = 値1
    ElseIf 条件2 Then
       変数 = 値2
    Else: Exit Do(変数 = 値3)
    End If
       繰返カウンタ = 繰返カウンタ + 1
Loop

検索して行番号・列番号を取得(EXCEL-VBA)

'例は【sheet1】の【A列】を検索して行番号と列番号を取得
Dim row_count as Integer
Dim column_count as Integer
  
Worksheets("sheet1").Range("A:A").Find("検索文字列").Select
    
row_count = ActiveCell.Row
columun_count = ActiveCell.Column

さ行

最大行列の取得(EXCEL-VBA)

'変数の設定
Dim maxrow As Long
Dim maxColumn As Long
Dim rowcounta As Long
Dim columncounta As Long
rowcounta = 1
columncounta = 1

'最大の行数を算出
Do
    If Cells(rowcounta, 1) <> "" Then
        rowcounta = rowcounta + 1
    Else: Exit Do
    End If
Loop
maxrow = rowcounta - 1

'最大の列数を算出
Do
    If Cells(1, columncounta) <> "" Then
        columncounta = columncounta + 1
    Else: Exit Do
    End If
Loop
maxColumn = columncounta

最大行列の取得(EXCEL-VBA)その2

Dim maxrow As Long
Dim maxCol As Long

With ActiveSheet.UsedRange
    MaxRow = .Rows(.Rows.Count).Row
    MaxCol = .Columns(.Columns.Count).Column
End With
Dim maxrow As Long
Dim maxCol As Long

ActiveCell.SpecialCells(xlLastCell).Select
  maxrow = ActiveCell.Row
  maxcol = Activecell.Column

最大行列の取得(EXCEL-VBA)その3

Dim LastRow As Long
Dim LastCol as Long
 LastRow = Cells(Rows.Count, 1).End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
End Sub

シートを選択(EXCEL-VBA)

Worksheets("シート名").Activate

シートを追加(EXCEL-VBA)

Worksheets.Add before:=Worksheets(1)

シート名を変更(EXCLE-VBA)

ActiveSheet.Name = "シート名"

重複行にフラグを付ける1(EXCEL-関数)

重複している行に「重複」とつけたい場合、例文では、「A列」の重複を判定している。

=IF(COUNTIF($A:$A,A2)>1,"重複","")

重複行にフラグを付ける2(EXCEL-関数)

スポンサーリンク

重複している行(2つ目以降)に「重複」とつけたい場合、例文では、「A列」の重複を判定している。

=IF(COUNTIF($A$2:A2,A2)>1,"重複","")

処理の前に確認を促す(ACCESS-VBA)

If (MsgBox("処理を実行しますか?", vbOKCancel) = vbCancel) Then
    Exit Sub
End If

処理の前に確認を促す(EXCEL-VBA)

If MsgBox("処理を実行しますか?", vbYesNo) = vbNo Then
    Exit Sub
End If

セルの値からフォルダを作成(EXCEL-VBA)

’A列の値からフォルダ作成する 
Dim i
  For i = 2 To 50
  MkDir ThisWorkbook.Path & "\" & Cells(i,1).value
Next

セルの値をクリア(EXCEL-VBA)

 Sheets("シート名").Range("セル指定").ClearContents

全選択(CTRL+A)(EXCEL-VBA)

ブログランキングにご協力ください!
ブログランキング・にほんブログ村へ

Range(”A1″).CurrentRegion.Slect

セルの色取得・ユーザー定義関数(EXCEL-VBA)

Function セル色取得(セル)
    セル色取得 = セル.Interior.Color
End Function

た行

直接日付を代入する(ACCESS-VBA)

sql1 = sql1 & " 比較変数 >= #2019/12/02# "

テキストボックスの文字列等をSQL文に代入する(ACCESS-VBA)

'テキストの場合
sql1 = sql1 & " 比較値等 <= '" & Me!テキストボックス名 & "'"

'日付の場合
sql1 = sql1 & " 比較値等 = #" & Me!日付の場合 & "#"

'あいまい検索の場合
sql1 = sql1 & " 比較値等 LIKE '*" & Me!テキストボックス名 & "*'"

テキストボックスを全てクリア(EXCEL-VBAのフォーム)

'テキストボックスを全てクリア
    Dim Ctrl As Control

    For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
    Next Ctrl

な行

ネットワークドライブ接続(CMD-COMMAND)

@echo off
net use z: \\(IPアドレス)\(共有ドライブ名) /USER:user_name password /PERSISTENT:YES(or NO)
start z:
echo 「z」はドライブ文字列 「PERSISTENT」は、自動再接続の設定

は行

配列(EXCEL-VBA)

Sub test_array()

'セルの値を配列に格納してプリントする
'例はA1~A5の値を配列に格納して順番にプリントする

    Dim X1 As String
    Dim X2 As String
    Dim X3 As String
    Dim X4 As String
    Dim X5 As String

    Dim array1 As Variant
    Dim item1 As Variant

    X1 = Worksheets("sheet1").Range("A1").Value
    X2 = Worksheets("sheet1").Range("A2").Value
    X3 = Worksheets("sheet1").Range("A3").Value
    X4 = Worksheets("sheet1").Range("A4").Value
    X5 = Worksheets("sheet1").Range("A5").Value

    array1 = Array(X1, X2, X3, X4, X5)
    
    For Each item1 In array1

        Debug.Print item1

    Next item1

End Sub

範囲(Range・CELL<セル>)の指定(EXCEL-VBA)

cells(1,1) 'A1
Range("A1") 'A1
Range("A1","D10") 'A1~D10
Range("A1:D10") ' A1~D10
Range(Cells(1, 1), Cells(2, 5)) 'A1~E2
Range("A3:B6,C9:D12") ’A3~B6とC9~D12

Dim i as Integer '変数を使って範囲指定
For i = 1 to 5
  Range(Cells(1, 1), Cells(i, 1)).Select
Next 

Range("A3:B5").Resize(5, 5) 'A3~B5から5行5列に範囲指定を変更

Range("A3:B5").Offset(2, 3) 'A3~B5から範囲指定を2行2列オフセット

範囲をクリアする

Range("A1").Clear 'A1をクリア

Clear '全て
ClearComments 'コメント
ClearContents '値と数式
ClearFormats '書式
ClearNotes 'コメント
ClearOutline 'アウトライン
ClearHyperlinks 'ハイパーリンク 

ファイルを開く(EXCEL-VBA)

'ファイルを開くダイアログを表示
OpenFileName = Application.GetOpenFilename("Excelファイル,*.xls*")
'キャンセル時の処理
If OpenFileName = "False" Then
    'メッセージ表示
    MsgBox "キャンセルされました。"
    End
Else
    Workbooks.Open OpenFileName
End If

フォーム(自身)を閉じる(ACCESS-VBA)

docmd.close acform,me.name

複数のファイルの全てのシートを処理する例

Dim Myfile As String 'ファイル指定
Dim Filepath As String 'ファイルのパス
Dim ws_count As Integer 'ファイルのシートの数
Dim i As Integer '繰り返しFORのカウンタ
 
Filepath = ThisWorkbook.Path & "\" '自身のフォルダを取得
Myfile = Dir(Filepath) '自身のフォルダ内のファイルを取得

Do While Myfile <> "" 'ファイルが無くなるまで繰り返す

Workbooks.Open Filename:=Filepath & Myfile '自身のフォルダ内のファイルを開く
Workbooks(Myfile).Activate '開いたファイルをアクティブに

ws_count = ActiveWorkbook.Worksheets.Count '開いたファイルのシート数を取得

For i = 1 To ws_count 'シートの数だけ繰り返す
Worksheets(i).Range("A1", "A10").Value = "あ" '処理例<A1~A10に「あ」を入力>
Next i

ActiveWorkbook.Save 'ファイルをセーブ
ActiveWorkbook.Close 'ファイルを閉じる

Myfile = Dir() '次のファイルへ
Loop

複数のシートを処理(EXCEL-VBA)

Dim ws As Worksheet
Dim i As Long
i = 1
For Each ws In Sheets
Cells(i, 1) = ws.Name
i = i + 1
Next

フォントの色を取得・ユーザー定義関数(EXCEL-VBA)

Function フォント色取得(セル)
    フォント色取得 = セル.Font.Color
End Function

ファイルを上書き保存(EXCEL-VBA)

ActiveWorkbook.Save

ファイルを閉じる(EXCEL-VBA)

ActiveWorkbook.Close

別のフォームのボタンを押す(ACCESS-VBA)

'ボタンを押されるフォームのボタンを【Public_Sub】にする
'test_form2
Public Sub コマンド0_Click()
    MsgBox "テスト"
End Sub

'ボタンを押すフォーム
'test_form1
Private Sub コマンド1_Click()

   DoCmd.OpenForm "test_form2"
   Call Form_test_form2.コマンド0_Click
 
End Sub
スポンサーリンク

ま行

メッセージ非表示・表示(MS-ACCESS)

DoCmd.SetWarnings False
 処理
DoCmd.SetWarnings True

や行

ら行

リモートでWindowsをシャットダウン・再起動

【powershell】を起動して次のコマンドを実行

rem シャットダウンの場合
stop-computer -force -computername "対象のIPアドレス" -credential (get-credential)

rem 再起動の場合
restart-computer -force -computername "対象のIPアドレス" -credential (get-credential)

レコードをテキストに書き出し(ACCESS-VBA)

'WITH句を使う場合

Dim dbs As Database
Dim rst As Recordset
Dim strOutPut As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("テーブル")

With rst
strOutPut = ""

Do Until .EOF 'レコードが空になるまで
  strOutPut = strOutPut & !Name & "," & !kn_name & "," & vbCrLf
  .MoveNext '次のレコード
Loop '繰り返し

End With

Me.box1 = strOutPut 'フォームのテキストボックス(box1)へ書き出し
'WITH句を使わない場合

Dim dbs As Database
Dim rst As Recordset
Dim strOutPut As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("テーブル")

strOutPut = ""

Do Until rst.EOF 'レコードが空になるまで繰り返し
  strOutPut = strOutPut & rst!Name & "," & rst!kn_name & "," & vbCrLf
   rst.MoveNext '次のレコードへ
Loop '繰り返し

Me.box1 = strOutPut 'フォームのテキストボックス(box1)へ書き出し
'SQLを使う場合

Dim sql As String
Dim rs As Recordset

Me!text1 = "" 'フォームのテキトストボック(text1)をクリア

sql = "select カラム名 from テーブル名;" 'SQL
Set rs = CurrentDb.OpenRecordset(sql)

Do Until rs.EOF
  Me!text1 = Me!text1 & rs![カラム名] & "','" 
  rs.MoveNext
Loop

わ行

ワークシートの関数をVBAで使う(EXCEL-VBA)

Application.WorksheetFunction.ワークシート関数

まとめ

良く使っていもなかなか覚えられない「関数・VBA・SQL構文」がある!

サイトにまとめておけば、検索もし易く便利!

日々更新・追記中です!

ブログ開設に必要なドメイン取得、サーバーレンタル、ASPの登録等は、こちらのサイトから!

コメント

タイトルとURLをコピーしました