トップ 差分 一覧 ソース 検索 ヘルプ PDF RSS ログイン

Excel

[カテゴリ:Excel]
[カテゴリ:言語]

操作 Tips

計算式の基本、相対/絶対参照、A1/R1C1形式

「相対参照」と「絶対参照」
http://www.asahi-net.or.jp/~ef2o-inue/shiki/sub03_010.html

A1形式とR1C1形式
http://www.asahi-net.or.jp/~ef2o-inue/shiki/sub03_010_02.html

実行中のマクロを強制終了する。(無限ループに入ったときの為に)

[Esc] もしくは、[Ctrl]+[Break]

配列数式

 =SUM(IF((C3:C12="A")*(D3:D12="あ"),E3:E12))
[Ctrl] + [Shift] + [Enter]

配列研究室 STEP 1
http://www.clayhouse.jp/array/array01_d.htm

単一条件なら、SUMIFとかで代用可能(しかも高速)

複数条件でも、Excel2007からは、AVERAGEIFS,COUNTIFS,SUMIFS で置き換え可能なケースも多くあり、こちらのほうが高速

Office TANAKA - Excel 2007レビュー[新しいワークシート関数]
http://officetanaka.net/excel/excel2007/010.htm

数式を見えるようにする。

[shift] + [ctrl] + `

指定セルの情報を得る。

CELL 関数 - Excel - Office.com
http://office.microsoft.com/ja-jp/excel-help/HP010062392.aspx

TYPE 関数 - Excel - Office.com
http://office.microsoft.com/ja-jp/excel-help/HP010062400.aspx?CTT=3

情報関数 - Excel - Office.com
http://office.microsoft.com/ja-jp/excel-help/HP010079185.aspx?CTT=3

VBA Tips

パフォーマンスを最適化するヒント

Microsoft - Excel 2010 のパフォーマンス: パフォーマンスの問題を最適化するヒント

http://msdn.microsoft.com/ja-jp/library/ff726673.aspx#xlArraySumProduct

Office TANAKA - Excel VBA高速化テクニック[目次]

http://officetanaka.net/excel/vba/speed/index.htm

Evaluate関数(省略形 [ ])

Excel VBA を学ぶなら moug モーグ | 即効テクニック | どんなワークシート関数でも実行する
http://www.moug.net/tech/exvba/0100045.html

VBA研究室 Part 1
http://www.clayhouse.jp/vba/vba01.htm

全シートの左上をアクティブにする。

Set book = xxx
' 全シートの左上をアクティブに。
Dim sheet_wk As Worksheet
book.Activate
For Each sheet_wk In book.Sheets
    If sheet_wk.Visible = True Then
        sheet_wk.Select
        sheet_wk.Range("A1").Select
    End If
Next

なお、画面更新を禁止して、つまり
Application.ScreenUpdating = Flase
を行なってからだと、期待通りに動作しない。
(アクティブセルは、A1に移動するが、表示位置が移動しない)

いい方法ないのかな。。。

シートが存在するかチェック

Function FuncExistSheet(i_book As Workbook, i_sheet_name As String) As Boolean

    FuncExistSheet = False
    Dim idx As Integer
    For idx = 1 To i_book.Sheets.Count
        If UCase(i_book.Sheets(idx).Name) = UCase(i_sheet_name) Then
            FuncExistSheet = True
            Exit Function
        End If
    Next

End Function

なるほど、こういうチェック方法もあるのか、これのほうが速いな。

VBA研究室 Part 1
http://www.clayhouse.jp/vba/vba01.htm

sheetname = "sheet33"
If Evaluate("ISREF(" & sheetname & "!A1)") Then
    シートあった。
Else
    シートなかった。
End If

再計算の制御

再計算モードの制御

Application.Calculation = xlCalculationManual      ' 自動で再計算
Application.Calculation = xlCalculationAutomatic   ' 手動で再計算

再計算を行う
(手動再計算モードにしておき、これを実行することで、再計算のタイミングをVBAから管理できる)

Application.Calculate

(自動/手動再計算モードに関係なく)シート単位で再計算の要/否/を制御

worksheet.EnableCalculation = True/Flase

シートに含まれる全てのシェープを削除

sheet.Shapes.SelectAll
Application.Selection.Delete

上記でよいはずなのだけど、Excel2002でメモリエラーになった。
理由はよくわからないが・・・

やむを得ず、1つずつ削除

Dim shape_wk As Shape
For Each shape_wk In sheet.Shapes
    If shape_wk.Type = msoAutoShape Then
        shape_wk.Delete
    End If
Next

試していないが、複数選択して一括で削除でもメモリエラーを回避できるのかも。。。

未検証だが、以下によると・・・

VBA研究室 Part 3
http://www.clayhouse.jp/vba/vba03.htm

ActiveSheet.DrawingObjects.Delete

で代用できるらしい。

画面更新、自動再計算 の制御

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = Flase
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

セルの値や書式を削除

メソッド 説明
.ClearContents セルの値を削除
.Clear セルの値&書式を削除
.Delete セル自身を削除

Deleteには副作用がある。
そのセルを参照している数式が壊れる。
つまり「セル自身が無くなる=参照先セルが無くなる」
となり、参照元セルが「#REF!」になってしまう。

VBAシート初期化時のプロパティ解説
http://chaichan.web.infoseek.co.jp/vbtips/VBMemo2006101101.htm

セルの値データ を 2次元配列 に格納して操作する

個々のセルに順次アクセスするのと比較すると、まとめて処理できるので高速である。

Dim src_range As Range    ' 処理対象範囲
Dim data_arr As Variant   ' 処理対象範囲の値データを格納する2次元配列

' 処理対象範囲を決める
Set src_range = src_sheet.Range("A1:C20")

' 処理対象範囲の値データを2次元配列にコピー
data_arr = src_range.Value

' 2次元配列にアクセス
Dim col As Integer
Dim row As Integer
For row = 1 To UBound(data_arr, 1)
    For col = 1 To UBound(data_arr, 2)
        data_arr(row, col) = 0
    Next
Next

' 2次元配列の値データを処理対象範囲に上書き
src_range.Value = data_arr

他ブックのマクロを呼び出す

Application.Run"ブック名!マクロ名( )" 
Call Application.Run("book1!sub1", 引数, 引数)

ブック名はシングルクウォートで囲むほうがよい。

ExcelマクロをWSHで実行 - 翠天:
http://suiten.wig.nu/index.php?itemid=289

ファイルの存在チェック

If Dir("c:\xxx.xxx") <> "" Then
    MsgBox "存在する"
Else
    MsgBox "存在しない"
End If

コマンドラインの取得

[VB]コマンドラインの取得
http://www.ops.dti.ne.jp/~allergy/vb/vbvba.html

http://oshiete1.goo.ne.jp/kotaeru.php3?q=1604491
WindowsAPIのGetCommandLine

コマンドラインの引数を取得する方法
http://www.amy.hi-ho.ne.jp/pepa/pc/tips/excel/xl_vba51.html

調べた結果、Excelのexeパスに続いて、独自のパラメータを付与
しようとしても、それはまずExcelが解釈しようとするので
やり方としてはまずそう・・・

こっちが無難か・・・

VBAでのコマンドライン引数の取得
http://chaichan.web.infoseek.co.jp/vbtips/VBMemo2006082401.htm

メッセージループ(DoEvents)

DoEventsが思い出せなかった。。。
備忘メモ

VBAでシートコピーするとグラフのXY軸タイトルが消える?

シートコピーのマクロを組んだところ、シート上に配置していたグラフのXY軸のタイトルが、コピー先では消えてしまう現象が発生した。

環境
-Excel2000

状況
-「ブックAのシートa」を「ブックB」にコピーするという処理を以下のように書いた。

src_workbook.Sheets(src_sheet_name).Copy before:=dst_workbook.Sheets(1)

-ちなみに、手操作でシートコピーすると上手くコピーできる。
-「マクロの記録」で覚えさせたマクロを実行しても上手くコピーできる。

対策
-コピー元シートを明示的にアクティブにすることで回避できた。

Call src_workbook.Sheets(src_sheet_name).Activate
src_workbook.Sheets(src_sheet_name).Copy before:=dst_workbook.Sheets(1)

解決できたから良いのだけど。
これってExcelのバグ?なのだろうか。。。

配列

VBAから使える配列のまとめ

種類 *1:動的*2:index指定*3:key指定説明
Array × × 要素は定数でしか指定できない
Dim × ×
ReDim ×
Collection
Dictionary× 正確にはWSHのオブジェクト

 *1:要素数を動的に変更可能か
 *2:配列要素を番号指定可能か
 *3:要素にキーを付けることが可能か

Sub test_Array()
    Debug.Print "Array::::"
    '宣言
    Dim arr As Variant
    '代入
    arr = Array("東京", "大阪")
    '単一参照
    Debug.Print arr(0) & arr(1)
    '変更
    arr(1) = "オオサカ"
    '追加 - 不可 / Array(arr, "名古屋") なんてしても期待した結果にはならない
    '削除 - 不可
    '全参照
    For idx = LBound(arr) To UBound(arr)
        Debug.Print arr(idx)
    Next
End Sub

Sub test_Dim()
    Debug.Print "Dim::::"
    '宣言
    Dim arr(2) As String
    '代入
    arr(1) = "東京"
    arr(2) = "大阪"
    '単一参照
    Debug.Print arr(1) & arr(2)
    '変更
    arr(2) = "オオサカ"
    '追加 - 不可
    '削除 - 不可
    '全参照
    For idx = 1 To UBound(arr)
        Debug.Print arr(idx)
    Next
End Sub

Sub test_ReDim()
    Debug.Print "ReDim::::"
    '宣言
    Dim arr() As String
    ReDim arr(2)
    '代入
    arr(1) = "東京"
    arr(2) = "大阪"
    '単一参照
    Debug.Print arr(1) & arr(2)
    '変更
    arr(2) = "オオサカ"
    '追加
    ReDim Preserve arr(3)
    arr(3) = "名古屋"
    '削除 - 不可
    '全参照
    For idx = 1 To UBound(arr)
        Debug.Print arr(idx)
    Next
End Sub

Sub test_Collection()
    Debug.Print "Collection::::"
    '宣言
    Dim arr As Collection
    Set arr = New Collection
    '代入
    Call arr.Add("東京", "tokyo")
    Call arr.Add("大阪", "oosaka")
    '単一参照
    Debug.Print arr(1) & arr(2)
    Debug.Print arr("tokyo") & arr("oosaka")
    '変更 / 実際には削除&追加
    Call arr.Remove("oosaka")
    Call arr.Add("オオサカ", "oosaka")
    '追加
    Call arr.Add("名古屋", "nagoya")
    '削除
    Call arr.Remove("tokyo")
    '全参照 / idxでまわせるし、For Each でもまわせる
    For idx = 1 To arr.Count
        Debug.Print arr(idx)
    Next
    Dim str_wk As Variant
    For Each str_wk In arr
        Debug.Print str_wk
    Next
    'キー存在チェック / 不細工。。。参照してみてエラーかどうかチェック
    Dim bflg As Boolean
    bflg = True
    On Error Resume Next
    Call arr("nagoya")
    If Err.Number = 5 Then bflg = False
    On Error GoTo 0
    Debug.Print bflg
End Sub

Sub test_Dictionary()
    Debug.Print "Dictionary::::"
    '宣言
    Dim arr As Object
    Set arr = CreateObject("Scripting.Dictionary")
    '代入
    arr("tokyo") = "東京"
    arr("oosaka") = "大阪"
    '単一参照
    Debug.Print arr("tokyo") & arr("oosaka")
    '変更
    arr("oosaka") = "オオサカ"
    '追加
    arr("nagoya") = "名古屋"
    '削除
    arr.Remove ("tokyo")
    '全参照 - WSH上では for each でもまわせるのだけど。。
    Dim keys As Variant
    keys = arr.keys
    For idx = 0 To arr.Count - 1
        Debug.Print arr(keys(idx))
    Next
    'キー存在チェック
    Debug.Print arr.Exists("nagoya")
End Sub

動的配列(ReDim)

ReDimで配列要素を拡張できる。

' 指定配列にitemを追加
Sub CArrayItemAdd(arr As Variant, item As Variant)
    Dim max As Integer
    max = UBound(arr)
    ReDim Preserve arr(max + 1)
    arr(max + 1) = item
End Sub

Sub test()

    ' 動的配列として宣言 
    Dim hoge() As String

    ' 要素数2で確保
    ReDim hoge(2)

    ' 普通にデータセット
    hoge(1) = "hoge1"
    hoge(2) = "hoge2"

    ' 現在の要素数を越えてデータセット
    Dim addidx As Integer
    addidx = UBound(hoge) + 1
    ReDim Preserve hoge(addidx)
    hoge(addidx) = "hoge3"

    ' 現在の要素数を越えてデータセット(関数版)     
    Call CArrayItemAdd(hoge, "hoge4")

    ' 表示     
    MsgBox hoge(1) & hoge(2) & hoge(3) & hoge(4)

End Sub

ただし、これをユーザ定義型で行うとCArrayItemAddで「パブリックオブジェクトモジュールで定義されたユーザ定義型に限り、変数に割り当てることができ、実行時バインディングの関数に渡すことが出来ます。」エラーが発生する。

調べたがいまいち理解できてない。
どうやら、ユーザ定義型ではなくクラスモジュールとして型を作ってやればうまく動くらしい。

ユーザ定義型の実装が特殊で、オブジェクトとしてうまく扱ってくれない?っぽい?という理解でよいのか??

動的配列(Collection)

動的配列と言えば、ReDim?それともCollection?
http://members3.jcom.home.ne.jp/daruma_kyo/info/redim_or_collection.html

Collectionでも、ユーザ定義型は配列化できないらしい。
ユーザ定義型ってだめじゃん!!

ps.クラスならCollectionで配列化できる。

Excel 配列研究室

http://www.clayhouse.jp/array/array.htm

Workbookを閉じるときの保存問合せを表示させない方法

編集済みのWorkbookを閉じようとすると、保存問合せが表示される。
これを表示せず、閉じるで即終了させる方法

ThisWorkbook に以下を記述

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Saved = True
End Sub

ようは、閉じるの直前のイベント処理で、保存したこと、にしてるだけ。

セル選択・参照Tips

セルの選択・参照方法

セルを選択・参照する方法 - アプリケーションとしてのVBA:
http://www.k1simplify.com/vba/tipsleaf/leaf21.html

セル指定から列や行指定に拡張する方法

以下を使う。

range.EntireColumn
range.EntireRow

例)セルを指定してから、これを列に拡張してからの列削除

Dim ranges As Range
Set ranges = Union(Range("A1"), Range("H7"))
ranges.EntireColumn.Delete

範囲(Range)の左上、右下セル取得(2008/01/29)

範囲の左上セル取得
・range.Cells(1, 1)

範囲の右下セル取得
・range.Cells(range.Rows.Count, range.Columns.Count)
・range.SpecialCells(xlCellTypeLastCell)

A1←→R1C1形式、相対←→絶対参照 の変換方法

Application.ConvertFormula関数を使う。

Office TANAKA - Excel VBA Tips[相対参照と絶対参照を変換する]
http://officetanaka.net/excel/vba/tips/tips117b.htm

列番号(数字) を 列番号(英字) に変換、もしくはその逆

その前に・・・
worksheet.Cells(1, 1) は Cells(1, "A") と書くこともできます。

---
列番号(数字) を 列番号(英字) に変換

Excel で列番号を英文字に変換する方法
http://support.microsoft.com/kb/833402/ja

列番号を文字列・アルファベットに変換する:Excel エクセルの使い方-ユーザー定義関数/VBA
http://www.relief.jp/itnote/archives/003478.php

列のアルファベット指定と番号指定の変換
http://chaichan.web.infoseek.co.jp/vbtips/VBMemo2009-09-06.htm

---
列番号(英字) を 列番号(数字) に変換

列を表す英文字を列番号に変換: やむえむのExcel VBAメモ
http://yumem.cocolog-nifty.com/excelvba/2010/03/post-a3b4.html

列のアルファベット指定と番号指定の変換
http://chaichan.web.infoseek.co.jp/vbtips/VBMemo2009-09-06.htm

シートを削除(削除確認メッセージを表示を抑制あり)

Application.DisplayAlerts = False
Worksheets("Sheet1").Delete
Application.DisplayAlerts = True

Excelマクロが急にエラーになり始める(2008/01/31)

急に一般保護違反が出たりおかしくなる。

全てのマクロをエクスポートして
インポートしなおす。
参照も設定しなおす。

ほぼ、これで回避できたのだが・・・

ユーザ関数で、MSComctlLib.ListView を引数で渡しているのだが、
.Name が無いと言われるようになってしまった。

参照の設定が悪さしてそう?
参照設定には「ツール→参照設定」と
ツールボックスを右クリックの「その他コントロール」からの参照設定
があるがMSComctlLibはコントロールなので後者のみでよい。

それでもダメな場合・・・

ListViewをフォーム上に配置して、すぐに削除してみる。
(よくわからんが、これで回避できたこともある)

以下メモ

MO9OL:Microsoft Office 9.0 Object Library
C:\Program Files\Microsoft Office\Office\MSO9.DLL

MSR:Microsoft Scripting Runtime
C:\WINNT\system32\scrrun.dll

MWCC:Microsoft Windows Common Controls 6.0(SP6)
C:\WINNT\system32\MSCOMCTL.OCX

VBSからExcel操作

Option Explicit

Dim obj_application
Dim obj_workbook
Dim obj_worksheet_data
Dim idx

' obj_application = Excel.Application
' Excel.Workbook obj_workbook = obj_application.Workbooks("xxx.xls")
' Excel.Worksheet obj_worksheet = obj_workbook.Sheets("xxx")
' Excel.Range obj_range = obj_worksheet.Cells(row, col)


' Application生成
Set obj_application = CreateObject("Excel.Application")
obj_application.Visible = True

'新規Workbookを開く
Set obj_workbook = obj_application.Workbooks.Add()

' 不要なSheetを削除
MsgBox(obj_workbook.Sheets.Count)
obj_application.DisplayAlerts = False
Do While (obj_workbook.Sheets.Count <> 1)
	obj_workbook.Sheets(2).Delete()
Loop
obj_application.DisplayAlerts = True

' Sheet名付け
MsgBox(obj_workbook.Sheets.Count)
Set obj_worksheet_data = obj_workbook.Sheets(1)
obj_worksheet_data.Name = "data"

Dim ws
Set ws = obj_worksheet_data

' 配列データをシートへ複写
Dim data_a(5)
data_a(0) = "データ0"
data_a(1) = "データ1"
data_a(2) = "データ2"
data_a(3) = "データ3"
data_a(4) = "データ4"
ws.Cells(1,1).Resize(1,5).Value = data_a		' A1基準に、データ0〜4 をコピー
ws.Cells(2,1).Resize(1,4).Value = data_a		' A2基準に、データ0〜3 をコピー
ws.Cells(3,1).Value = data_a				' A3基準に、データ0 をコピー
ws.Range(ws.Cells(4,1), ws.Cells(4,5)).Value = data_a	' A4基準に、データ0〜4までコピー
ws.Cells(5,1).Resize(1,UBound(data_a)).Value = data_a		' A5基準に、データ0〜4 をコピー

' 2次元配列データをシートへ複写
Dim data_b(2,5)
MsgBox(LBound(data_b, 1) & "-" & UBound(data_b, 1))
MsgBox(LBound(data_b, 2) & "-" & UBound(data_b, 2))
data_b(0,0) = "データ0,0"
data_b(0,1) = "データ0,1"
data_b(0,2) = "データ0,2"
data_b(0,3) = "データ0,3"
data_b(0,4) = "データ0,4"
data_b(1,0) = "データ1,0"
data_b(1,1) = "データ1,1"
data_b(1,2) = "データ1,2"
data_b(1,3) = "データ1,3"
data_b(1,4) = "データ1,4"
ws.Cells(11,1).Resize(2,5).Value = data_b		' A11基準に、データ0,0〜1,4 をコピー
ws.Cells(14,1).Resize(2,4).Value = data_b		' A14基準に、データ0,0〜1,3 をコピー
ws.Cells(17,1).Value = data_b				' A17基準に、データ0,0 をコピー
ws.Range(ws.Cells(20,1), ws.Cells(21,5)).Value = data_b	' A20基準に、データ0,0〜1,4までコピー
ws.Cells(23,1).Resize(UBound(data_b, 1),UBound(data_b, 2)).Value = data_b		' A23基準に、データ0,0〜1,4 をコピー

WScript.Sleep(1000)

'obj_application.Quit()
'Set obj_application = Nothing

リンク集


個別

一文字に三つ星: [エクセル] RangeオブジェクトのFormula/Value/Textプロパティ
http://arrow3.way-nifty.com/shige/2004/08/excelvba_range.html

VBAを使うには
http://www.bekkoame.ne.jp/~poetlabo/COMP/Excel/VBALEC/

擬似からの脱却(擬似コントロール配列の話)
http://www.h3.dion.ne.jp/~sakatsu/Breakthrough_P-Ctrl_Arrays.htm

VBA応用(ユーザーフォームのコントロールを配列にする。)http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_100_040.html

モーグスキルアップ講座 - クラスモジュールを使った究極のVBAプログラミング http://www.moug.net/skillup/opm/opm08-01.htm

オートシェイプを描画する
http://www.ops.dti.ne.jp/~allergy/vb/vbvba.html#autoshape


総合

@IT:Windows TIPS − TIPSディレクトリ > プラットフォーム別 > Office > Excelhttp://www.atmarkit.co.jp/fwin2k/win2ktips/index/tipsdir/dir215.html

デジタルARENA / Excel INDEX
http://arena.nikkeibp.co.jp/tec/excel/

ExcelQ&A掲示板(エクセル質問掲示板)
http://excelfactory.net/excelboard/

Excel VBA モーグ即効テクニック集
http://www.moug.net/tech/exvba/index.htm

エクセルスマイル
http://www.happy2-island.com/excelsmile/

Office TANK Excel テクニック集
http://www.officetanaka.net/excel/index.htm

Excel VBA Parts Collection
http://t_shun.at.infoseek.co.jp/My_Page/Excel-VBA/vba_menu.htm

Excelでお仕事
http://www.asahi-net.or.jp/~ef2o-inue/top01.html

K窓 Excel
http://homepage2.nifty.com/kmado/kexcel.htm

MS-Excel2000スキルアップ講座/標準操作編
http://www.amy.hi-ho.ne.jp/pepa/pc/tips/excel/xl_vba.html

最終更新時間:2012年03月11日 02時48分04秒