複数シートにわたる連番を自動作成するfunctio

質問

複数シートにわたる連番を自動作成するfunction関数をoffice 365のEXCELで作りました。
variant型で定義しており、連番途中に右側のセルが空白だった場合、対象セルを空白にし、上のセルが空白だった場合は、数字が出てくるセルまで上に行き＋１します。
左端のシート以外の最上のセルは左のシートの最大の数字+1に設定しています。

Function renban1() As Variant
Dim temp As Integer, a_row As Long, a_col As Long
Dim range1 As Range, ind As Integer
Const a_spc = " "

ind = ActiveSheet.Index
a_row = ActiveCell.Row
a_col = ActiveCell.Column
renban1 = a_spc

Set range1 = Range(Cells(a_row, a_col + 1), Cells(a_row, a_col + 7))

If a_col = 2 And a_row = 5 Then
renban1 = 1
Exit Function
End If

If a_row = 12 And a_col = 2 Then
If ind = 1 Then
renban1 = 2
Exit Function
Else
If WorksheetFunction.CountA(range1) = 0 Then '右が空白
renban1 = a_spc
Exit Function

Else
For temp = 80 To 12 Step -2
If ActiveSheet.Previous.Cells(temp, a_col).Value <> a_spc Then '上が空白以外≒数値
renban1 = ActiveSheet.Previous.Cells(temp, a_col).Value + 1
Exit Function
End If
Next temp
End If
End If
End If

If a_row > 10 And a_row < 82 Then '番号のセル範囲
If WorksheetFunction.CountA(range1) = 0 Then '右が空白
renban1 = a_spc
Exit Function
Else
For temp = a_row - 2 To 12 Step -2
If Cells(temp, a_col).Value <> a_spc Then '上が空白以外≒数値
renban1 = Cells(temp, a_col).Value + 1
Exit Function
End If
Next temp
End If
End If
End Function

これで、動作自体は正しいのですが、いちいちセルをクリックしてENTERを押さないと正しく更新されません。（トリガーが無いので当然と言えなくもないのですが）
１シートに３５行ほどあり、同様のシートが１１枚あるので、出来れば自動で更新させたいのですが、calculateやapplication.volatileを試してもうまくいきませんでした。

VBAにお詳しい方、どうやれば良いか教えて下さい。よろしくお願い致します。

回答No.3

HohoPapa

一般に、
Functionは与えられた引数を使って計算し
その値を返します。
なので、
Function関数だけでは機能せず、
いくつかのセルにこの関数を使った計算式を埋めているものと思います。
また、関数は１つ前のシートも参照していることから
２枚目のシート以降にそれぞれ埋まっていますね。
ここまでの私の理解は正しいですね？

提示された関数には引数がないので
計算に必要な情報（他のセル）が書き換わっても
再計算が必要とエクセルが判断しないため、
>いちいちセルをクリックしてENTERを押さないと
>正しく更新されません
という事態が起きています。

半ば強制的に再計算させるコード
Sub aaa()
　Application.CalculateFull
End Sub
を実行すれば、課題関数式の埋まったセルが再計算されます。
しかし、課題関数は
選択されているシートに依存したコードが書き込まれているため
CalculateFullを行っても期待の結果にならなと思います。

そこで、今のコードを生かすのであれば
Sub ccc()
　Dim ws As Worksheet
　Dim wkShiki As String
　Dim MyRang As Range

　For Each ws In Worksheets
　　With ws
　　　If ws.Index <> 1 Then
　　　　.Select
　　　　Set MyRang = .Cells(1, 3) '式の埋まっているセル
　　　　MyRang.Select
　　　　wkShiki = MyRang.Formula
　　　　MyRang.Formula = wkShiki
　　　End If
　　End With
　Next

End Sub
といったコードが思いつきます。
よかったら試してみてください。

以下提案です。
提示されたコードは、ぶっちゃけ突っ込みどころ満載です。
むしろ、シートイメージのサンプルと
やりたいことを詳しく再ポストし、
ゼロベースで再構築したほうがいいと思います。

お礼

ありがとうございます。
そうですね。突っ込みどころ満載ですね。

仰せに従って再質問させて頂きます。

回答No.2

unokwave

>したらいいということでしょうか？

指摘の箇所はそうですね。
ただよく見ると他にも問題がありますね。
具体的にはRangeやCellsを単独で呼び出している箇所です。
例えば単にRangeと書いてある箇所はActiveSheet.Rangeと書くのと同じです。
任意のシートにRange機能を使いたい場合はWorkSheets(1).Rangeと書くか、
WorkSheets(1).Activate
set range1 = Range
と言う具合に一旦シートをActivateで切り替える必要があります。
RangeをCellsに置き換えて全く同じ事がCellsにも当てはまります。

Activateを使わずに処理したい場合、
Dim sht As WorkSheet
set sht = WorkSheets(1)
set range1 = sht.Range("A1")
という具合にシートを変数に割り当てて呼び出すと読みやすくなります。

回答No.1

unokwave

セルをクリックしなければならないのはActiveSheetを使っているからです。
Activeという名前がついた機能は現在選択されている物に対する操作である事を指していますから。
任意のシートを選ぶにはActiveSheetの代わりにWorksheets(数字)を呼び出してください。
数字を変数にしてfor文で変化させれば望みは達成できます。

補足

ありがとうございます。
具体的には例えば

If ActiveSheet.Previous.Cells(temp, a_col).Value <> a_spc Then '上が空白以外≒数値

を書き換えればいいのでしょうか？
ここを例えば、for文でシートのINDEXを選び、

If Worksheets(i).Previous.Cells(temp, a_col).Value <> a_spc Then

に
したらいいということでしょうか？