Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7全般 : Excel 2016)
シートを変数にいれた書き方
投稿日時: 18/09/06 15:16:22
投稿者: いわちゃん

シートを変数にいれた書き方がわかりません。
 
 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  Sheets(変数) & "!R2C1:R120C36", Version:=xlPivotTableVersion12).CreatePivotTable _
  TableDestination:=Sheets("作業用").Cells(3, 1), TableName:="ピボットテーブル1", DefaultVersion _
  :=xlPivotTableVersion12
 
 
2行目に変数ではなく、正しいシート名記載すると動きます。
 
変数を使用したい場合、どのように書いたらいいかご教示お願いします。
 
よろしくお願い致します。

回答
投稿日時: 18/09/06 15:55:13
投稿者: TAKA君

 
変数にシート名を入れる

Sub TEST()
    Dim S As String
    S = "作業用"
    Sheets(S).Select
End Sub

 
変数にシートそのものを入れる
Sub TEST2()
    Dim S As Worksheet
    Set S = Sheets("作業用")
    S.Select
End Sub


 
参考にしてください

投稿日時: 18/09/06 16:09:07
投稿者: いわちゃん

TAKA君さま
 
ご返信ありがとうございます。
 
説明不足で申し訳ありません。
変数は、セット済みです。
 
 
変数だと動かず、困っております。
Sheets(変数)を使用せずにシート名をいれると動きます。。
 
Sheets(変数) & "!R2C1:R120C36"
 
どうぞよろしくお願い致します。

回答
投稿日時: 18/09/06 16:18:25
投稿者: 虎

初心者なので見当違いなことを言っていたらすみません(^^;
 
  Sheets(変数) & "!R2C1:R120C36"
 
ここは、
 
  Sheets(変数).Range("A2:AJ120")
 
のように、Rangeで指定することができた気がします。
試してはないですし、Rangeの範囲が合ってるか自信ないですが(^^;

回答
投稿日時: 18/09/06 16:31:12
投稿者: TAKA君

 
Sheets("Sheet1")
 

 
S = "Sheet1"
Sheets(S)
 
は全く同じなので
上なら動く、下では動かないというのはありえません。。
 
変数の中身の文字は本当に正しいですか?
 
それとも変数はもしかしたら数字で、Sheets(2)みたいな指定方法とかですか?
もしそうなら変数が文字列型で宣言されていたりすると動きません。
 
動かないというのは、エラーで止まるって事ですよね?
インデックスが有効範囲にありませんって出てますか?

回答
投稿日時: 18/09/06 16:49:59
投稿者: sk

引用:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
Sheets(変数) & "!R2C1:R120C36", Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=Sheets("作業用").Cells(3, 1), TableName:="ピボットテーブル1", DefaultVersion _
:=xlPivotTableVersion12

・変数のデータ型が不明。
 
・その時点で変数に格納されている値が不明。
 
例えば「変数のデータ型が String 型であり、アクティブブックの
いずれかのワークシートの名前が格納されている」場合と、
「変数のデータ型が Integer 型(あるいは Long 型)であり、
いずれかのワークシートのインデックスを示す数値が格納されている」場合が
考えられるわけで、そのどちらに該当するのかを具体的に明記されない限りは
話になりません。
 
例えば前者のケースなら、
 
引用:
Sheets(変数) & "!R2C1:R120C36"

変数 & "!R2C1:R120C36"
 
----------------------------------------------
 
とすれば済むことでしょう。

回答
投稿日時: 18/09/06 16:50:55
投稿者: Suzu

いわちゃん さんの引用:
SourceData:= Sheets(変数) & "!R2C1:R120C36"

 
SourceData:= "Sheets(" & 変数 & ")!R2C1:R120C36"
 
ではどうですか。

回答
投稿日時: 18/09/06 17:13:13
投稿者: sk

虎 さんの引用:
ここは、
  
  Sheets(変数).Range("A2:AJ120")
  
のように、Rangeで指定することができた気がします。

Range オブジェクトを渡すのも勿論可。
(Excel VBA ヘルプの記述に従うなら、文字列式を渡すよりも
こちらの方が真っ当なやり方と言える)
 
虎 さんの引用:
Rangeの範囲が合ってるか自信ないですが(^^;

範囲指定は合ってます。

回答
投稿日時: 18/09/06 17:23:05
投稿者: sk

sk さんの引用:
(Excel VBA ヘルプの記述に従うなら、文字列式を渡すよりも
こちらの方が真っ当なやり方と言える)

と思ったら、Excel 2010 以前と Excel 2013 以降で
引数 SourceData に関する説明や扱いが変更されていたようです。
 
MSDN より:
https://msdn.microsoft.com/ja-jp/vba/excel-vba/articles/pivotcaches-create-method-excel
 
引用:
SourceType が xlExternal でない場合は、 SourceData 引数が必要です。
範囲 ( SourceType が xlConsolidation または xlDatabase である場合)
または Excel ブックの接続オブジェクト ( SourceType が xlExternal である場合)
として渡される必要があります。
範囲として渡す場合は、文字列を使用してブック、ワークシート、
およびセル範囲を指定するか、名前付き範囲を設定して
名前を文字列として渡すことをお勧めします。
Range オブジェクトを渡すと、"型の不一致" エラーが
予期せず発生します。

回答
投稿日時: 18/09/06 17:25:41
投稿者: WinArrow
投稿者のウェブサイトに移動

いわちゃん さんの引用:

Sheets(変数) & "!R2C1:R120C36"

 
対応策1
変数 & "!R2C1:R120C36"
 
※こちらは、skさんと被っていますが・・・
 
対応策2
Sheets(変数).Name & "!R2C1:R120C36"

投稿日時: 18/09/07 14:36:48
投稿者: いわちゃん

皆さま
 
Sheets(変数).Range("A2:AJ120")
 
で、できました!ありがとうございます!!!!
 
 
もし、ご存知でしたら教えてください。
 
"!R2C1:R120C36"で動かないのは、なぜでしょうか??
 
よろしくお願い致します。

回答
投稿日時: 18/09/07 15:24:15
投稿者: WinArrow
投稿者のウェブサイトに移動

>対応策1
> 変数 & "!R2C1:R120C36"
 
↑正しいアドバイスではありませんでした。
 
セルをR1C1方式で指定する場合は
 
With Sheets(シート名)
    .Range(.Cells(2, 1), .Cells(12, 36))
 
という具合に記述します。

回答
投稿日時: 18/09/07 16:13:56
投稿者: sk

いわちゃん さんの引用:
もし、ご存知でしたら教えてください。
  
"!R2C1:R120C36"で動かないのは、なぜでしょうか??

それは当初のコードの
 
いわちゃん さんの引用:
Sheets(変数) & "!R2C1:R120C36"

が通らなかった理由を知りたいのか、
 
虎 さんの引用:
Sheets(変数).Range("A2:AJ120")


 
--------------------------------------------------
 
Sheets(変数).Range("R2C1:R120C36")
 
--------------------------------------------------
 
のように記述したけどエラーが出てしまう原因が知りたいのか、
どちらなのでしょうか。
 
いずれにせよ、
 
sk さんの引用:
・変数のデータ型が不明。
  
・その時点で変数に格納されている値が不明。

を明らかにされた方がよいと思います。

回答
投稿日時: 18/09/08 07:22:06
投稿者: simple

既に回答をいただいているところ恐縮です。
 
>"!R2C1:R120C36"で動かないのは、なぜでしょうか??

シート名!範囲 ではじめて意味を持つからです。
 
ワークシート上の計算式を入力するとき、
=!A1 とか
=!R2C3 とか使いますか?
それと同じですね。
 
-------------------------------
冒頭の
> Sheets(変数) & "!R2C1:R120C36"
> がなぜ動かないのか

という質問ですが、
 
アドレスを示す文字列でも、
範囲を示すRangeオブジェクトでもないからです。
 
・ Sheets(変数)はワークシートを表すオブジェクト
・ "!R2C1:R120C36" は文字列
その全く違う種類のものを & でくっつけようとしているからですね。
余り難しく考えないことです。

回答
投稿日時: 18/09/09 16:33:41
投稿者: mattuwan44

>シートを変数にいれた書き方がわかりません。
 
いわちゃんさんが、知りたいことは、
「シートを変数にいれる書き方」
ですよね?
 
「操作したいシート」と、
「操作したいシートの名前」との違いをまず認識してください。
名前は「文字」で表せますが、
シートそのものは文字ではありません。「操作の対象物」です。
そこの認識をはっきりしてみてください。
 
で、
改めて、マクロの記録の結果をみてみます。
 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R2C2:R39C7", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R1C1", TableName:="ピボットテーブル1", DefaultVersion _
        :=xlPivotTableVersion14
 
こちらの環境では上記のように記録されました。
ここで、「ダブルクォーテーションで括られた単語は値としての文字列」ということをご存知でしょうか?
 
s = "Sheet1"
 
と書いたら変数「s」に「Sheet1」という文字を代入するといういみですよね?
ということは、
"Sheet1!R2C2:R39C7" と記録されたものは文字列であり、
セル範囲をシートの情報を含めて指定しているということが分かります。
なので、これを、「Sheet2」に変えたりしたいので変数を使いたいということならば、
 
    s = "Sheet2"
 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        s & "!R2C2:R39C7", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R1C1", TableName:="ピボットテーブル1", DefaultVersion _
        :=xlPivotTableVersion14
という風に文字列を作ってやればよいことになります。
 
ただし、
VBAでは「操作対象物」を変数に入れることが可能ですので、
文字列をごちょごちょいじらないで、
操作対象物そのものを変数に入れるようにすることを覚えることをお勧めします。
 
例)マクロの記録の結果

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R2C2:R39C7", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet4!R1C1", TableName:="ピボットテーブル1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet4").Select
    Cells(1, 1).Select
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("支払先")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル1").PivotFields("金額"), "データの個数 / 金額", xlCount
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("データの個数 / 金額")
        .Caption = "合計 / 金額"
        .Function = xlSum
    End With
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("支出科目")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("補助科目")
        .Orientation = xlRowField
        .Position = 2
    End With
End Sub

 
上記を参考にコードを整理した結果
Sub サンプル()
    Dim pvCache As PivotCache           'ピボットテーブルの元ネタ
    Dim pvTable As PivotTable           '生成したピボットテーブル
    Dim rngSourceData As Range          'ピボットテーブルの元ネタが参照するセル範囲
    Dim rngTableDestination As Range    'ピボットテーブルの生成先
    

    'ピボットテーブルで集計するデータ元のセル範囲を変数に代入
    Set rngSourceData = ThisWorkbook.Sheets("Sheet1").Range("B2:G37")
    'ピボットテーブルを生成する先頭セル
    Set rngTableDestination = ThisWorkbook.Sheets("Sheet4").Range("A1")
    
    
    'ピボットテーブルの元ネタを生成し結果を変数に代入する(結果は目に見えないが情報はブックに含まれる)
    Set pvCache = ThisWorkbook.PivotCaches.Create( _
                  SourceType:=xlDatabase, _
                  SourceData:=rngSourceData)
    'ピボットテーブルをシート上に作る(次の行以降で使うので変数に代入する)
    Set pvTable = pvCache.CreatePivotTable(TableDestination:=rngTableDestination)

    '生成したピボットテーブルの各フィールドに各要素を配置
    With pvTable
        .PivotFields("支払先").Orientation = xlColumnField
        .AddDataField .PivotFields("金額"), "合計 / 金額", xlSum
        .PivotFields("支出科目").Orientation = xlRowField
        .PivotFields("補助科目").Orientation = xlRowField
    End With
End Sub

 
参考URL:
https://www.exvba.com/2320/
https://www.relief.jp/docs/018038.html
 
※ここまで書いてなんですが、
やりたいことは、
「ピボットテーブルの参照元を差し替えたい」
だと思うので、
1回手動でピボットテーブルを作っておけば、
あとは、
 
データソースの変更
 ↓
更新

 
という部分だけをマクロ(=操作の自動化)にしたらいいと思います。
参考になれば。
 
 

投稿日時: 18/09/14 12:27:35
投稿者: いわちゃん

皆さま
ありがとうございます!!!
 
お礼がおそくなり申し訳ありません。勉強になりました!!