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

Posted by muchag | Excel VBA |
初回投稿:2016-03-19 (土) 10:30:45 | 最終更新:2016-03-19 (土) 11:25:03

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

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

【環境】
Excel: 2010
書式
[evba language=”vb” firstline=”” highlight=””] ‘ Range.Copy メソッド
式.Copy(Destination)

‘ Range.Cut メソッド
式.Cut(Destination)
[/evba]

  1. Destination
    1. オプション
    2. バリアント型 (Variant)
    3. コピー先のセル範囲。この引数を省略すると、クリップボードにコピーされます。
  2. 戻り値
    1. バリアント型 (Variant)
[evba language=”vb” firstline=”” highlight=””] Worksheets(“Sheet1”).Range(“A1:D4”).Copy _
destination:=Worksheets(“Sheet2”).Range(“E5”)
[/evba]
貼り付け
Paste

クリップボードの内容をオブジェクトに転送します。
[evba language=”vb” firstline=”” highlight=””] object.Paste
[/evba]

[evba language=”vb” firstline=”” highlight=””] Worksheets(“Sheet2”).Range(“E5”).Paste
[/evba]
PasteSpecial

Range をクリップボードから指定範囲に貼り付けます。
[evba language=”vb” firstline=”” highlight=””] 式.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)
[/evba]

  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 貼り付け操作で計算は行われません。
[evba language=”vb” firstline=”” highlight=””] With Worksheets(“Sheet1”)
.Range(“C1:C5”).Copy
.Range(“D1:D5”).PasteSpecial _
Operation:=xlPasteSpecialOperationAdd
End With
[/evba]
Posted by muchag | Excel VBA |
初回投稿:2016-03-19 (土) 10:30:45 | 最終更新:2016-03-19 (土) 11:25:03

Posted by muchag | Excel VBA |
初回投稿:2016-03-05 (土) 8:56:11 | 最終更新:2016-03-05 (土) 8:56:11

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

Web では、RGB の順序で指定するけど
VBA では、BGR の順序なので注意。
[evba language=”vb” firstline=”” highlight=””] ‘ 赤
‘ Web であれば、”#FF0000″
TextBox.BackColor = “&H0000FF”
[/evba]

Posted by muchag | Excel VBA |
初回投稿:2016-03-05 (土) 8:56:11 | 最終更新:2016-03-05 (土) 8:56:11

宣言と初期化

Posted by muchag | Excel VBA |
初回投稿:2015-09-09 (水) 13:17:33 | 最終更新:2015-09-09 (水) 13:18:20

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

【環境】
Excel:2010
基本
変数
[evba language=”vb” firstline=”” highlight=””] ‘ Dim 変数名 As データ型

‘ プリミティブ
Dim hoge As String
hoge = “Yeah”

‘ オブジェクト
Dim hoge As Object
Set hoge = Activesheet
… 処理…
Set hoge = nothing ‘ 解放
[/evba]

1行にまとめる方法

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

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

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

ずぅぅぅぅぅぅぅぅぅっと、そう思ってました!
[evba language=”vb” firstline=”” highlight=””] Dim foo As Integer: foo = 0
Dim bar As String: bar = “hogehoge”

Dim obj As Object: Set obj = WScript.CreateObject(“WScript.Shell”)
[/evba] 素晴らしい~♪
ありがとうございます!

Posted by muchag | Excel VBA |
初回投稿:2015-09-09 (水) 13:17:33 | 最終更新:2015-09-09 (水) 13:18:20

正規表現

Posted by muchag | Excel VBA |
初回投稿:2015-09-09 (水) 13:00:25 | 最終更新:2018-09-17 (月) 10:40:28

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

【環境】
Excel:2010
準備
参照設定
[ツール]-[参照設定] Microsoft VBScript Regular Expressions 5.5
にチェック。

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


Dim objRE As New RegExp

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


Dim objRE As Object
Set objRE = CreateObject("VBScript.RegExp")
置換
HTML タグ除去

Dim objRE As Object
Set objRE = CreateObject("VBScript.RegExp")
objRE.Pattern = "<[^>]*>"
objRE.Global = True

Dim strHoge as String
strHoge = "

hoge

" strHoge = objRE.Replace(strHoge, vbNullString)
抽出
[123]abc[789]xyz
において、[] で括られたもの以外
つまり、abcxyz を抽出する場合。


Dim objRE As Object
Set objRE = CreateObject("VBScript.RegExp")
objRE.Pattern = "\[.*?\]"
objRE.Global = True

Dim strHoge as String
strHoge = "[123]abc[789]xyz"
strHoge = objRE.Replace(strHoge, vbNullString)

書いてみて思った。
やっていることは、HTML タグ除去と同じだw
考えてみれば当然なんだけど
せっかくなので残しておく。

抽出
[123]abc[789]xyz
ここから、123, 789 を抽出したい場合。


Dim objRE As Object
Set objRE = CreateObject("VBScript.RegExp")
objRE.Pattern = "\[(.*?)\]"
objRE.Global = True

Dim strHoge as String
strHoge = "[123]abc[789]xyz"
strHoge = objRE.execute(strHoge)

If strHoge.Count > 0 Then
	Dim index As Integer
	For index = 0 To strHoge.Count - 1
		MsgBox strHoge(index).SubMatches(0)
	Next index
End If
参考サイト

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

Posted by muchag | Excel VBA |
初回投稿:2015-09-09 (水) 13:00:25 | 最終更新:2018-09-17 (月) 10:40:28

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

Posted by muchag | Excel VBA |
初回投稿:2015-05-20 (水) 18:28:51 | 最終更新:2015-05-20 (水) 20:10:02

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

【環境】
Excel:2010
現象

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

クラスを作成して、setter を配置したところ
呼び出し元でエラー。
[evba language=”vb” firstline=”” highlight=””] class.setHoge (a)
[/evba]

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

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

お試し

「= がないよ~」と言われた気がするので、試しに無意味な変数をかましてみた。
[evba language=”vb” firstline=”” highlight=””] Dim a
a = class.setHoge(a)
[/evba] 確かに、これだと動くw

解決策

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

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

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

あw そゆこと?
[evba language=”vb” firstline=”” highlight=””] call class.setHoge (a)
[/evba] 確かに~!!! ちゃんと動いた。

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

Posted by muchag | Excel VBA |
初回投稿:2015-05-20 (水) 18:28:51 | 最終更新:2015-05-20 (水) 20:10:02

外部 UTF-8 ファイル

Posted by muchag | Excel VBA |
初回投稿:2015-05-20 (水) 16:45:27 | 最終更新:2018-09-16 (日) 21:36:23

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

【環境】
Excel:2010
準備

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

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

読み込み
文字コード変換
[evba language=”vb” firstline=”” highlight=””] Dim csv As String

With CreateObject(“ADODB.Stream”)
.Charset = “UTF-8”
.Open
.LoadFromFile “C:\hoge.csv”
csv = .ReadText
.Close
End With
[/evba] これで、変数 csv に、ファイル全体文字列が格納されているので
適宜整形して、利用。

改行コード変換

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

Excel 用に vbCrLf で統一しておく方が問題が出ないので
下記のように置換すると吉。
[evba language=”vb” firstline=”” highlight=””] csv = Replace(csv, vbLf, vbCrLf)
csv = Replace(csv, vbCr, vbCrLf)
[/evba]

利用
CSV
[evba language=”vb” firstline=”” highlight=””] Dim arrLines As Variant
arrLines = Split(csv, vbCrLf)

Dim arrFieldsAs Variant
For index = 0 To UBound(arrLines)

arrFields= Split(arrLines(index ), “,”)

Next
[/evba]

書き出し

読み込み編とは時期が異なったので、書き方が全然違うけど、
そこは気にしない!
[evba language=”vb” firstline=”” highlight=””] Dim string as String
Dim i As Long
Dim j As Integer

Dim outStream As ADODB.Stream
Set outStream = New ADODB.Stream

‘ADODB.Stream の設定
With outStream
.Type = adTypeText
.Charset = “UTF-8”
.LineSeparator = adLF ‘改行コードをLFに指定(adCRLF)
.Open
End With

‘ データの書き出し
For i = 1 To 100
For j = 1 To 5
If j > 1 Then string = string & “,”

string = string & Cells(i, j).Value
Next j

outStream.WriteText string , adWriteLine
Next i

‘BOMを削る前処理
outStream.Position = 0 ‘ファイル先頭にセット
outStream.Type = adTypeBinary ‘バイナリに変更
outStream.Position = 3 ‘BOMの3バイト分スキップ

‘BOMを削った分をコピー
Dim outStreamCopy As New ADODB.Stream
outStreamCopy.Type = adTypeBinary
outStreamCopy.Open
outStream.CopyTo outStreamCopy

‘CSVファイルに保存
outStreamCopy.SaveToFile Activebook.Path & “\text_utf8n.csv”, adSaveCreateOverWrite

‘クローズ処理
outStream.Close
outStreamCopy.Close
Set outStream = Nothing
Set outStreamCopy = Nothing
[/evba]

ユーティリティー

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

utilUtf8
[evba language=”vb” firstline=”” highlight=””] Option Explicit

Public Function inputStream(ByVal filePath As String) As String

Dim strWhole As String

With CreateObject(“ADODB.Stream”)
.Charset = “UTF-8”
.Open
.LoadFromFile filePath
strWhole = .ReadText
.Close
End With

strWhole = Replace(strWhole, vbLf, vbCrLf)
strWhole = Replace(strWhole, vbCr, vbCrLf)

Set inputStream = strWhole

End Function

Public Function getOutStream(ByVal outStream As ADODB.Stream) As ADODB.Stream

‘ADODB.Stream の設定
With outStream
.Type = adTypeText
.Charset = “UTF-8”
.LineSeparator = adLF ‘改行コードをLFに指定
.Open
End With

Set getOutStream = outStream

End Function

Public Sub outputStream(ByVal outStream As ADODB.Stream, ByVal filePath As String, ByVal noBom As Boolean)

If noBom Then
‘BOMを削る前処理
outStream.Position = 0 ‘ファイル先頭にセット
outStream.Type = adTypeBinary ‘バイナリに変更
outStream.Position = 3 ‘BOMの3バイト分スキップ
End If

‘BOMを削った場合に備えてコピーを出力
Dim outStreamCopy As New ADODB.Stream
outStreamCopy.Type = adTypeBinary
outStreamCopy.Open
outStream.CopyTo outStreamCopy

‘ファイルに保存
outStreamCopy.SaveToFile filePath, adSaveCreateOverWrite

outStreamCopy.Close
Set outStreamCopy = Nothing

outStream.Close

End Sub
[/evba]

参考サイト

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

Posted by muchag | Excel VBA |
初回投稿:2015-05-20 (水) 16:45:27 | 最終更新:2018-09-16 (日) 21:36:23

Excel VBA Index

Posted by muchag | Excel VBA |
初回投稿:2015-05-20 (水) 16:41:22 | 最終更新:2019-11-04 (月) 19:53:14

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

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

セル
  1. コピー、切り取り、貼り付け
Tips
  1. HTTP リクエスト
  2. 外部 UTF-8 ファイル
  3. コントロールの動的配置
  4. サブディレクトリ一覧を取得
  5. 正規表現
  6. 宣言と初期化
  7. 入力規則の設定
  8. 配列
    1. 連想配列
  9. メソッドの引数
  10. クラス
    1. 雛型
  11. VBE のカラーリング
番外:本体編
  1. 画像抽出

 

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

 

参考

MSDN:Excel VBA リファレンス

Posted by muchag | Excel VBA |
初回投稿:2015-05-20 (水) 16:41:22 | 最終更新:2019-11-04 (月) 19:53:14

入力規則の設定

Posted by muchag | Excel VBA |
初回投稿:2012-03-25 (日) 16:15:31 | 最終更新:2016-07-09 (土) 14:48:50

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

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

【環境】
[Excel] 2003 SP2

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

[evba]Range(“G6″).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”=セルの名前”

細かいのは省略
End With[/evba]  
これを基にして

[evba]With Range(“A1:A3″).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”=セルの名前”

細かいのは省略
End With[/evba]  
こうしてみたら

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

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

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

[evba]Range(“A1:A3″).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=”=セルの名前”

細かいのは省略
End With[/evba]  
無事に動いた・・・。
選択しないとだめなのねぇ。

Posted by muchag | Excel VBA |
初回投稿:2012-03-25 (日) 16:15:31 | 最終更新:2016-07-09 (土) 14:48:50

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

Posted by muchag | Excel VBA,Fution Tabels |
初回投稿:2011-07-17 (日) 22:35:23 | 最終更新:2013-04-20 (土) 3:47:03