ChatGPTとの格闘～ExcelVBAで詳細設計書の段落番号を自動採番する方法を教えていただいた
1.ゴール
Wordの段落番号の自動採番を模した機能をExcelVBAを使用してExcelに実装する。
(1)実行前
(2)実行後
2.質問を工夫する
ロジックのイメージが掴めない。
三日間に渡るChatGPTとの格闘の結果、期待どおりの結果を得ることができ、ノンプログラミングでプログラムすることができた。
質問の方法について参考にしたのは以下の動画。
GPTは各単語から次の言葉にふさわしい単語を確率で導いている。
よって、広範囲のキーワード検索(ググる)ではなく、範囲を狭めて削ることで、続く単語を限定することが大切である。
つまり、GPTにプログラミングしてもらうためにはまず、「あなたはプロのプログラマーです」と伝える必要がある。
その際、関数概要、引数(入力)、戻り値(出力)、制約条件が範囲の限定に繋がる。
まずは日本語で質問を書き出す。
続いて、DeepLで英訳する。
#指南書
あなたはプロのVBAマクロプログラマーです。
次の制約条件と入力、出力に基づいて、VBAマクロで処理を実装しなさい。
#findings
You are a professional VBA macro programmer.
Based on the following constraints, inputs and outputs, implement the process with VBA macros.
#制約条件
セル範囲A1～N500に詳細設計を記述してください。
A列は基本的に各処理のタイトルを記述する。
A列のタイトルを記述した後、具体的な処理を記述する。この記述の階層は、C列、D列、E列、F列、G列、H列、I列、J列、K列、L列、M列、N列、O列、P列、Q列と深くなり、階層が深くなればなるほど、より多くのセルを使用することになる。
階層が深くなればなるほど、セルを1行下に、1列右に移動して処理を記述することになる。
階層が深くなるケースは、分岐処理や反復処理が発生する場合である。
階層が深くならないケースもある。
階層が深くならない場合は、現在の階層に応じた連番が付与される。
#Constraints
Describe the detailed design in the cell range A1-N500.
Column A basically describes the title of each process.
After describing the title in column A, describe the specific process. The hierarchy of this description becomes deeper with columns C, D, E, F, G, H, I, J, K, L, M, N, O, P, and Q. The deeper the hierarchy, the more cells are used.
The deeper the hierarchy, the more cells are used. The deeper the hierarchy, the more cells are moved down one line and to the right one column to describe the process.
The case where the hierarchy becomes deeper is when branch processing or iterative processing occurs.
There are cases where the hierarchy is not deep. There are cases where the hierarchy is not deep.
If the hierarchy is not deep, a sequential number is assigned according to the current hierarchy.
#入力
セル内に書かれた文章は、A1～G500まで。
#Input
Sentences written in the cell range from A1 to G500.
#入力例
#Example of input
Title1
|--A is～
|--B is～
| |--IF ～
| | |--FOR LOOP
| | | |--IF ～
| | | | |--Z is～
| | | | |--Y is～
| | | |--LOOP END
| | |--ELSE IF～
| | | |--IF ～
| | | | |--C is～
| | | |--ELSE～
| | | | |--IF ～
| | | | | |--IF ～
| | | | | | |--IF ～
#出力
タイトルと階層に応じた連番を振ってください。
例えば、A1にタイトルとなる値がある場合、その値の先頭に1.を付与します。B2に値がある場合は、その値の先頭に1.1.を付与します。順次処理が続く場合は、B3に処理を記述し、B3に値がある場合は、その値の先頭に1.2.を付与します。順次処理がどこまで続くかは分かりません。C4に値があれば、1.2.1を付与します。この考え方は再帰的に繰り返されます。
タイトルが3つあれば、連番の最初の数字がカウントアップされます。つまり、タイトルが3つあれば、1.、2.、3.から始まります。階層が深い場合は、そのタイトルが付与されている連番が一番上にきます。値がない場合は、連番をつける必要はありません。
最大15列まで対応できるようにしてください。
#Output
Please assign sequential numbers according to the title and hierarchy.
For example, if there is a value in A1 that serves as the title, add 1. to the beginning of that value. If there is a value in B2, add 1.1. to the beginning of the value. If sequential processing continues, the processing is described in B3, and if there is a value in B3, 1.2. is added to the beginning of the value. The sequential process does not know how far the sequential process will go. If there is a value in C4, it is assigned 1.2.1. This concept is repeated recursively.
If there are three titles, the first number of the sequential number is counted up. In other words, if there are three titles, they start with 1., 2., and 3. If the hierarchy is deeper, the sequential number of the title to which it is attached will be given at the top.
If there is no value, there is no need to assign a sequential number.
Please allow for a maximum of 15 rows.
#出力例
#Example of output
- Title1
|--1.1. A is～
|--1.2. B is～
| |--1.2.1. IF ～
| | |--1.2.1.1. FOR LOOP
| | | |--1.2.1.1.1. IF ～
| | | | |--1.2.1.1.1.1. Z is～
| | | | |--1.2.1.1.1.2. Y is～
| | | |--1.2.1.1.2. LOOP END
| | |--1.2.1.2. ELSE IF～
| | | |--1.2.1.2.1. IF ～
| | | | |--1.2.1.2.1.1. C is～
| | | |--1.2.1.2.2. ELSE～
| | | | |--1.2.1.2.2.1. IF ～
| | | | | |--1.2.1.2.2.1.1. IF ～
| | | | | | |--1.2.1.2.2.1.1.1. IF ～
3.GPTが回答したソースコード
Sub addSequentialNumbers()
Dim i As Integer, j As Integer, k As Integer, l As Integer, m As Integer
Dim n As Integer, o As Integer, p As Integer, q As Integer, r As Integer
Dim s As Integer, t As Integer, u As Integer, v As Integer, w As Integer
i = 1
j = 1
k = 1
l = 1
m = 1
n = 1
o = 1
p = 1
q = 1
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
' Loop through each cell in the range
For Each c In Range("A1:O500")
If c.Value <> "" Then
' If the cell contains a value, add sequential numbers based on hierarchy
Select Case c.Column
Case 1
' If the value is in column A, increment i and set j, k, l, m, n, o, p, q, r, s, t, u, v, and w to 1
c.Value = i & ". " & c.Value
j = 1
k = 1
l = 1
m = 1
n = 1
o = 1
p = 1
q = 1
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
i = i + 1
Case 2
' If the value is in column B, increment j and set k, l, m, n, o, p, q, r, s, t, u, v, and w to 1
c.Value = i - 1 & "." & j & ". " & c.Value
k = 1
l = 1
m = 1
n = 1
o = 1
p = 1
q = 1
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
j = j + 1
Case 3
' If the value is in column C, increment k and set l, m, n, o, p, q, r, s, t, u, v, and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k & ". " & c.Value
l = 1
m = 1
n = 1
o = 1
p = 1
q = 1
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
k = k + 1
Case 4
' If the value is in column D, increment l and set m, n, o, p, q, r, s, t, u, v, and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l & ". " & c.Value
m = 1
n = 1
o = 1
p = 1
q = 1
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
l = l + 1
Case 5
' If the value is in column E, increment m and set n, o, p, q, r, s, t, u, v, and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m & ". " & c.Value
n = 1
o = 1
p = 1
q = 1
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
m = m + 1
Case 6
' If the value is in column F, increment n and set o, p, q, r, s, t, u, v, and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m - 1 & "." & n & ". " & c.Value
o = 1
p = 1
q = 1
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
n = n + 1
Case 7
' If the value is in column G, increment o and set p, q, r, s, t, u, v, and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m - 1 & "." & n - 1 & "." & o & ". " & c.Value
p = 1
q = 1
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
o = o + 1
Case 8
' If the value is in column H, increment p and set q, r, s, t, u, v, and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m - 1 & "." & n - 1 & "." & o - 1 & "." & p & ". " & c.Value
q = 1
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
p = p + 1
Case 9
' If the value is in column I, increment q and set r, s, t, u, v, and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m - 1 & "." & n - 1 & "." & o - 1 & "." & p & "." & q & ". " & c.Value
r = 1
s = 1
t = 1
u = 1
v = 1
w = 1
q = q + 1
Case 10
' If the value is in column J, increment r and set s, t, u, v, and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m - 1 & "." & n - 1 & "." & o - 1 & "." & p - 1 & "." & q - 1 & "." & r & ". " & c.Value
s = 1
t = 1
u = 1
v = 1
w = 1
r = r + 1
Case 11
' If the value is in column K, increment s and set t, u, v, and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m - 1 & "." & n - 1 & "." & o - 1 & "." & p - 1 & "." & q - 1 & "." & r - 1 & "." & s & ". " & c.Value
t = 1
u = 1
v = 1
w = 1
s = s + 1
Case 12
' If the value is in column L, increment t and set u, v, and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m - 1 & "." & n - 1 & "." & o - 1 & "." & p - 1 & "." & q - 1 & "." & r - 1 & "." & s - 1 & "." & t & ". " & c.Value
u = 1
v = 1
w = 1
t = t + 1
Case 13
' If the value is in column M, increment u and set v and w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m - 1 & "." & n - 1 & "." & o - 1 & "." & p - 1 & "." & q - 1 & "." & r - 1 & "." & s - 1 & "." & t - 1 & "." & u & ". " & c.Value
v = 1
w = 1
u = u + 1
Case 14
' If the value is in column N, increment v and set w to 1
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m - 1 & "." & n - 1 & "." & o - 1 & "." & p - 1 & "." & q - 1 & "." & r - 1 & "." & s - 1 & "." & t - 1 & "." & u - 1 & "." & v & ". " & c.Value
w = 1
v = v + 1
Case 15
' If the value is in column O, increment w
c.Value = i - 1 & "." & j - 1 & "." & k - 1 & "." & l - 1 & "." & m - 1 & "." & n - 1 & "." & o - 1 & "." & p - 1 & "." & q - 1 & "." & r - 1 & "." & s - 1 & "." & t - 1 & "." & u - 1 & "." & v - 1 & "." & w & ". " & c.Value
w = w + 1
End Select
End If
Next c
End Sub
Discussion