Excel VBA マクロTips よく使う便利な数行マクロ


文字列操作

関数は=FIND VBAではInstr()
http://officetanaka.net/excel/vba/function/InStr.htm
文字列を指定文字で右側から取り出す InStrRev

http://officetanaka.net/excel/vba/tips/tips78.htm
文字列が含まれているかどうか
 If InStr(c.Value, "あああ") > 0 Then

 end if

http://officetanaka.net/excel/vba/tips/tips33.htm

アルファベットの大文字/小文字を変換する(UCase/LCase関数)
Sub Sample()
    MsgBox UCase("Excel VBA")   '→ EXCEL VBA
    MsgBox LCase("Excel VBA")   '→ excel vba
End Sub

http://www.moug.net/tech/exvba/0140027.html

VBA内で検索をする
　　Public Sub test()
　　　　Dim MyRange As Range
　　　　Set MyRange = Columns("a").Find(What:="excel")
　　　　If MyRange Is Nothing Then
　　　　　　Debug.Print "Not Found"
　　　　Else
　　　　　　Debug.Print MyRange.Offset(0, 1)
　　　　End If
　　End Sub

※ 名前の範囲は Rangeで表現する
例） 名前定義：nmNames内を検索
Set MyRange = Range(nmNames).Find(What:=”excel”)

http://home.att.ne.jp/zeta/gen/excel/c04p42.htm

.Net の StringBuilder
研究中。C#勉強中に発見したのだが、感覚としてAppend()しか利用しないからラッパは必要ない。
  1. C# StringBuilder で高速に文字列を処理する
  2. C# 文字列を連結するには？
  3. ExcelVBA de StringBuilder
  4. 【VB.NET】StringBuilderの利点
  5. StringBuilderをvba用にラップしてみた
  6. VBAのクラスモジュールとは
  7. Excel VBAではじめるクラス入門

セルの操作、選択

アクティブセル領域の最後まで選択
  Range(Cells(2, 1), ActiveCell.SpecialCells(xlLastCell)).Select
セル幅自動調整 – currentregionではなく、特定のセルからラストまで選択して自動調整したいとき
  Range(Cells(2, 1), ActiveCell.SpecialCells(xlLastCell)).Columns.AutoFit
セルのクリアをするときは”行の削除”を検討する。
つまり、”.ClearContents” ではなく、 “.Delete Shift:=xlUp” 

  'Range("A3", ActiveCell.SpecialCells(xlLastCell)).ClearContents
  Range("A3", ActiveCell.SpecialCells(xlLastCell)).Delete Shift:=xlUp
copy　の終了
Application.CutCopyMode = False
セル範囲の指定方法、全般
Specialcellsとか
http://www.eurus.dti.ne.jp/yoneyama/Excel/vba/vba_cell.html#specialcells

E4からCtrl＋↓を押して選択した部分のフォントを9ポイントにする

Range(Range("E4"), Range("E4").End(xlDown)).Font.Size = 9

 Selection(Selection.Count).Row
セルのアドレスを取得する(Addressプロパティ)

http://www.moug.net/tech/exvba/0050094.html

名前付きセル範囲のアドレスを取得
Sub 名前付きセル範囲の行列数を取得する()
    行数 = Range("範囲名A").Rows.Count          '※１
    列数 = Range("範囲名A").Columns.Count       '※１
End Sub

http://www.asahi-net.or.jp/~zn3y-ngi/YNxv206.html#6-2

http://officetanaka.net/excel/vba/tips/tips111.htm

セル範囲のコピー
Worksheets("Sheet1").Range("B1:D3") _
  .Copy Destination:=Worksheets("Sheet1").Range("F5")

http://excelvba.pc-users.net/fol2/2_8.html

ActiveCellのアドレスを直接指定する
Cells(hl.Range.Row, hl.Range.coloumn)
ハイパーリンクの追加時に便利？
ActiveSheet.Hyperlinks.Add Anchor:=Cells(hl.Range.Row, hl.Range.Column), _

  ActiveSheet.Hyperlinks.Add Anchor:=Cells(hl.Range.Row, hl.Range.Column), _
    Address:=address, _
    TextToDisplay:=texttodisplay

シートの操作、選択

先頭のシートを選択
  Sub 先頭のシートを選択()
    ActiveWorkbook.Worksheets(1).Activate
  End Sub
最後のシートを選択
  Sub 最後のシートを選択()
    ActiveWorkbook.Worksheets(Worksheets.Count).Activate
  End Sub
ワークブック間でシートをコピーするが、最後尾にコピーする
開いたブック（コピー元）のシートを裏で開いているブック（コピー先）の最後尾にコピーする。
ポイントは Worksheets.Count とすると開いたブックのシート数をカウントしてしまうので、
“sample.xls”.Worksheets.Count　と裏で開いているブックの Worksheets.Countだと明示しなくてはいけない。
＃このあたりは誰も書いていない。実際に動かしていないんじゃないの？おかげで1時間悩んだわ（笑）

できれば、1行で名称変更もできればいいのだが。。


        Worksheets(1).Copy _
          After:=Workbooks("sample.xls").Worksheets("sample.xls".Worksheets.Count)

その他）
http://okwave.jp/qa/q2586801.html
Worksheets ワークシートのみ
Sheets　　 ワークシートに加え、グラフシート、ダイアログシートや
　　　　　 マクロシート等全てのシート

新しいシートを挿入しても、現在のアクティブシートを変更しない
http://officetanaka.net/excel/vba/sheet/sheet03.htm
ワークシートを2枚追加
  ActiveWorkbook.Worksheets.Add _
　　　　after:=Worksheets(Worksheets.Count), _
　　　　Count:=2
関数だけでワークシート名を表示する、あれこれ
http://www2.odn.ne.jp/excel/waza/sheet.html#SEC5

日付 時間

時刻の取得、表示
Format(Time, “hhmmss”)

データ関連

オートフィルタ – 無いときのみ設定
    'オートフィルタ設定
    If Not ActiveSheet.AutoFilterMode Then
      With ActiveSheet.Range(Cells(2, 1), ActiveCell.SpecialCells(xlLastCell))
        .autofilter
      End With
    End If
データ入力規則
http://www.eurus.dti.ne.jp/yoneyama/Excel/vba/vba_validation.html
最大行、最大列
    Dim Maxrow As Integer    '最大行数をセット（可変）
    Dim Maxcol As Integer    '最大列数をセット(可変）

    '最終行セット
    'http://www.niji.or.jp/home/toru/notes/8.html
    With ActiveSheet.UsedRange
        Maxrow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row    '最終行を取得
        Maxcol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column    '最終列を取得
    End With

ハイパーリンク関連

ハイパーリンク張る際のファイル名、シート名、セル位置の表記法
ファイル名#’シート名’!セル位置　　　例）ファイル名#’シート名’!$A$1
ハイパーリンクの取得
for each でしか抜き出せない（set ではNG。変数型の関係？）

  Sub ハイパーリンク取得()
    Dim hl As Variant
    For Each hl In Selection.Hyperlinks
      MsgBox "設定されているハイパーリンクは: " & vbCrLf & hl.Address
    Next hl
  End Sub

コントロール シェイプ フォームコントロール ActiveXコントロール 操作

プロパティの直接取得： Name, AlternativeText, Caption, Value, LinkedCell
“Object” を付加するのが勘所のようです

Sub オプションボタンの値を直接取得()

    strName = "OptionButton1"

    MsgBox "Name（OLEObjects.Name）: " & _
        ActiveSheet.OLEObjects(strName).Name
        
    MsgBox "Name（Shapes.AlternativeText）: " & _
        ActiveSheet.Shapes(strName).AlternativeText
        
    MsgBox "Caption（OLEObjects.Object.Caption）: " & _
        ActiveSheet.OLEObjects(strName).Object.Caption

    MsgBox "Value（OLEObjects.Object.Value）: " & _
        ActiveSheet.OLEObjects(strName).Object.Value
        
    MsgBox "LinkedCell（OLEObjects.LinkedCell）: " & _
        ActiveSheet.OLEObjects(strName).LinkedCell

End Sub
参考：’http://hpcgi1.nifty.com/kenzo30/b_cbbs/cbbs.cgi?mode=al2&namber=27546&rev=&no=0&P=R&KLOG=177
オブジェクトの存在確認、ありなし確認
図形の操作はいつも難しい。。。単純に、IsObject() を過信するとはまります。
http://okwave.jp/qa/q1715453.html
http://d.hatena.ne.jp/bs_n/20110929/1317300950
http://officetanaka.net/excel/vba/function/IsObject.htm

ファイル操作

上書きセーブをするかしないかをダイアログボックスで表示
'セーブ
  'http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_msgbox.html
  keizoku = MsgBox("上書きセーブしますか？", vbYesNo)    
    Select Case keizoku    
        Case vbYes  '            
            ActiveWorkbook.Save                        
        Case vbNo        
            MsgBox ("上書きセーブを中止しました。")            
        Case Else       
    End Select
ファイル名　取り出し -> FSO.GetBaseName(strFileName)

フルパスから拡張子の付いてないファイル名を取得するには？
ファイルの存在を調べる

ファイルの存在を調べる
読み取り専用で開く

  Workbooks.Open Filename:=FLName, ReadOnly:=True

フォルダ操作

BrowseForFolder メソッド

BrowseForFolder メソッド
フォルダを選択するダイアログ

フォルダを選択するダイアログ
カレントフォルダの名称を取り出す

'http://officetanaka.net/excel/vba/function/InStr.htm
    Dim objFs As Object
    Dim objFld As Object

    strPath = Range("B1").Value
    MsgBox strPath
    
    Set objFs = CreateObject("Scripting.FileSystemObject")
    Set objFld = objFs.GetFolder(strPath)
    MsgBox objFld.Name
フォルダのパス名からカレント？フォルダだけを抜き出す InStrRev利用

  MsgBox Mid(strPath, InStrRev(strPath, "") + 1, 20)
複数の階層が無い状態でフォルダ作成
これはすばらしい！あとは、ネットワークドライブ（\\が頭につく）にどう対応するか。
→どうも、CreateFolderしてやるときにネットワークドライブ名を負荷してあげればいいみたいですねｂ
http://www.excel.studio-kazu.jp/kw/20070727144955.html
フォルダ作成とか
http://officetanaka.net/excel/vba/tips/tips91.htm#folder

ワークシート関数

ワークシート関数
http://officetanaka.net/excel/vba/tips/tips106.htm
「計算の範囲であるSUM関数の引数を自動取得する」があまりにも華麗で感動しますｂ

名前定義関連

名前の定義
Names.Add Name:="Sample", RefersTo:="=$A$1"

http://www.moug.net/tech/exvba/0050159.html

名前定義の削除
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        n.Delete
    Next

http://trash-area.com/archives/677

名前定義後はRangeで扱える
Range("範囲").Select　'とか
Range("範囲").Address

http://www.moug.net/tech/exvba/0050107.html

VBA記載の問題

ダブルコーテーションは２つ重ねる

URLエンコード、デコード

URLエンコード、デコード – 漢字を含むURL、特にフォルダ名称を漢字に変換する
http://www.relief.jp/itnote/archives/003799.php

Public Function URL_Decode(ByVal strOrg As String) As String

　With CreateObject("ScriptControl")
　　.Language = "JScript"
　　URL_Decode = .CodeObject.decodeURI(strOrg)
　End With

End Function

未整理

セルにエラーが出ているときの処理
http://www.serpress.co.jp/excel/vba031.html

Outlook関連

Excel VBAからOutlookを操作する際のURL走り書き
Outlookのフォルダー を 探ってみた アイテムとサブフォルダー – ken3memo (三流君)
_MailItem インターフェイス
MSDN_AppointmentItem プロパティ (Microsoft.Office.Interop.Outlook)
電子メール アドレスの情報にアクセスしようとしているか、自分の代わりに電子メールを送信しようとしているプログラムに関する警告が表示される
Outlookの二重起動をしない

2012/05/04 (金) 01:29 作成 / 2013/10/31 (木) 01:29 更新 | カテゴリー: EXCEL