セル -> コピー、切り取り、貼り付け

Posted by muchag | Excel VBA | 2016-03-19 (土) 10:30:45

大昔、VBA を始めた頃に、「マクロ記録」を利用していた名残なのか
コピーのときは、コピーしてペースト、というコードを書いてしまう癖があるな~。
でも、本当は、ただの貼り付けなら1行なのよね。

なので、今更ながら、まとめてみる。

【環境】
Excel: 2010
書式
  1. ' Range.Copy メソッド
  2. .Copy(Destination)
  3.  
  4. ' Range.Cut メソッド
  5. .Cut(Destination)
  1. Destination
    1. オプション
    2. バリアント型 (Variant)
    3. コピー先のセル範囲。この引数を省略すると、クリップボードにコピーされます。
  2. 戻り値
    1. バリアント型 (Variant)
  1. Worksheets("Sheet1").Range("A1:D4").Copy _
  2.     destination:=Worksheets("Sheet2").Range("E5")
貼り付け
Paste

クリップボードの内容をオブジェクトに転送します。

  1. object.Paste
  1. Worksheets("Sheet2").Range("E5").Paste
PasteSpecial

Range をクリップボードから指定範囲に貼り付けます。

  1. .PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
  1. Paste
    1. オプション
    2. XlPasteType
    3. 範囲の中で貼り付ける部分を指定します。
  2. Operation
    1. オプション
    2. XlPasteSpecialOperation
    3. 貼り付けの操作を指定します。
  3. SkipBlanks
    1. オプション
    2. バリアント型 (Variant)
    3. クリップボードに含まれる空白のセルを貼り付けの対象にしないようにするには、True を指定します。既定値は False です。
  4. Transpose
    1. オプション
    2. バリアント型 (Variant)
    3. 貼り付けのときにデータの行と列を入れ替えるには、True を指定します。既定値は False です。
XlPasteType
名前 説明
xlPasteAll -4104 すべてを貼り付けます。
xlPasteAllExceptBorders 7 輪郭以外のすべてを貼り付けます。
xlPasteAllMergingConditionalFormats 14 すべてを貼り付け、条件付き書式をマージします。
xlPasteAllUsingSourceTheme 13 ソースのテーマを使用してすべてを貼り付けます。
xlPasteColumnWidths 8 コピーした列の幅を貼り付けます。
xlPasteComments -4144 コメントを貼り付けます。
xlPasteFormats -4122 コピーしたソースの形式を貼り付けます。
xlPasteFormulas -4123 数式を貼り付けます。
xlPasteFormulasAndNumberFormats 11 数式と数値の書式を貼り付けます。
xlPasteValidation 6 入力規則を貼り付けます。
xlPasteValues -4163 値を貼り付けます。
xlPasteValuesAndNumberFormats 12 値と数値の書式を貼り付けます。
XlPasteSpecialOperation
名前 説明
xlPasteSpecialOperationAdd 2 コピーしたデータは、対象セルの値に追加されます。
xlPasteSpecialOperationDivide 5 コピーしたデータは、対象セルの値によって分割されます。
xlPasteSpecialOperationMultiply 4 コピーしたデータには、対象セルの値が掛けられます。
xlPasteSpecialOperationNone -4142 貼り付け操作で計算は行われません。
  1. With Worksheets("Sheet1")
  2.     .Range("C1:C5").Copy
  3.     .Range("D1:D5").PasteSpecial _
  4.         Operation:=xlPasteSpecialOperationAdd
  5. End With
Excel VBA | 2016-03-19 (土) 10:30:45 |

Posted by muchag | Excel VBA | 2016-03-05 (土) 8:56:11

【環境】
Excel:2013
指定
16進数

Web では、RGB の順序で指定するけど
VBA では、BGR の順序なので注意。

  1. ' 赤
  2. ' Web であれば、"#FF0000"
  3. TextBox.BackColor = "&H0000FF"
Excel VBA | 2016-03-05 (土) 8:56:11 |

宣言と初期化

Posted by muchag | Excel VBA | 2015-09-09 (水) 13:17:33

VB は独特なので、しばらく触らないとすぐ忘れる。。。
確かめずに記憶で書いているので怪しい。

【環境】
Excel:2010
基本
変数
  1. ' Dim 変数名 As データ型
  2.  
  3. ' プリミティブ
  4. Dim hoge As String
  5. hoge = "Yeah"
  6.  
  7. ' オブジェクト
  8. Dim hoge As Object
  9. Set hoge = Activesheet
  10. ... 処理...
  11. Set hoge = nothing ' 解放
1行にまとめる方法

VBAやVBScriptって、変数宣言と初期化がものっそい面倒なイメージがある。

C#やJavaみたいに「int num = 0;」みたいに書ければいいのに…って思っている方は多いのではないでしょうか?

Black Everyday Company:【VB系】変数の宣言と初期化を1行で書く(VB.NET,VBA,VBScript)

ずぅぅぅぅぅぅぅぅぅっと、そう思ってました!

  1. Dim foo As Integer: foo = 0
  2. Dim bar As String:  bar = "hogehoge"
  3.  
  4. Dim obj As Object: Set obj = WScript.CreateObject("WScript.Shell")

素晴らしい~♪
ありがとうございます!

Excel VBA | 2015-09-09 (水) 13:17:33 |

正規表現

Posted by muchag | Excel VBA | 2015-09-09 (水) 13:00:25

Excel VBA で 正規表現 を使う時がきた。

【環境】
Excel:2010
準備
参照設定

[ツール]-[参照設定]
Microsoft VBScript Regular Expressions 5.5
にチェック。

これで、下記のように宣言できる。

  1. Dim objRE As New RegExp

参照設定をしない場合は、下記のごとく。

  1. Dim objRE As Object
  2. Set objRE = CreateObject("VBScript.RegExp")
置換
HTML タグ除去
  1. Dim objRE As Object
  2. Set objRE = CreateObject("VBScript.RegExp")
  3. objRE.Pattern = "<[^>]*>"
  4. objRE.Global = True
  5.  
  6. Dim strHoge as String
  7. strHoge = "<p>hoge</p>"
  8. strHoge = objRE.Replace(strHoge, vbNullString)
参考サイト

マイナビニュース > パソコン:正規表現を用いてHTTPタグを除去するVBAを記述する

Excel VBA | 2015-09-09 (水) 13:00:25 |

引数があるメソッドの呼び出しエラー

Posted by muchag | Excel VBA | 2015-05-20 (水) 18:28:51

前から気になっていたことがやっと解決したのでメモ。

【環境】
Excel:2010
現象

以前から何度も出くわしているので、前の例は忘れてしまった。
今回の例。

クラスを作成して、setter を配置したところ
呼び出し元でエラー。

  1. class.setHoge (a)

記述して、改行すると、メソッド名と () の間にスペースが。。。

コーディング中に、「修正候補:=」みたいな感じの
「= がないよ~」という案内が出たと思うけど
確かなことは記憶がない。

お試し

「= がないよ~」と言われた気がするので、試しに無意味な変数をかましてみた。

  1. Dim a
  2. a = class.setHoge(a)

確かに、これだと動くw

解決策

でも気持ちが悪いなぁ、と思っていたら、ひょっこり正解に当たった。

あとは、VBAの場合戻り値のない関数を呼ぶ際には「Call」を
先頭につけたほうが安全です。
( なくても呼べる場合もありますが、基本はつけておく。 )

YAHOO! JAPAN 知恵袋:Excel2003 VBAのクラスメソッドの呼び出しに…

あw そゆこと?

  1. call class.setHoge (a)

確かに~!!! ちゃんと動いた。

昔はちゃんと call をつけていたけど、
call がなくても動くので、いつの間にか忘れてた。。。

Excel VBA | 2015-05-20 (水) 18:28:51 |

外部 UTF-8 ファイル

Posted by muchag | Excel VBA | 2015-05-20 (水) 16:45:27

毎回同じ質問を Google 先生にしているので、いい加減まとめておきます。。。

【環境】
Excel:2010
準備

文字コード UTF-8 に対応するためには、ライブラリの参照設定が必要。

Visual Basec Editor の
[ツール]-[参照設定]
[参照可能なライブラリ ファイル]
[Microsoft ActiveX Data Object *.* Library]
というのが複数並んでいるので、2.5以降にチェック。
(まぁ、最新がよいのではないでしょうか)

読み込み
文字コード変換
  1. Dim csv As String
  2.  
  3. With CreateObject("ADODB.Stream")
  4.     .Charset = "UTF-8"
  5.     .Open
  6.     .LoadFromFile "C:\hoge.csv"
  7.     csv = .ReadText
  8.     .Close
  9. End With

これで、変数 csv に、ファイル全体文字列が格納されているので
適宜整形して、利用。

改行コード変換

改行コード については、こんな感じ。

Excel 用に vbCrLf で統一しておく方が問題が出ないので
下記のように置換すると吉。

  1. csv = Replace(csv, vbLf, vbCrLf)
  2. csv = Replace(csv, vbCr, vbCrLf)
利用
CSV
  1. Dim arrLines As Variant
  2. arrLines = Split(csv, vbCrLf)
  3.  
  4. Dim arrFieldsAs Variant
  5. For index = 0 To UBound(arrLines)
  6.  
  7.     arrFields= Split(arrLines(index ), ",")
  8.  
  9. Next
書き出し

読み込み編とは時期が異なったので、書き方が全然違うけど、
そこは気にしない!

  1. Dim string as String
  2. Dim i As Long
  3. Dim j As Integer
  4.  
  5. Dim outStream As ADODB.Stream
  6. Set outStream = New ADODB.Stream
  7.  
  8. 'ADODB.Stream の設定
  9. With outStream
  10.     .Type = adTypeText
  11.     .Charset = "UTF-8"
  12.     .LineSeparator = adLF '改行コードをLFに指定
  13.     .Open
  14. End With
  15.  
  16. ' データの書き出し
  17. For i = 1 To 100
  18.     For j = 1 To 5
  19.         If j > 1 Then string = string & ","
  20.        
  21.         string = string & Cells(i, j).Value
  22.     Next j
  23.    
  24.     outStream.WriteText string , adWriteLine
  25. Next i
  26.  
  27. 'BOMを削る前処理
  28. outStream.Position = 0 'ファイル先頭にセット
  29. outStream.Type = adTypeBinary 'バイナリに変更
  30. outStream.Position = 3 'BOMの3バイト分スキップ
  31.  
  32. 'BOMを削った分をコピー
  33. Dim outStreamCopy As New ADODB.Stream
  34. outStreamCopy.Type = adTypeBinary
  35. outStreamCopy.Open
  36. outStream.CopyTo outStreamCopy
  37.  
  38. 'CSVファイルに保存
  39. outStreamCopy.SaveToFile Activebook.Path & "\text_utf8n.csv", adSaveCreateOverWrite
  40.  
  41. 'クローズ処理
  42. outStream.Close
  43. outStreamCopy.Close
  44. Set outStream = Nothing
  45. Set outStreamCopy = Nothing
ユーティリティー

毎回書いていられないので、ユーティリティークラスを作成。

utilUtf8
  1. Option Explicit
  2.  
  3. Public Function inputStream(ByVal filePath As String) As String
  4.    
  5.     Dim strWhole As String
  6.      
  7.     With CreateObject("ADODB.Stream")
  8.         .Charset = "UTF-8"
  9.         .Open
  10.         .LoadFromFile filePath
  11.         strWhole = .ReadText
  12.         .Close
  13.     End With
  14.  
  15.     strWhole = Replace(strWhole, vbLf, vbCrLf)
  16.     strWhole = Replace(strWhole, vbCr, vbCrLf)
  17.  
  18.     Set inputStream = strWhole
  19.        
  20. End Function
  21.  
  22. Public Function getOutStream(ByVal outStream As ADODB.Stream) As ADODB.Stream
  23.    
  24.     'ADODB.Stream の設定
  25.     With outStream
  26.         .Type = adTypeText
  27.         .Charset = "UTF-8"
  28.         .LineSeparator = adLF '改行コードをLFに指定
  29.         .Open
  30.     End With
  31.    
  32.     Set getOutStream = outStream
  33.        
  34. End Function
  35.  
  36. Public Sub outputStream(ByVal outStream As ADODB.Stream, ByVal filePath As String, ByVal noBom As Boolean)
  37.  
  38.     If noBom Then
  39.         'BOMを削る前処理
  40.         outStream.Position = 0 'ファイル先頭にセット
  41.         outStream.Type = adTypeBinary 'バイナリに変更
  42.         outStream.Position = 3 'BOMの3バイト分スキップ
  43.     End If
  44.  
  45.     'BOMを削った場合に備えてコピーを出力
  46.     Dim outStreamCopy As New ADODB.Stream
  47.     outStreamCopy.Type = adTypeBinary
  48.     outStreamCopy.Open
  49.     outStream.CopyTo outStreamCopy
  50.  
  51.     'ファイルに保存
  52.     outStreamCopy.SaveToFile filePath, adSaveCreateOverWrite
  53.    
  54.     outStreamCopy.Close
  55.     Set outStreamCopy = Nothing
  56.    
  57.     outStream.Close
  58.    
  59. End Sub
参考サイト

Excel VBAでUTF-8(BOM無し)に変換してCSV出力してみる
→書き出し編。参考というより、ソースを頂戴しました。ありがとうございます♪

Excel VBA | 2015-05-20 (水) 16:45:27 |

Excel VBA Index

Posted by muchag | Excel VBA | 2015-05-20 (水) 16:41:22

私が初めて仕事で携わった言語。
もう長いお付き合いになりますね。。。
今後ともよろしくお願いします!

丸っきりの書きかけ項目は Not found になる。
 

セル
  1. コピー、切り取り、貼り付け
Tips
  1. 外部 UTF-8 ファイル
  2. コントロールの動的配置
  3. 正規表現
  4. 宣言と初期化
  5. 入力規則の設定
  6. 配列
    1. 連想配列
  7. メソッドの引数
  8. クラス
    1. 雛型
  9. VBE のカラーリング

 

がい~ん!(はまったこと)
  1. 解決済み(たぶん)
    1. 引数があるメソッドの呼び出しエラー
  2. 未解決

 

参考

MSDN:Excel VBA リファレンス

Excel VBA | 2015-05-20 (水) 16:41:22 |

入力規則の設定

Posted by muchag | Excel VBA | 2012-03-25 (日) 16:15:31

エディタはやっぱりダークテーマ!

VBA でセルに入力規則を設定しようとしたら
はまったのでメモ。

【環境】
[Excel] 2003 SP2

入力規則を設定しようとして、Excel でマクロを記録した。

  1. Range("G6").Select
  2. With Selection.Validation
  3.     .Delete
  4.     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  5.     xlBetween, Formula1:="=セルの名前"
  6.    
  7.     細かいのは省略
  8. End With

 
これを基にして

  1. With Range("A1:A3").Validation
  2.     .Delete
  3.     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  4.     xlBetween, Formula1:="=セルの名前"
  5.    
  6.     細かいのは省略
  7. End With

 
こうしてみたら

実行時エラー 2147417848(80010108)
Addメソッドは失敗しました。Validationオブジェクト

こんなエラーが出た。
 
あれこれ調べてもサッパリ解決せず。

あ! っと思い、
試しに当該セル範囲を選択させてからの処理にしてみた。

  1. Range("A1:A3").Select
  2. With Selection.Validation
  3.     .Delete
  4.     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  5.     xlBetween, Formula1:="=セルの名前"
  6.    
  7.     細かいのは省略
  8. End With

 
無事に動いた・・・。
選択しないとだめなのねぇ。

Excel VBA | 2012-03-25 (日) 16:15:31 |

Fusion Tables -> マイマップからの移行 CSV 編

Posted by muchag | Excel VBA,Fution Tabels | 2011-07-17 (日) 22:35:23

Fusion Tables -> マイマップからの移行 KML 編

CSV

テキストファイル。

前回の記事で書いた Google マップの KML ファイルを
CSV 形式に変換して Excel で編集して
Fusion Tables に取り込むことができる。
 

KML の問題点

前回の記事に書いたけど
KML ファイルでマイマップの情報を Fusion Tables に移行させると
今まで付けてきたマーカーが、1種類に変換されてしまう。

そこで、KML ファイルからマーカーの情報を取り出して
ちゃんとテーブルから反映させるために
CSV 形式にて移行することにした。
 

変換作業

KML ファイルは XML 形式なので
CSV 形式にするには一手間必要。

そこで、その手間を Excel に担当してもらうことにした。
 

KML ファイルの取得

マイマップの KML ファイルの取得方法は
Fusion Tables -> マイマップからの移行 KML 編
参照。
 

CSV に変換

自作 Excel VBA マクロを用いる。
KML2CSV_0.0.2.xls

以前、WordPress プラグイン QuickTags 用の マクロ を公開したが
今回も、いつも情報やらライブラリやらをいただいてばかりの私のちっさな恩返しとして、
こんな私の稚拙なマクロでも役に立つ奇跡を祈りつつ公開してみる。
 

利用上の注意
  • このマクロを利用して何か問題・損害が発生しても私は一切関知しません
  • 必ず「自己責任」でどうぞ
  • 私が必要とする狭い利用例を元に製作してあるので、機能が足りないとか誤変換とかの可能性は大
  • Fusion Tables は、現在 Beta 版なので仕様変更の可能性は大。そうしたら使えなくなる可能性は極大

KML2CSV_0.0.2.zip (40,600byte)

Windows XP Pro SP3 & Excel 2003 VAB にて作成および動作確認。
Google Fusion Tables (Beta) にて動作確認。
2011-07-17 現在。
文字コードは UTF-8 。

【更新履歴】
v 0.0.2 2011-07-17
バグフィクス。

v 0.0.1 2011-07-17
新規作成。

 

完全自動変換


 

1.「KML => CSV 自動変換」ボタンをクリック(上図参照)

 

2..kml ファイルを選択する

 

3._auto.csv ファイルが .kml ファイルと同じディレクトリに保存される

 

テーブル手動編集

1.「KML ファイル読み込み」ボタンをクリック(上図参照)
CSV シートの初期化

CSV シートに残っている
前回の作業結果をクリアする。

もちろん初めて利用される方にはもんだいありませんが、
綺麗サッパリなくなるので、
2回目以降の方で不安な場合は、シートをコピーしておいてください。

 

マイマップから DL した .kml ファイルを指定

 

CSV シートに読み込まれる


これを読み込むとこうなる。

タグの除去

description カラムについて
独断と偏見により不要と思われるタグ系文字列を除去してあります。

あくまでも私の環境で不要と感じただけなので
もし、必要だと感じられる方がおられたらすみません・・・。
 

2.「CSV」シートを自身で編集
1行目はフィールド(カラム)名

マイマップから取得した .kml ファイルの項目を
私が勝手にカスタマイズして上図のように次のようなカラム名を付けてある。

  1. name タイトル部
  2. icon マーカー名称
  3. geometry ポイントの緯度・経度
  4. description 詳細テキストエリア内のテキスト

第4カラムの description のみ改行に合わせて
次々とセル分割して入れてある。

これを用途に合わせて変更する。

左図例では
description の代わりに
zip, phone, content
としている。

 
この例では、データが少ないのですんなりいくが
多い場合はカラムとデータがズレズレになるので
面倒でも編集する・・・orz
 

セル内改行

今回の例に合わせると
歌詞は1つのセルにまとめたい。
でも、表示するときは改行を入れたい。

そういうときは、Alt + Enter でセル内改行を利用する。

こうしておけば、本番でも1つの項目内で改行される。

 

3.「CSV ファイル保存」ボタンをクリック(上図参照)

KML ファイルと同じディレクトリに
ファイル名_manual.csv
という名前で保存される。

前述の例を変換するとこうなる。

  1. "name","icon","geometry","zip","phone","content"
  2. "富士山","volcano","<Point><coordinates>138.731567,35.362873,0.000000</coordinates></Point>","123-4567","0120-xxxx-xxxx","頭を雲の上に出し<br />四方の山を見下ろして<br />雷様を下に聞く<br />富士は日本一の山"
  3. "厳島神社","picnic","<Point><coordinates>132.319870,34.295929,0.000000</coordinates></Point>","","",""
  4. "ラベンダーの森","tree","<Point><coordinates>142.340302,43.366375,0.000000</coordinates></Point>","","",""

 
タグが入っている場合
属性値を囲む (ダブルクォーテーション)は
Fusion Tables 読み込み用に二重に書き換えられる。
 

アップロード

Google Fusion Tabels にアクセスして
左ペインの [New tables]-[Import table]

 
ブラウザによってちょっと表示が変わるが
(左図は Google Chrome)
ファイル選択ボタンから

 
先ほど作成した
ファイル名_manual.csv
を選択する。

 
今回作成した CSV ファイルは
カンマ区切り
UTF-8
なので、変更の必要なし。

「Next」

 
「Next」

 
「Next」

 
インポート成功!

 

マップ表示

マップを見るには
[Visualize]-[Map]

 
[Configure styles]

 
① Column タブをクリック

② Use icon specified in a column をチェック

③ ドロップダウンから icon を選択

④ save をクリック

 
元通りのアイコンでマイマップが表示された♪

Excel VBA,Fution Tabels | 2011-07-17 (日) 22:35:23 |

シートの保護

Posted by muchag | Excel VBA | 2010-06-15 (火) 18:01:01


今回 plugin » WP-AddQuicktag その2 で、久しぶりにExcel VBAを触った。
そうしたら、シートの保護について素敵な方法をめっけたのでメモ。

ユーザによるセル内容の変更を防ぐために
Excelのシートを保護する機能がある。

それをVBAから制御するには(例:ActiveSheet)

  1. ' シートの保護
  2. ActiveSheet.Protect
  3.  
  4. ' シートの保護解除
  5. ActiveSheet.Unprotect

パスワードを利用する場合は

  1. ' シートの保護
  2. ActiveSheet.Protect Password:="hoge"
  3.  
  4. ' シートの保護解除
  5. ActiveSheet.Unprotect Password:="hoge"

という風にするわけだが
保護をかけてしまうと、ユーザはもちろんVBAからも制御不能となる。

今回見つけたのは、ユーザからは保護しつつVBAからは制御可能な命令。
なんて便利な・・・。

  1. ActiveSheet.Protect UserInterfaceOnly:=True
Excel VBA | 2010-06-15 (火) 18:01:01 |
次ページへ »