現在、下記のようなコード書いて利用しています。
このコードを他の人がに転用する時に、指定箇所さえ書き換えれば簡単に転用できる!と言うようにしたいのです。
例えば
>Set myBook = Workbooks.Open(Filename:=myDir & "\" & myName)
>Set Copydata = SH1.Range("Z1").Resize(100, 1)
といった指定するような箇所(" "で囲った所)を先にまとめて定義しておくにはどう記述したらよいのでしょうか。
宜しくお願いします。
-------------------------------------------------------------
Private Sub 読込ボタン_Click() '他のBookからデータを転記するマクロ
Dim SH2 As Worksheet, SH1 As Worksheet
Dim GYO As Range, Copydata As Range
Dim myDir As String, myName As String, myBook As Workbook
Set SH2 = ThisWorkbook.Worksheets("情報シート")
'集計用のBookがあるフォルダ名を指定(このBookを格納している場所)
myDir = ThisWorkbook.Path
'他Bookのファイル名を指定(*.xls)
myName = Dir(myDir & "\" & "*.xls")
Do While myName <> ""
'このBook以外を対象
If myName <> ThisWorkbook.Name Then
'転記先[情報シート]の最終行を取得
Set GYO = SH2.Range("A65536").End(xlUp).Offset(1)
'他のBookを開いて変数に格納
Set myBook = Workbooks.Open(Filename:=myDir & "\" & myName)
'転記元を取得(Z列1行を基点に100行コピー)
Set SH1 = myBook.Worksheets("回答内容")
Set Copydata = SH1.Range("Z1").Resize(100, 1)
'転記先の最終次行に転記(行列入替で貼付)
Copydata.Copy
GYO.PasteSpecial Paste:=xlPasteValues, Transpose:=True
'開いた他Bookを閉じる
myBook.Close
End If
myName = Dir()
Loop
End Sub
-------------------------------------------------------------
No.5ベストアンサー
- 回答日時:
>このコードを他の人がに転用する時に、指定箇所さえ書き換えれば簡単に転用できる!
汎用性を持たせたいと言うことかな
Private Sub 読込ボタン_Click() '他のBookからデータを転記するマクロ
Dim SH2 As Worksheet, SH1 As Worksheet
Dim GYO As Range, Copydata As Range
Dim myDir As String, myName As String
Dim myBook As Workbook
Dim SH2_Name As String, SH1_Name As String
Dim Copydata_Home As String
'設定開始---------------------------------------
SH2_Name = "情報シート" '転記先シート名
SH1_Name = "回答内容" '転記元シート名
Copydata_Home = "Z1" '転記元の基点(セル)を指定
'設定終了---------------------------------------
Set SH2 = ThisWorkbook.Worksheets(SH2_Name)
'集計用のBookがあるフォルダ名を指定(このBookを格納している場所)
myDir = ThisWorkbook.Path
'他Bookのファイル名を指定(*.xls)
myName = Dir(myDir & "\" & "*.xls")
Do While myName <> ""
'このBook以外を対象
If myName <> ThisWorkbook.Name Then
'転記先[情報シート]の最終行を取得
Set GYO = SH2.Range("A65536").End(xlUp).Offset(1)
'他のBookを開いて変数に格納
Set myBook = Workbooks.Open(Filename:=myDir & "\" & myName)
'転記元を取得(Z列1行を基点に100行コピー)
Set SH1 = myBook.Worksheets(SH1_Name)
Set Copydata = SH1.Range(Copydata_Home).Resize(100, 1)
'転記先の最終次行に転記(行列入替で貼付)
Copydata.Copy
GYO.PasteSpecial Paste:=xlPasteValues, Transpose:=True
'開いた他Bookを閉じる
myBook.Close
End If
myName = Dir()
Loop
End Sub
設定開始~設定終了の間の3行を設定すれば、後は変更することなく
マクロを実行できますで良いのかな?
No.6
- 回答日時:
コード内で設定せずに、InputBoxを使って対話式にしたりとか、
Sub 例えば()
With Sheets.Add
.Name = "config"
.Range("A1:B1").Value = [{"フォルダ選択?(しない場合はこのBookのフォルダ)","yes"}]
.Range("A2:B2").Value = [{"転記先シート名?","情報シート"}]
.Range("A3:B3").Value = [{"転記元シート名?","回答内容"}]
.Range("A4:B4").Value = [{"転記元のセル?","Z1"}]
End With
End Sub
こんな「設定用シート」みたいな感じで外に出して、このシートを修正してもらい、
VBAからはその「設定用シート」の値を変数に取り込んで実行したりとか、も、考えられますね。
Option Explicit
Sub test()
Dim SH As Worksheet
Dim SH1 As Worksheet
Dim SH2 As Worksheet
Dim GYO As Range
Dim myDir As String
Dim myName As String
Dim shtName As String
Dim kiten As String
With ThisWorkbook
On Error GoTo errHndr
Set SH = .Sheets("config")
Set SH2 = .Sheets(SH.Range("B2").Value)
On Error GoTo 0
shtName = SH.Range("B3").Value
kiten = SH.Range("B4").Value
If SH.Range("B1").Value = "yes" Then
myDir = FDSELECT
Else
myDir = .Path
End If
End With
If Len(myDir) = 0& Then Exit Sub
Application.ScreenUpdating = False
myName = Dir(myDir & "\" & "*.xls")
Do While myName <> ""
'このBook以外を対象
If myName <> ThisWorkbook.Name Then
'転記先シートの最終行を取得
Set GYO = SH2.Range("A65536").End(xlUp).Offset(1)
'他のBookを開く
With Workbooks.Open(Filename:=myDir & "\" & myName, ReadOnly:=True)
On Error Resume Next
Set SH1 = .Worksheets(shtName)
On Error GoTo 0
If Not SH1 Is Nothing Then
'転記元を取得(?列1行を基点に100行コピー)
SH1.Range(kiten).Resize(100).Copy
'転記先の最終次行に転記(行列入替で貼付)
GYO.PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
Set SH1 = Nothing
End If
'開いた他Bookを閉じる
.Close False
End With
End If
myName = Dir()
Loop
errHndr:
Application.ScreenUpdating = True
If Err.Number <> 0 Then MsgBox "設定シートがありません。処理中止"
Set GYO = Nothing
Set SH = Nothing
Set SH2 = Nothing
End Sub
'---------------------------------------------------------------------
Private Function FDSELECT() As String 'フォルダ選択Function
Dim obj As Object
Dim ret As String
Set obj = CreateObject("Shell.Application") _
.BrowseForFolder(0, "SelectFolder", 0)
If obj Is Nothing Then Exit Function
On Error Resume Next
ret = obj.self.Path & "\"
If Err.Number <> 0 Then
ret = obj.Items.Item.Path & "\"
Err.Clear
End If
On Error GoTo 0
Set obj = Nothing
FDSELECT = ret
End Function
No.4
- 回答日時:
関数(手続きだけど)の例
Private Sub 読込ボタン_Click()
LoadingHandler(myDir & "\" & myName,"Z1")
End Sub
Sub LoadingHandler( AFileName As String _
,ARange As String)
省略
Set myBook = Workbooks.Open(Filename:=AFileName)
Set Copydata = SH1.Range(ARange).Resize(100, 1)
省略
End Sub
クラスは、さっきのURLの2ページ目に書き方が簡潔に書いてありますので補足しません。
No.3
- 回答日時:
なるほど。
・パラメータを与えるだけで動くような関数を作成するとか。
・クラスにしてしまうとか。
参考:
https://codezine.jp/article/detail/499
クラスにしてしまえば、表面のコードは、かなりシンプルになると思います。
クラスを作成するに当たって、手っ取り早いのは、最初にプロパティやメソッドなどの一覧をつくってしまいます。
で、あとから1つずつ、実装していけばよいです。
No.2
- 回答日時:
こんにちわ
どういう書き方がいい書き方かというのは分かりませんが
私がよく行う方法は
'---------○○○○○○○の設定-----------------
'---------○○○○○○○の設定ココまで-----------------
の様にブロック化してしまいます。
沢山コメントがあれば後で読んだ時に自分も分かるし
自分以外の人が修正する場合も分かりやすいと思います。
アドバイスありがとうございます。
書き方が悪かったかもしれませんが、コードとしてスリム化したかったので、補足説明の「'~の」では目的が違ってしまいます…スミマセン
No.1
- 回答日時:
Const a=1
Const b="string"
で、それを使う場所で a とか b を参照するようにすれば、よろしいかと。
この回答への補足
アドバイスありがとうございます!
Constを使ってスリム化しようと考えていました。
が、上手くいきません。CopydataはmyBookの中のSH1に存在するので、リンクだらけでどうConstを使って良いか困っています。
宜しければ具体的にアドバイスいただけると大変助かります。
>'他のBookを開いて変数に格納
>Set myBook = Workbooks.Open(Filename:=myDir & "\" & myName)
>'転記元を取得(Z列1行を基点に100行コピー)
>Set SH1 = myBook.Worksheets("回答内容")
>Set Copydata = SH1.Range("Z1").Resize(100, 1)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
- Visual Basic(VBA) 【前回の続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/16 16:44
- Visual Basic(VBA) VBAのユーザーフォームのテキストボックスに入力制限をしたい 6 2022/11/15 08:28
- Visual Basic(VBA) 【ご教示ください】VBAの記述方法がわかりません。 2 2022/08/12 21:28
- Visual Basic(VBA) 稀に1円合いません? Sheet1から金額と個数を貼り付ける下記コードで、金額を切り上げるコードを何 3 2022/09/05 15:11
- Visual Basic(VBA) 別シートのデータを参照して値を入れたい。 まとめデータシートのC列D列の値を商品一覧シートのコードが 7 2022/08/17 13:20
- その他(Microsoft Office) マクロVBAについて 1 2022/09/06 18:12
- Visual Basic(VBA) ExcelVBAに関する質問 3 2023/02/17 10:47
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) フォルダの場所を可変にしたいです(マクロ) 4 2023/05/11 10:00
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
マクロの「SaveAs」でエラーが...
-
VBA 別ブックからの転記の高速...
-
マクロ実行後に別シートの残像...
-
グラフマクロで系列を変数にす...
-
VBA 空白行に転記する
-
Excel2013で切り取り禁止
-
Count Ifのセルの範囲指定に変...
-
Changeイベントで複数セルへの...
-
VBAで変数の数/変数名を動的に...
-
楽天RSSからエクセルVBAを使用...
-
ExcelのVBAでやりたい操作でで...
-
VBA別シートの最終行の次行へ転...
-
VBAで質問ですが、皆さんはどの...
-
Consolidateの範囲
-
Excel VBA オートフィルターで...
-
まとめシートから集計シートへA...
-
【Excel VBA】自動メール送信の...
-
ExcelのVBマクロを、バックグラ...
-
VBA 最終行を選んだシートにコ...
-
EXCEL VBA 転記 条件分岐 新...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
マクロの「SaveAs」でエラーが...
-
マクロ実行後に別シートの残像...
-
VBAコードについて
-
VBA 空白行に転記する
-
EXCELのSheet番号って変更でき...
-
VBA 別ブックからの転記の高速...
-
【VBA】特定の条件でセルをコピー
-
VBA別シートの最終行の次行へ転...
-
100万件越えCSVから条件を満た...
-
VBAで変数の数/変数名を動的に...
-
Changeイベントで複数セルへの...
-
楽天RSSからエクセルVBAを使用...
-
Count Ifのセルの範囲指定に変...
-
Unionでの他のシートの参照につ...
-
Excel2013で切り取り禁止
-
Excel VBA オートフィルターで...
-
VBA 実行時エラー1004 rangeメ...
-
複数シートの複数列に入力され...
-
VBA Userformで一部別シートに...
-
ExcelのVBマクロを、バックグラ...
おすすめ情報