1,workbookとworksheetを格納した変数で書くことはできないのでしょうか。 今回のマクロは別のExcelブック間を行き来しないので良いのですが発生する場合、変数で記載出来ればいいなと思って書いたらエラーとなってしまいました。 workbookやworksheetの書き方の規則はどんなものがあるのでしょうか。 Dim shF As Worksheet Dim boF As Workbook Set boF = Workbooks("s09_homework.xls") Set shF = Worksheets("main")
Option Explicit
Private shF As Worksheet
Private shT As Worksheet
Private lnfz As Long
Private r As String
Public Sub kaishi()
Set shF = Worksheets("main")
lnfz = shF.Range("B" & Rows.Count).End(xlUp).Row
sakuzyo
Aretu
r = "B"
narabikae
sakusei
r = "A"
narabikae
shF.Range("A1:A" & lnfz).ClearContents
’なぜ一度シートをactiveteしなければshF.Range("A1").Selectが動かないのか
shF.Activate
shF.Range("A1").Select
End Sub
Private Sub Aretu()
Dim boF As Workbook
Set boF = Workbooks("s09_homework.xls")
' Set shF = Worksheets("main")
' lnFz = shF.Range("B" & Rows.Count).End(xlUp).Row
' 動く
' shF.Range("A1").Value = "No."
' Workbooks("s09_homework.xls").Worksheets("main").Range("A1").Value = "No."
' boF.Worksheets("main").Range("A1").Value = "No."
' 動かない
' boF.shF.Range("A1").Value = "No."
' Workbooks("s09_homework.xls").shF.Range("A1").Value = "No."
shF.Range("A1").Value = "No."
shF.Range("A2").Value = 1
shF.Range("A2").AutoFill Destination:=Range("A2:A" & lnfz), Type:=xlFillSeries
End Sub
Private Sub narabikae()
' Set shF = Worksheets("main")
' lnFz = shF.Range(r & Rows.Count).End(xlUp).Row
With shF.Sort
.SortFields.Clear
.SortFields.Add2 Key:=Range(r & "2:" & r & lnfz), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange Range("A1:G" & lnfz)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub sakusei()
Dim gyo As Long
Dim saki As Long
Dim d As Long
Dim Kaisya As String
' Set shF = Worksheets("main")
' lnFz = shF.Range("B" & Rows.Count).End(xlUp).Row
For gyo = 2 To lnfz
If shF.Range("B" & gyo).Value <> Kaisya Then
If gyo <> 2 Then
koushisen
tuika1
End If
Kaisya = shF.Range("B" & gyo).Value
Sheets("main1").Copy After:=Sheets(Worksheets.Count)
Set shT = Sheets("main1 (2)")
shT.Name = Kaisya
saki = 16
End If
shT.Range("E" & saki).Value = shF.Range("D" & gyo).Value
shT.Range("F" & saki).Value = shF.Range("E" & gyo).Value
shT.Range("H" & saki).Value = shF.Range("F" & gyo).Value
If shF.Range("G" & gyo).Value > 1 Then
shT.Range("I" & saki).Value = shF.Range("G" & gyo).Value
Else
shT.Range("J" & saki).Value = shF.Range("G" & gyo).Value
End If
If saki = 16 Then
shT.Range("K" & saki).Value = shF.Range("G" & gyo).Value
Else
shT.Range("K" & saki).Value = shF.Range("G" & gyo).Value + shF.Range("G" & gyo).Offset(-1).Value
End If
d = shF.Range("C" & gyo).Value
shT.Range("B" & saki).Value = Format(d, "yy")
shT.Range("C" & saki).Value = Format(d, "mm")
shT.Range("D" & saki).Value = Format(d, "dd")
saki = saki + 1
Next
koushisen
tuika1
End Sub
Public Sub sakuzyo()
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In Worksheets
If Left(sh.Name, 4) <> "main" Then
sh.Delete
End If
Next
Application.DisplayAlerts = True
End Sub
Private Sub koushisen()
Dim lnTz As Long
lnTz = shT.Range("B" & Rows.Count).End(xlUp).Row
With shT.Range("B16:K" & lnTz + 1)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End With
shT.Range("B16").Select
End Sub
Private Sub tuika1()
Dim lnTz As Long
lnTz = shT.Range("B" & Rows.Count).End(xlUp).Row
Application.PrintCommunication = False
With shT.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
shT.PageSetup.PrintArea = "$B$1:$K$" & lnTz + 1
Application.PrintCommunication = False
With shT.PageSetup
.LeftHeader = "&A"
.CenterFooter = "&D"
End With
Application.PrintCommunication = True
End Sub
2020/05/20 05:28
小川慶一さんのコメント
(コメントID: 6601)
先に、以下について述べた回答だけお送りします。
> 1,workbookとworksheetを格納した変数で書くことはできないのでしょうか。
Sub Worksheet_Ref_Sample()
'Book1.xlsm には以下の3枚のシートがあるとします
' SheetA
' SheetB
' SheetC
'
'Book2.xlsm には以下の3枚のシートがあるとします
' SheetD
' SheetE
' SheetF
''' 変数宣言と参照の設定 はじめ '''
Dim wb1 As Workbook
Dim wsB As Worksheet
Dim wb2 As Workbook
Dim wsF As Worksheet
Set wb1 = Workbooks("Book1.xlsm")
Set wsB = wb1.Worksheets("SheetB")
Set wb2 = Workbooks("Book2.xlsm")
Set wsF = wb2.Worksheets("SheetF") '(*1)
''' 変数宣言と参照の設定 おわり '''
''' 下準備として Book1.xlsm のほうをアクティブにします
wb1.Activate
''' 下準備おわり。では、wb2内のシートwsFをどのように操作するか?
''' 結論から書くと、以下のような書き方で十分です。(*2)
wsF.Range("A1").Value = 1
wsF.Tab.Color = vbMagenta
'逆に言うと、以下は間違い(コメントをはずして実行して、エラーになることを確かめてください)
'wb2.wsF.Range("A2").Value = 2
'wb2.wsF.Tab.Color = vbBlack
'何故か?
'「何故か?」というか、(*2)で十分だからです。
'(*1)の段階で、オブジェクトに参照設定(ニックネームをつける)をしています。
'以下、直感的な説明をします。
'小学校5年生のクラスが5クラスあるとして、そのうちの「3組」に、「田中健三くん」という生徒がいたとします。
'田中健三くんに「たなけん」というニックネームが割り当てられたとします。
'さて、そのとき、同学年の友人が「田中健三くん」を指名するとき、
'「3組のたなけん」とは呼びかけません。(ちょっと違和感ありますね)
'単に「たなけん」で、「3組」の「田中健三くん」を示すのに十分です。
'それと同様です。
'
'※学年に「たなけん」というニックネームの生徒はひとりしかいないという前提です
' VBでも、同じ名前の変数を同じプロシージャ内に2つ作れせまんね。
End Sub
Option Explicit
Private shF As Worksheet
Private shT As Worksheet
Private lnfz As Long
Private r As String
'開始ボタン、終了ボタンつけましょう ogawa
Public Sub kaishi()
Set shF = Worksheets("main")
lnfz = shF.Range("B" & Rows.Count).End(xlUp).Row
sakuzyo
Aretu
r = "B"
narabikae
sakusei
r = "A"
narabikae
shF.Range("A1:A" & lnfz).ClearContents
'なぜ一度シートをactiveteしなければshF.Range("A1").Selectが動かないのか
shF.Activate
shF.Range("A1").Select
End Sub
Private Sub Aretu()
Dim boF As Workbook
Set boF = Workbooks("s09_homework.xls")
' Set shF = Worksheets("main")
' lnFz = shF.Range("B" & Rows.Count).End(xlUp).Row
' 動く
' shF.Range("A1").Value = "No."
' Workbooks("s09_homework.xls").Worksheets("main").Range("A1").Value = "No."
' boF.Worksheets("main").Range("A1").Value = "No."
' 動かない
' boF.shF.Range("A1").Value = "No."
' Workbooks("s09_homework.xls").shF.Range("A1").Value = "No."
shF.Range("A1").Value = "No."
shF.Range("A2").Value = 1
shF.Range("A2").AutoFill Destination:=Range("A2:A" & lnfz), Type:=xlFillSeries
End Sub
Private Sub narabikae()
' Set shF = Worksheets("main")
' lnFz = shF.Range(r & Rows.Count).End(xlUp).Row
With shF.Sort
.SortFields.Clear
.SortFields.Add2 Key:=Range(r & "2:" & r & lnfz), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, _
DataOption:=xlSortNormal
.SetRange Range("A1:G" & lnfz)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub sakusei()
Dim gyo As Long
Dim saki As Long
Dim d As Long
Dim Kaisya As String
' Set shF = Worksheets("main")
' lnFz = shF.Range("B" & Rows.Count).End(xlUp).Row
For gyo = 2 To lnfz
If shF.Range("B" & gyo).Value <> Kaisya Then
If gyo <> 2 Then
koushisen
tuika1
End If
Kaisya = shF.Range("B" & gyo).Value
Sheets("main1").Copy After:=Sheets(Worksheets.Count)
Set shT = Sheets("main1 (2)")
shT.Name = Kaisya
saki = 16
End If
shT.Range("E" & saki).Value = shF.Range("D" & gyo).Value
shT.Range("F" & saki).Value = shF.Range("E" & gyo).Value
shT.Range("H" & saki).Value = shF.Range("F" & gyo).Value
If shF.Range("G" & gyo).Value > 1 Then
shT.Range("I" & saki).Value = shF.Range("G" & gyo).Value
Else
shT.Range("J" & saki).Value = shF.Range("G" & gyo).Value
End If
If saki = 16 Then
shT.Range("K" & saki).Value = shF.Range("G" & gyo).Value
Else
shT.Range("K" & saki).Value = shF.Range("G" & gyo).Value + shF.Range("G" & gyo).Offset(-1).Value
End If
d = shF.Range("C" & gyo).Value
shT.Range("B" & saki).Value = Format(d, "yy")
shT.Range("C" & saki).Value = Format(d, "mm")
shT.Range("D" & saki).Value = Format(d, "dd")
saki = saki + 1
Next
koushisen
tuika1
End Sub
Public Sub sakuzyo()
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In Worksheets
If Left(sh.Name, 4) <> "main" Then
sh.Delete
End If
Next
Application.DisplayAlerts = True
End Sub
Private Sub koushisen()
Dim lnTz As Long
lnTz = shT.Range("B" & Rows.Count).End(xlUp).Row
With shT.Range("B16:K" & lnTz + 1)
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End With
shT.Range("B16").Select
End Sub
'ベストの解答は、「新規追加する都度シートの書式を設定する」ではなく、「シート『main1』の書式を変更する」でした (^^; ogawa
Private Sub tuika1()
Dim lnTz As Long
lnTz = shT.Range("B" & Rows.Count).End(xlUp).Row
Application.PrintCommunication = False
With shT.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
shT.PageSetup.PrintArea = "$B$1:$K$" & lnTz + 1
Application.PrintCommunication = False
With shT.PageSetup
.LeftHeader = "&A"
.CenterFooter = "&D"
End With
Application.PrintCommunication = True
End Sub
Sub tameshi1()
Dim wb1 As Workbook
Dim wsA1 As Worksheet
Dim wb2 As Workbook
Dim wsA2 As Worksheet
Set wb1 = Workbooks("Book1.xlsm")
Set wsA1 = Worksheets("sheetA")'参照設定記載が同じくなり、混乱!
Set wb2 = Workbooks("Book2.xlsm")
Set wsA2 = Worksheets("sheetA")'参照設定記載が同じくなり、混乱!
End Sub
Sub tameshi2()
Dim wb1 As Workbook
Dim wsA1 As Worksheet
Dim wb2 As Workbook
Dim wsA2 As Worksheet
Set wb1 = Workbooks("Book1.xlsm")
Set wsA1 = Workbooks("Book1.xlsm").Worksheets("sheetA")
Set wb2 = Workbooks("Book2.xlsm")
Set wsA2 = Workbooks("Book2.xlsm").Worksheets("sheetA")
wb1.Activate
wsA2.Range("A1").Value = 1
wsA2.Tab.Color = vbMagenta
End Sub
受講生さんの投稿
(投稿ID: 4730)
添削を宜しくお願いいたします。
ここまで取り組んだことで、仕事でマクロを使ったツール作成ができるようになっていて、大変うれしいです。
作成していて2点疑問があり、以下ご教示頂きたいです。
何卒宜しくお願いいたします。
1,workbookとworksheetを格納した変数で書くことはできないのでしょうか。
今回のマクロは別のExcelブック間を行き来しないので良いのですが発生する場合、変数で記載出来ればいいなと思って書いたらエラーとなってしまいました。
workbookやworksheetの書き方の規則はどんなものがあるのでしょうか。
Dim shF As Worksheet
Dim boF As Workbook
Set boF = Workbooks("s09_homework.xls")
Set shF = Worksheets("main")
動いた
shF.Range("A1").Value = "No."
Workbooks("s09_homework.xls").Worksheets("main").Range("A1").Value = "No."
boF.Worksheets("main").Range("A1").Value = "No."
動かない
boF.shF.Range("A1").Value = "No." ←このように記載できればと思っていました。
Workbooks("s09_homework.xls").shF.Range("A1").Value = "No."
2,kaishiのSubプロシージャですが、最後mainシートのA1を選択された状態で終了したいとしたとき、
shF.Range("A1").Select
とだけ書くとエラーとなり、
shF.Activate
といれるとちゃんと動くようになりました。
こちらはselectしたいのであれば一度そのシートをActivateの状態にしないとだめというルールなのですか?
何卒宜しくお願い致します。
小川慶一さんのコメント
(コメントID: 6601)
> 1,workbookとworksheetを格納した変数で書くことはできないのでしょうか。
小川慶一さんのコメント
(コメントID: 6602)
> 2,kaishiのSubプロシージャですが、最後mainシートのA1を選択された状態で終了したいとしたとき、
> shF.Range("A1").Select
> とだけ書くとエラーとなり、
> shF.Activate
> といれるとちゃんと動くようになりました。
> こちらはselectしたいのであれば一度そのシートをActivateの状態にしないとだめというルールなのですか?
まさに、そうです。
ワークシートでも同様で、アクティブでないブック内のシートをアクティブにするには、そのシートが所属するブックをアクティブになっている必要があります。
...というか、ありました。
かつてのバージョンのエクセルでは。最近はそうでもないかもしれません。
実は、僕の環境で以下をテストしたら、なぜか動きました。本当は(*3)でエラーが出て動作が中断するはずだったのですが。
小川慶一さんのコメント
(コメントID: 6603)
添削返送します。直すようなところは特にありませんが、一読しておいてください。
あと、さきほどの book1.xlsm, book2.xlsm をシェアします。 .zip ファイルにまとめていますが、解凍してから使ってみてください。
https://www.dropbox.com/sh/xy32x73u92bu3z1/AACJCQWPGLmBtpRvBVTsZqYKa?dl=0
受講生さんのコメント
(コメントID: 6607)
お世話になっております。
ご返信頂きありがとうございます!
> 1,workbookとworksheetを格納した変数で書くことはできないのでしょうか。
について、すっきりしました!
直感的な説明を頂けて、
同姓同名が3組と5組にいて、違うニックネームだった場合はどうなるの?と思い、以下記載したところ、そういうことだったのか!とすごく納得できました。
(wsA1もwsA2も参照設定が同じ内容でこびとちゃんが混乱する!と書いていて気づきました。直感的な説明により思考しやすくなり、理解が深まりました!)
こびとちゃんが混乱しないために同姓同名がいた場合どうすれば良いか考えたとき、
以下の通り作成したら、問題無く動きました。そして、そもそも参照設定で詳細に”何組の”を設定しているので、書く必要が無いなと納得しました。
> 2,kaishiのSubプロシージャですが、最後mainシートのA1を選択された状態で終了したいとしたとき、
>> shF.Range("A1").Select
>> とだけ書くとエラーとなり、
>> shF.Activate
>> といれるとちゃんと動くようになりました。
>> こちらはselectしたいのであれば一度そのシートをActivateの状態にしないとだめというルールなのですか?
>
>まさに、そうです。
>
>
>ワークシートでも同様で、アクティブでないブック内のシートをアクティブにするには、そのシートが所属するブックをアクティブになっている必要があります。
>...というか、ありました。
>かつてのバージョンのエクセルでは。最近はそうでもないかもしれません。
>実は、僕の環境で以下をテストしたら、なぜか動きました。本当は(*3)でエラーが出て動作が中断するはずだったのですが。
私の環境でも動きました!一応そういう規則となっていること承知いたしました!ありがとうございます。
添削もありがとうございます。
ボタンをつけるのを忘れておりました。コードを見るだけでボタンがついていないとわかるのですね!?
何か記載間違っているのかな?と思い、ボタン作成しましたら実行されました。
「新規追加する都度シートの書式を設定」は実行速度が重くなりますね!ベストはシート『main1』の書式を変更するとのこと、ありがとうございます。
小川慶一さんのコメント
(コメントID: 6606)
おはようございます。
> 同姓同名が3組と5組にいて、違うニックネームだった場合はどうなるの?と思い、以下記載したところ、そういうことだったのか!とすごく納得できました。
> (wsA1もwsA2も参照設定が同じ内容でこびとちゃんが混乱する!と書いていて気づきました。直感的な説明により思考しやすくなり、理解が深まりました!)
> こびとちゃんが混乱しないために同姓同名がいた場合どうすれば良いか考えたとき、
> 以下の通り作成したら、問題無く動きました。そして、そもそも参照設定で詳細に”何組の”を設定しているので、書く必要が無いなと納得しました。
Sub tameshi2、拝見しました。
ご理解のとおりで良いかと思います。
> >実は、僕の環境で以下をテストしたら、なぜか動きました。本当は(*3)でエラーが出て動作が中断するはずだったのですが。
> 私の環境でも動きました!一応そういう規則となっていること承知いたしました!ありがとうございます。
規則(仕様)が変わったのかもしれません。
この件は、以前はよくあったハマりごとでした。
僕もこの点注意深くプログラムを書いていた時期があります。だいぶ前に納品して今でもときどきメンテナンスの依頼の来るシステムがあるのですが、そこではこの問題を回避すべくかなり注意深くコーディングをしています。
> 「新規追加する都度シートの書式を設定」は実行速度が重くなりますね!ベストはシート『main1』の書式を変更するとのこと、ありがとうございます。
「テンプレートを作り込むことで、コーディング量が減り、処理に要する時間も短くなる」というのは、実務でもよくある改善例です。
ひきつづき、お楽しみください☆