このページでは、管理人が良く使う次のソフト
MS-EXCEL(エクセル)
MS-ACCESS(アクセス)
Oracle(オラクル)SQL
について、良く使うけれどもすぐに忘れて、何度も調べ「関数やVBAや構文等」について「あいうえお順」でまとめています。
日々思い立ったことを追記していきます。
適宜(CTRL+F)で検索して利用してください。
なお各(関数・VBA・SQL構文)名称(タイトル)については、
やりたい事名 + 例(EXCEL-関数) 等と記載します。
あ行
値を取得
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構文」がある!
サイトにまとめておけば、検索もし易く便利!
コメント