[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ユーザーフォームで取得した変数を標準モジュールで使用したい』（メロウ）
VBA初心者です。参考書など見ながら、ネットでのコードを触って操作しているレベルです。
ユーザーフォームにて、シート内に入力されたマスタ部分から値取得後、標準モジュールにてその値を使用してセルに式を代入したいです。
ユーザーフォームでは正しく値を取得できたのですが、標準モジュールで変数(kngt と plg）を呼び出すと0になっています。
コード載せますので、どう修正・追加したらよいかご教授頂きたいです。
また、お時間をさいて頂けるのであれば、こうしたら見やすい！なども教えていただけたら嬉しいです。
よろしくお願いします。
Option Explicit
Public kngt As Long, plg As Long
Dim OpenF As Variant, writeSheet As Worksheet, readBook As Workbook, readSheet As Worksheet
Sub 基データ参照()
UserForm1.Show
OpenF = Application.GetOpenFilename("Microsoft Excelブック,*.xls")
If VarType(OpenF) = vbBoolean Then
MsgBox "キャンセルされました"
Exit Sub
End If
Set writeSheet = ThisWorkbook.Worksheets("データ") ' Sheet データ を参照
Set readBook = Workbooks.Open(OpenF) ' 相手ブックを開いて参照
Set readSheet = readBook.Worksheets("sheet1") ' 相手シートを参照
readSheet.Range("B:B").Select
Application.CutCopyMode = False
Selection.Copy
writeSheet.Activate
Range("B:B").Select
ActiveSheet.Paste
Excel.Application.CutCopyMode = False
readSheet.Activate
readSheet.Range("C:C").Select
Application.CutCopyMode = False
Selection.Copy
writeSheet.Activate
Range("D:D").Select
ActiveSheet.Paste
readBook.Activate
writeSheet.Range("J4") = ActiveWorkbook.Name
Excel.Application.CutCopyMode = False
readBook.Close False ' 相手ブックを閉じる
Set readSheet = Nothing
Set readBook = Nothing
writeSheet.Range("B1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1") = "金型"
writeSheet.Range("D1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("D1") = "プラグ"
Dim lastRow As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row
writeSheet.Activate
Range("F:F").ClearContents
Range("F1") = "金型補正値"
Range("F2") = "=" & kngt & "-B2"
Range("F2").AutoFill Destination:=Range("F2:F" & lastRow), Type:=xlFillSeries
lastRow = Range("D" & Rows.Count).End(xlUp).Row
Range("H:H").ClearContents
Range("H1") = "プラグ補正値"
If Range("D2") <> "" Then
Range("H2") = "=" & plg & "-D2"
Range("H2").AutoFill Destination:=Range("H2:H" & lastRow), Type:=xlFillSeries
End If
Dim WS2 As Worksheet
Set WS2 = Worksheets("データ")
With WS2
.Range("A50:A65536").ClearContents
End With
lastRow = Range("B" & Rows.Count).End(xlUp).Row 'B列最終行(B列のデータによって変わる)
Range("A2") = "00:00.01"
Range("A3") = "00:00.02"
Range("A2:A3").AutoFill Destination:=Range("A2:A" & lastRow), Type:=xlFillSeries 'A2からB列最終行のA列までオートフィル
End Sub
Public Sub CommandButton1_Click()
If ComboBox1.ListIndex = -1 Then
MsgBox "行が選択されていません。"
Else
kngt = ComboBox1.ListIndex = 1
plg = ComboBox1.ListIndex = 2
End If
Unload Me
End Sub
Private Sub UserForm_Initialize()
With ComboBox1
.ColumnCount = 3
.RowSource = "補正値!A3:C" & Worksheets("補正値").Range("A" & Rows.Count).End(xlUp).Row
End With
End Sub
シート”補正値”は
A B C 2 機械名 kngt plg 3 1号機 130 150 4 2号機 150 160 5 3号機 180 100 ・ ・ ・ とあり、増減する可能性があります。
＜ 使用 Excel：Excel2007、使用 OS：WindowsXP ＞
全部見てないけど、ユーザーフォームにPublicで宣言しているなら、
Range("F2") = "=" & UserForm1.kngt & "-B2"
で取得できないですか？
（稲葉） 2015/09/29(火) 17:35
本当によく読んでなかった！ 標準モジュールで宣言している ＞Public kngt As Long, plg As Long これをUserFormで宣言して、↑の解答を参考に呼び込んでみてください。
（稲葉） 2015/09/29(火) 17:46
私もコードを精査しておらず眺めた程度ですが
標準モジュール側 Public変数
UserForm1 をモーダル表示
UserForm1 側 で Public変数にセット UserForm1(自身)をUnload
標準モジュール側でPublic変数を参照
これで、コード構成としては、問題はありません。 （逆にUserForm1側でPublicで変数をもつと、Unload で消滅しますので）
ただ、その変数のセットですけど
kngt = ComboBox1.ListIndex = 1
これは、具体的には何を変数にセットしていると理解していますか？ つまり、kngt にセットしているのは ComboBox1.ListIndex = 1 という式の評価結果なんですが したがって True(1)またはFalse(0) なんですけど、それでいいのですか？
（β） 2015/09/29(火) 18:08
たぶん
With ComboBox1
If .ListIndex = -1 Then
MsgBox "行が選択されていません。"
Else
kngt = .List(.ListIndex, 1)
plg = .List(.ListIndex, 2)
End If
End With
こんなことをしたかったのでしょうか？
で、本題とは離れますが、
・ユーザーフォーム側、コンボボックスからの選択があろうがなかろうが Unload ですね。 結果的に、選ばれなければ 補正値である kngt や plg は 0 ですけど、それはそれでいいのですね？
・Public Sub CommandButton1_Click() なぜ Public なんですか？
コード自体は、もう少しブラッシュアップできそうですね。
たとえば
readSheet.Range("B:B").Select
Application.CutCopyMode = False
Selection.Copy
writeSheet.Activate
Range("B:B").Select
ActiveSheet.Paste
Excel.Application.CutCopyMode = False
ここは
readSheet.Range("B:B").Copy writeSheet.Range("B1")
この１行で済みますし、基本的に Select/Selection は使わない努力をされたほうがいいですよ。
あと、シート修飾を明示しないで、 Range(なんたら) と アクティブシート頼みのコード（状況依存コードと呼んだりします） になっていますが、今、どのシートがアクティブなんだろうと、コードを書くときも神経を使いますし うっかりミスで間違う場合もあります。 また、後でコードを見た場合にも、わかりづらいです。
シート.Activate や シート.Select を行い Range(なんたら) という記述ではなく
シート.Range(なんたら) という記述にしましょう。
（β） 2015/09/29(火) 19:37
状況依存コードでよくある悪い例がコード内にありますので、ついでに。
Set readSheet = readBook.Worksheets("sheet1") ' 相手シートを参照
readSheet.Range("B:B").Select
もし、このブックを開いたときのアクティブシート（最後に保存されたときにアクティブになっていたシート）
が、Sheet1 ではなかった場合、readSheet.Range("B:B").Select ここで実行時エラーになってしまいます。
セルの選択は、アクティブシートに対してのみ許されますから。
（β） 2015/09/29(火) 19:54
帰宅してごはん作り終えたのでゆっくり見てみました。 早とちり×２ですみません。
でβさんがおっしゃるように、ブラッシュアップが必要と思います！ こちらで手直ししましたので、参考にしてください。 テスト環境準備していないので、動作確認まではしていません。
それから、標準モジュールにPublic変数置くのは私はお勧めしません。
UserFormから見ると、いきなりわけのわからない変数に入れているように見えてしまうからです。
で、最初に投稿したようにUserForm内にPublic変数を持ち、標準モジュールから参照する方法をお勧めします。
また、現状ではUserFormをキャンセルした場合でも処理が進んでしまい、具合が悪いのではないでしょうか？
その点を考慮し、下記の通り書き直しました。
ComboBoxの値もおそらくListの中にある1列目と2列目を取りたかったのかな？と思い
書き直しました。
ご確認お願いします。
（ちなみに明日終日パソコン見られないので、返事は木曜日以降になります。）
'============================================================
'UserForm1モジュール
'============================================================
Option Explicit
Public kngt As Long
Public plg As Long
Public flg As Boolean
Private Sub CommandButton1_Click()
With ComboBox1
If ComboBox1.ListIndex = -1 Then
MsgBox "行が選択されていません。"
Else
kngt = .List(.ListIndex, 1)
plg = .List(.ListIndex, 2)
flg = True
Me.Hide
End If
End With
End Sub
Private Sub UserForm_Initialize()
flg = False
With ComboBox1
.ColumnCount = 3
.RowSource = "補正値!A3:C" & Sheets("補正値").Range("A" & Rows.Count).End(xlUp).Row
End With
End Sub
'============================================================
'標準モジュール
'============================================================
Option Explicit
Sub 基データ参照()
Dim OpenF As Variant
Dim writeSheet As Worksheet
Dim UF As UserForm1
Set UF = UserForm1
UF.Show
If UF.flg Then
OpenF = Application.GetOpenFilename("Microsoft Excelブック,*.xls")
If VarType(OpenF) <> vbBoolean Then
Set writeSheet = ThisWorkbook.Sheets("データ") ' Sheet データ を参照
With Workbooks.Open(OpenF)
With .Sheets("Sheet1")
.Range("B:B").Copy writeSheet.Range("B:B")
.Range("C:C").Copy writeSheet.Range("D:D")
End With
writeSheet.Range("J4") = .Name 'ActiveWorkbook.Name
.Close savechanges:=False ' 相手ブックを閉じる
End With
With writeSheet
.Range("B1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("D1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Range("B1").Value = "金型"
.Range("D1").Value = "プラグ"
Dim lastRow As Long
lastRow = .Range("B" & Rows.Count).End(xlUp).Row
.Range("F:F").ClearContents
.Range("F1").Value = "金型補正値"
.Range("F2").Value = "=" & UF.kngt & "-B2"
.Range("F2").AutoFill Destination:=.Range("F2:F" & lastRow), Type:=xlFillSeries
lastRow = .Range("D" & Rows.Count).End(xlUp).Row
.Range("H:H").ClearContents
.Range("H1").Value = "プラグ補正値"
If .Range("D2").Value <> "" Then
.Range("H2").Value = "=" & UF.plg & "-D2"
.Range("H2").AutoFill Destination:=Range("H2:H" & lastRow), Type:=xlFillSeries
End If
' Dim WS2 As Worksheet
' Set WS2 = Sheets("データ")
.Range("A50:A" & Rows.Count).ClearContents
lastRow = .Range("B" & Rows.Count).End(xlUp).Row 'B列最終行(B列のデータによって変わる)
.Range("A2").Value = "00:00.01"
.Range("A3").Value = "00:00.02"
.Range("A2:A3").AutoFill Destination:=Range("A2:A" & lastRow), Type:=xlFillSeries 'A2からB列最終行のA列までオートフィル
End With
Else
MsgBox "ファイルが選択されないため、キャンセルされました"
End If
Else
MsgBox "ユーザーフォームでキャンセルされました。"
End If
Unload UF
Set UF = Nothing
End Sub
シート名の明示忘れてたので修正しました。2136
（ 稲葉） 2015/09/29(火) 21:31
>>それから、標準モジュールにPublic変数置くのは私はお勧めしません。
私も全く同感です。 コードの実行結果としては問題がないという意味でコメントしましたが 稲葉さんのコードのように UserForm1 は Unload ではなく Hide でメモリー内に残しておいて そこを参照するというのが、望ましい構成ですね。
ないしは、このUserForm1 の位置付けにもよりますが、これが、コンボボックス選択という汎用的なツールの位置づけではなく 標準モジュールでの処理を行うための専用フォームであれば、標準モジュールのロジックは UserForm1.Show だけにして、シートへの書き込みも含めて、このUserForm1 内で完結させる という構成も考えられますね。
（β） 2015/09/29(火) 21:40
＞＞それから、標準モジュールにPublic変数置くのは私はお勧めしません。 ＞私も全く同感です この「私」は、βさんの投稿に対する返答ではないです。 一般的な自分の見解を述べただけです。 紛らわしい書き方ですみません。
＞標準モジュールでの処理を行うための専用フォームであれば、標準モジュールのロジックは ＞UserForm1.Show だけにして、シートへの書き込みも含めて、このUserForm1 内で完結させる 言われてみれば、そうですよね。 UserForm内にも、ブックを参照する記述がありますから、このまま使うと結合度が高くなりすぎそうですし。
メロウさんへ というわけで、今回のケース、私もUserForm内完結が良いと思います！ 質問の趣旨は「ユーザーフォームから標準モジュールを参照したい」から、 「標準モジュールからユーザーフォームを参照したい」になって 「ユーザーフォームで完結したほうが今回のケースでは良い」になってしまいましたが。
勉強のため当初の趣旨通りの動作を希望なら、UserFormの ＞kngt = ComboBox1.ListIndex = 1 Module1.kngt = ComboBox1.List(ComboBox1.ListIndex , 1) とすると良いと思います。
なぜ今までの記述ではだめかというと、 kngtがいきなり出てきたら、どこの変数かわかりません。 ですので、標準モジュールの名前を付けて指定されるとよいでしょう。
次に ComboBox1.ListIndex = 1 の部分、1が選択されていたらTrue、それ以外ならFalseの値がkngtに入ります。 kngtはLong型ですので、何を選んでも1と0しか返りません。 提示された表を見ると、2列目と3列目の値を取得したいと推測できましたので List(Index,列)というプロパティで取得させているという具合です。
標準モジュールからユーザーフォームを参照する方法は、私が先に提示した通りです。
ユーザーフォーム完結型は、おそらくメロウさんに作れると思いますので、頑張ってみてください。 （稲葉） 2015/09/30(水) 05:39
（メロウ） 2015/09/30(水) 09:14
上記分ですが、ようやくユーザーフォーム完結型が完成しました！！！！！
感動の一言です。
本当にありがとうございました！！！！！
（メロウ） 2015/10/06(火) 17:39
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.