つらつら Excel VBA

私の備忘録です。

InstrRev関数

後ろから探して、前から数える

スプレッドシートの日付計算

今日の日付が2018/8/24、 A1に日付 2018/8/20、 =TODAY()-A1の計算結果 Excel 4 スプレッドシート 3 なんやコレ...

日付のスプレッドシート互換

Excelで自動認識された日付はGoogleスプレッドシートで数値表示されてしまう。ユーザー書式で日付を指定すると、スプレッドシートでも日付と認識してくれる。

Interior.ColorとInterior.ColorIndex

Interior.Colorでは背景色未設定が判別できないので、以下のようにする。 If Interior.ColorIndex=xlNone Then 最悪、16777215で比較するとできる

偶数、奇数行だけ消したい

=mod(row(),2) 後はオートフィルタでOK

フリガナ削除

For Each rng In Selection rng.Characters.PhoneticCharacters = ""Next rng

漢字の並べ替えに注意

50音順の並べ替えが何故か期待通りに動かない!フリガナを疑いましょう。 自分でセルに入力した文字、コピペした文字、フリガナの有無で並べ替え結果が変わります!! フリガナ嫌い!!!!

Interior.ColorIndex

背景色をコピーする時、結果が異なるのは仕様なのだろうか r.Interior.ColorIndex = rr.Interior.ColorIndex r.Interior.Color = rr.Interior.Color 私は下を使う事にする

ユーザー定義書式

無理矢理文字を表示させたい時 ユーザー定義書式に「@"文字"」と入力 セルに「なんたら」と入力 「なんたら文字」と表示される この場合、セル未入力だと何も表示されない 完全に非表示にしたい場合 ユーザー定義書式にセミコロンを3個入力「;;;」 何を入力…

ランダム値を重複なしで取得

Dim DIC As Object, mKey Dim i As Integer, rd As Integer, no As IntegerSet DIC = CreateObject("Scripting.Dictionary")For i = 1 To 52 '連想配列の作成。 DIC.Add i, i Next For i = 52 To 1 Step -1 rd = getRandom(1, i) '1~iの範囲でランダム値を…

フォームに画像ファイル表示

Dim gazou As Stringgazou = ThisWorkbook.Path & "\gif\gazou.gif" Image1.Picture = LoadPicture(gazou)

オートフィルタ

'オートフィルタで絞り込み、必要列だけデータを取得する。 searchData = "*条件を含む*" wsh.AutoFilterMode = False 'オートフィルタ解除 wsh.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=searchData Set targetRange = wsh.Range("A1").C…

Select文の使い方メモ

Select文は上から順に条件を確認し、一致したところで終わる。 基本編 Select Case aaa Case "A" Case "B" Case Else End Select 応用編 Select Case True Case aaa Like "*A*" Case aaa Like "*B" Case Else End Select

シートコピー失敗

wsh.Copy After:=ThisWorkbook.Worksheets(1) Excel2007以降の環境で上記のコードをExcel2003(*.xls)ファイルで実行すると失敗する。 原因 扱うことのできるExcelシートの行数、列数が異なるため。 行・列数をExcelのバージョン毎に覚えるのは嫌なので、どう…

ブックのパスワード解除

On Error Resume Next SendKeys "{ESC}"Dim wb As WorkbookSet wb = Workbooks.Open(fileName:=target_path, Password:="Password")If ActiveWorkbook.Name = FileName Then Debug.Print "開きました。"End If On Error GoTo 0Set wb = Nothing

シートの保護解除

Dim wsh As WorksheetSet wsh = ThisWorkbook.ActiveSheet If wsh.ProtectContents Then wsh.Unprotect Password:="Password" End If wsh.Protect Password:="Password" Set wsh = Nothing ※シート保護されていれば保護解除し、最後にシート保護をする。意味…

サービスを起動するバッチファイル

@echo off echo.echo ============================================================echo サービスSQLServerを起動します。echo.echo 本バッチファイルは管理者権限で実行して下さい。echo ============================================================ec…

セルの結合、セルの個数などの確認

Dim c As Range Set c = Thisworkbook.Worksheet("Sheet1").Range("A1") If c.MergeCells Then MsgBox "結合されています。" If c.Areas.Count = 1 And c.Count = 1 Then MsgBox "選択セルは1つです。" If c.Interior.ColorIndex >= 0 Then "背景色が設定さ…

右クリックメニュー作成

■Sheet1に追加 Option Explicit Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myCB As CommandBar Dim myCBCtrl As CommandBarControl On Error Resume Next CommandBars("User_Short_Menu").Delete On Error GoT…

ファイルパスからファイル名を取り出す案

'ファイルパス、ファイル名+拡張子の状態からファイル名だけを取り出す処理。'[\]でSplitをかけて最後を取得、[.]でSplitをかけて最初を取得すればOKFunction getFileName(file_path) As String Dim temp As String Dim tmp_array2 As Variant, tmp_array1 …

他ワークブックのシートを最後尾にコピー

Dim wb As Workbook, wsh As Worksheet Set wb = Workbooks(file_name)Set wsh = wb.Sheets(1) 'シート1枚目 '同じ名前のシートがあっても「シート名(2)」とかになってコピーされる。wsh.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count…

ディレクトリ内のファイル検索(Dir)

file_name = Dir(target_dir & "*.csv")Do While file_name <> "" file_name = Dir() '次のファイルLoop ※Dirはファイル名が返される。 ファイルが見つからない場合には空文字が返る。

DoEvents

DoEvents おまじない。 画面更新されなかったり処理停止したい時に書くおまじない。 深く考えない!

関連付けられたアプリケーションでファイルを開く

Dim WSH As Object Set WSH = CreateObject("WScript.Shell")WSH.Run Chr(34) & FilePath & Chr(34) 'Set WSH = Nothing ※ファイルパスに空白が含まれていると実行に失敗します。なぜー Chr(34)でFilePathを囲み、これを回避しています。

InStr

If InStr(1, "C:\nantara\test.xls", "\", vbTextCompare) > 0 Then MsgBox "発見" End If ※1文字目から、比較方法はvbTextCompareで、\を探したら、3文字目でした。 見つからなかった場合は0が返ります。 検索開始場所と比較方法は省略可能。というか省略す…

On Error なんたらエラー処理

Sub Sample1 On Error GoTo onErr 'エラーが起きるような処理 GoTo onEnd 'Exit Subなど onErr: 'エラー時の処理を記載 Debug.Print Err.Number 'エラー番号 Debug.Print Err.Description 'エラーメッセージ Resume Next 'エラー発生個所に戻る?未検証onEnd…

デバッグ作業

VBEにおいてデバッグでよく使うヤツ [表示]-[ツールバー]-[編集] 主にコメント設定で使用 [表示]-[イミディエイトウィンドウ] Debug.Print の出力ウィンドウ [表示]-[ウォッチウィンドウ] 実行中の変数の中身を確認・編集ができる ショートカットキー [F5] …

ディレクトリ削除(RmDir)

RmDir "C:\FilePath\" ※RmDirはディレクトリ内にファイルがあるとエラーとなる。 ファイルが存在するディレクトリを削除するにはFileSystemObjectを使うようです。

構造体配列

'構造体の宣言は標準モジュールのTopで行う。(Option~のすぐ下) Option Explicit Public Type FileInfo '構造体の宣言 no As Integer name As String title As StringEnd Type Public FileInfoList(10) As FileInfo '構造体配列の宣言 'こんな感じ(動作…

ファイル数を数える

'指定したディレクトリ内のファイル数を数える。Public Function FileCount(sDir As String) As Integer Dim rtnCnt As Integer Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") rtnCnt = FSO.GetFolder(sDir).Files.Count Set FSO…