[[20150929162104]] 『ユーザーフォームで取得した変数を標準モジュール』(メロウ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『ユーザーフォームで取得した変数を標準モジュールで使用したい』(メロウ)

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")

 この1行で済みますし、基本的に 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


 帰宅してごはん作り終えたのでゆっくり見てみました。
 早とちり×2ですみません。

 でβさんがおっしゃるように、ブラッシュアップが必要と思います!
 こちらで手直ししましたので、参考にしてください。
 テスト環境準備していないので、動作確認まではしていません。

 それから、標準モジュールに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.