回答編集履歴
3
コード修正
test
CHANGED
|
@@ -22,10 +22,10 @@
|
|
|
22
22
|
Public location As String
|
|
23
23
|
Public value As Variant
|
|
24
24
|
|
|
25
|
-
Public Sub Initialize(
|
|
25
|
+
Public Sub Initialize(rng As Range)
|
|
26
|
-
name = rng(1)
|
|
26
|
+
name = rng.Cells(1)
|
|
27
|
-
age = rng(2)
|
|
27
|
+
age = rng.Cells(2)
|
|
28
|
-
location = rng(3)
|
|
28
|
+
location = rng.Cells(3)
|
|
29
29
|
value = rng.value
|
|
30
30
|
End Sub
|
|
31
31
|
```
|
2
コードにコメント追記
test
CHANGED
|
@@ -43,6 +43,7 @@
|
|
|
43
43
|
Set getItems = items
|
|
44
44
|
End Property
|
|
45
45
|
|
|
46
|
+
'シートからデータ読み込み
|
|
46
47
|
Public Sub DataStore(rng As Range)
|
|
47
48
|
Dim r As Range, d As C_Data
|
|
48
49
|
For Each r In rng.Rows
|
|
@@ -52,6 +53,8 @@
|
|
|
52
53
|
Next
|
|
53
54
|
End Sub
|
|
54
55
|
|
|
56
|
+
'データをシートへ出力
|
|
57
|
+
'FirstCell 出力先の先頭セル
|
|
55
58
|
Public Sub DataOutput(FirstCell As Range)
|
|
56
59
|
Dim itm As C_Data, rng As Range
|
|
57
60
|
Set rng = FirstCell.Resize(1, 3)
|
1
サンプルコード追記
test
CHANGED
|
@@ -9,6 +9,68 @@
|
|
|
9
9
|
|
|
10
10
|
---
|
|
11
11
|
|
|
12
|
-
上記の点は承知の上で、プログラミングの勉強のため、あるいは趣味で、ということでしょうか。
|
|
12
|
+
~~上記の点は承知の上で、プログラミングの勉強のため、あるいは趣味で、ということでしょうか。~~
|
|
13
13
|
|
|
14
|
+
実際に実務で有効に使っているということなので、提示のコードから気になった部分を自分なりに作り直してみました。
|
|
14
15
|
|
|
16
|
+
クラスモジュール **C_Data**
|
|
17
|
+
```vba
|
|
18
|
+
Option Explicit
|
|
19
|
+
|
|
20
|
+
Public name As String
|
|
21
|
+
Public age As Integer
|
|
22
|
+
Public location As String
|
|
23
|
+
Public value As Variant
|
|
24
|
+
|
|
25
|
+
Public Sub Initialize(ByVal rng As Range)
|
|
26
|
+
name = rng(1)
|
|
27
|
+
age = rng(2)
|
|
28
|
+
location = rng(3)
|
|
29
|
+
value = rng.value
|
|
30
|
+
End Sub
|
|
31
|
+
```
|
|
32
|
+
クラスモジュール **C_Items**
|
|
33
|
+
```vba
|
|
34
|
+
Option Explicit
|
|
35
|
+
|
|
36
|
+
Private items As Collection
|
|
37
|
+
|
|
38
|
+
Private Sub Class_Initialize()
|
|
39
|
+
Set items = New Collection
|
|
40
|
+
End Sub
|
|
41
|
+
|
|
42
|
+
Public Property Get getItems() As Collection
|
|
43
|
+
Set getItems = items
|
|
44
|
+
End Property
|
|
45
|
+
|
|
46
|
+
Public Sub DataStore(rng As Range)
|
|
47
|
+
Dim r As Range, d As C_Data
|
|
48
|
+
For Each r In rng.Rows
|
|
49
|
+
Set d = New C_Data
|
|
50
|
+
d.Initialize r
|
|
51
|
+
items.Add d
|
|
52
|
+
Next
|
|
53
|
+
End Sub
|
|
54
|
+
|
|
55
|
+
Public Sub DataOutput(FirstCell As Range)
|
|
56
|
+
Dim itm As C_Data, rng As Range
|
|
57
|
+
Set rng = FirstCell.Resize(1, 3)
|
|
58
|
+
For Each itm In items
|
|
59
|
+
rng.value = itm.value
|
|
60
|
+
Set rng = rng.Offset(1)
|
|
61
|
+
Next
|
|
62
|
+
End Sub
|
|
63
|
+
```
|
|
64
|
+
|
|
65
|
+
標準モジュール
|
|
66
|
+
```vba
|
|
67
|
+
Sub 転記マクロ()
|
|
68
|
+
With New C_Items
|
|
69
|
+
.DataStore Sheet1.Range("A2:C6")
|
|
70
|
+
.DataOutput Sheet2.Range("A2")
|
|
71
|
+
End With
|
|
72
|
+
End Sub
|
|
73
|
+
```
|
|
74
|
+
|
|
75
|
+
HiraKazu1124さんの回答の指摘は、私も同意見です。（C_Dataの設計や名前付けなど）
|
|
76
|
+
が、そこはあまり変更せずに、C_Itemsの設計に関しての一例コードです。
|