エクセルVBAに関する二三質問

質問

いつもお世話になっております。
VBAに関する二三の質問ですが、分かる範囲でお願いします。
1.他モジュールでも使える変数ってありますか？
Publicじゃできませんでした。

2.A列のセルが全部「完了」なら、B1に「全部完了」
IF Range("A1").Value = "完了" Or _
Range("A1").Value = "完了" Or _
・・・・・・
Range("A65536").Value = "完了" Then
Range("B1").Value = "全部完了"
End if

IF Range("A1:B65536").Value = "完了" Then
Range("B1").Value = "全部完了"
End if

じゃできませんでした。
何か方法ありませんか？

3.左上のプロジェクト画面で、
sheet1(職員名簿)
sheet2(住所録)
とありますよね、sheet1と職員名簿、どっちも通用するのですが、
どっちが本当の名前ですか？
sheetを増やしたら、sheet332とどんでもないようなかけ離れたシート名になりますし、
シート現在名を変更したら、他のシートに影響を与えしまうのではと心配です。

お願いします。

回答No.4

Wendy02 · ベストアンサー

こんにちは。　#3 のWendy02です。

ちょっと、自分の発言が気になりました。

#3　の
>なお、Version によって、この挙動が多少変わるような気がします。

調べてみましたが、この発言は間違いでした。Excel　2000　以降、同じでした。

今更、なぜ、こんなことをと思う方もいるでしょうが、シートのオブジェクト名について、調べてみました。

オブジェクト名 で、ユニーク（全ブックに対して)な名前をつけることによって、独立させることは出来ますが、例えば、Sheet1 や Sheet2 のような名前の場合は、ひとつのワークブックでは、一意であっても、ワークシートの名前と混同して、ひじょうに間違えやすくなります。

オブジェクト名というのは、いわゆる本名ですね。そして、シート名は、通称名でしょうね。

例：
このような場合があります。

*　オブジェクト名とシート名が違っている場合

シート名　オブジェクト名　シートIndex
Sheet1　Sheet3　 1
Sheet2　Sheet1　 2
Sheet3　Sheet2　 3

'標準モジュール
'（これは問題あり)
Sub test_SheetsName()
'サンプルプログラム
Dim i As Integer
Debug.Print "シート名"; Spc(2); "オブジェクト名"; Spc(2); "シートIndex"
'On Error Resume Next

For i = 1 To ThisWorkbook.Sheets.Count
　MsgBox "シート名　　　:" & Sheets(i).Name & vbNewLine & _
　　　　"オブジェクト名:" & Sheets(i).CodeName & vbNewLine & _
　　　　"シートInex　　:" & Sheets(i).Index

　　　 Debug.Print Sheets(i).Name; Spc(2); Sheets(i).CodeName; Spc(2); Sheets(i).Index
Next i
End Sub

並びを替えて、シート2 を先頭に持ってくると

シート名　オブジェクト名　シートIndex
Sheet2　Sheet1　 1
Sheet1　Sheet3　 2
Sheet3　Sheet2　 3

グラフシートを加えると
シート名　オブジェクト名　シートIndex
Graph1　Graph1　 1
Sheet2　Sheet1　 2
Sheet1　Sheet3　 3
Sheet3　Sheet2　 4

こうすると、まず、Sheets インデックスは、その都度変わるから使えない、ということですね。オブジェクト名は、使えるけれども、そのままでは紛らわしいですね。それと、Graph1 というシートですと、ちょっと問題がありそうですね。

Sheet1 のシート名を、ワークシート上のシートタブから「シート1」と替えてみると、

シート名　オブジェクト名　シートIndex
Graph1　Graph1　 1
Sheet2　Sheet1　 2
シート1　Sheet3　 3
Sheet3　Sheet2　 4

となりますから、どのシートかは、ワークシート上から分かりますね。

ところで、Sheets(i) という方法で、ループするのは問題がないでしょうか？

例えば、今時は、そのようなものは使わないけれども、Dialogシートや、Macroシートがあると、エラーが発生します。

そこで、以下のように訂正しました。

Sub test_SheetsName2()
Dim i As Integer

Debug.Print "シート名"; Spc(2); "オブジェクト名"; Spc(2); "シートIndex"
'On Error Resume Next
For i = 1 To Worksheets.Count
　MsgBox "シート名　　　:" & Worksheets(i).Name & vbNewLine & _
　　　　"オブジェクト名:" & Worksheets(i).CodeName & vbNewLine & _
　　　　"シートInex　　:" & Worksheets(i).Index
　　　 Debug.Print Worksheets(i).Name; Spc(2); Worksheets(i).CodeName; Spc(2); Worksheets(i).Index
Next i
End Sub

つまり、ワークシートを指定する場合は、Worksheets コレクションでないと、失敗する可能性がある、ということです。

回答No.3

Wendy02

こんばんは。
どうやら、私の発言もアリのようですから、書かせていただきます。

>1.他モジュールでも使える変数ってありますか？
>Publicじゃできませんでした。

[標準モジュール]（のみ)に、モジュールレベルの変数を書けば、（つまり、Option Explicit の次の行あたりから、Public MyVal As String と書けば、Public 変数になっています。

なお、Version によって、この挙動が多少変わるような気がします。

また、今のコードの状況からして、Public 変数を使うような場面には、あまり出会わないはずです。内容を詳しく聞かないと分かりません。

2.A列のセルが全部「完了」なら、B1に「全部完了」

>IF Range("A1").Value = "完了" Or _
>Range("A1").Value = "完了" Or _
>・・・・・・
>Range("A65536").Value = "完了" Then
>Range("B1").Value = "全部完了"
>End if

論理的にはありえますが、実用的でないコードは書かないほうがよいです。
ハングの原因になります。

'基本的には、標準モジュールです。

2番が一般的です。

Sub Test1()
　'ワークシート関数を使う
　If WorksheetFunction.CountIf(Range("A1", Range("A65536").End(xlUp)), "完了") = _
　　Range("A1", Range("A65536").End(xlUp)).Count Then
　　Range("B1").Value = "全部完了"
　End If
End Sub

Sub Test2()
'オーソドックスVBAスタイル
Dim c As Range
　For Each c In Range("A1", Range("A65536").End(xlUp))
　　If StrComp(c.Value, "完了") <> 0 Then
　　　Exit Sub
　　End If
　Next c
　Range("B1").Value = "全部完了"
End Sub

Sub Test3()
'Excel VBAスタイル
　Dim r As Range
　On Error Resume Next
　Set r = Range("A1", Range("A65536").End(xlUp)).ColumnDifferences(Range("A1"))
　On Error GoTo 0
　If r Is Nothing Then
　　Range("B1").Value = "全部完了"
　End If
End Sub

>3.左上のプロジェクト画面で、
>sheet1(職員名簿)
>sheet2(住所録)
>とありますよね、sheet1と職員名簿、どっちも通用するのですが、
>どっちが本当の名前ですか？

Sheet1 というオブジェクト名によって、VBAの処理は、基本的にはしません。それは、Worksheet とは限らないからです。また、VB Editor 上で、並べ替えられてしまいます。

Worksheets("職員名簿") が、一応、一意の名前といえるかと思います。一般的には、それをオブジェクトとして扱いをします。

回答No.2

papayuka

1.Publicで出来ると思いますが、どんなコードを書かれているのでしょう。

2.A列全部に同じ文言が入っているなら、、、、

Sub Test()
　With ActiveSheet
　　If WorksheetFunction.CountIf(.Range("A:A"), "完了") = 65536 Then
　　　 .Range("B1").Value = "全部完了"
　　Else
　　　 .Range("B1").Value = ""
　　End If
　End With
End Sub

3.sheet1(職員名簿)・・Sheet1 が オブジェクト名（CodeName）で、職員名簿がシート名（Name）です。
オブジェクト名はVBE画面のプロパティウィンドウでなら変更出来ます。
シート名はExcel上で容易に変更出来るので、運用でカバー出来ないならオブジェクト名を使用する方が良いかも知れません。

Sub Test2()
　　MsgBox Worksheets("職員名簿").Name
　　MsgBox Worksheets("職員名簿").CodeName
　　MsgBox Worksheets("職員名簿").Range("A1").Value
　　MsgBox Sheet1.Range("A1").Value
End Sub

回答No.1

Hayashi_Trek

１．Publicで十分。

　　［モジュール１］
　　Public a As Integer
　　Private Sub hogehoge
　　　　a = 2
　　End Sub

　　［モジュール２］
　　Private Sub piyo
　　　　MsgBox "a=" & cstr(a)
　　End Sub

２．Dim flag As Boolearn
　　Dim row As Long
　　flag = True
　　For row = 1 to 65535
　　　　If Cells(row, 1).Value <> "完了" Then flag = False
　　Next row
　　If flag Then
　　　　Cells(1,2).Value = "全部完了"
　　Else
　　　　Cells(1,2).Value = ""
　　End If

３．シートを名前登録しているのかな？
　　シート名が「sheet1(職員名簿)」じゃないよね。
　　良くわからん。