Excel VBA 引数が2個のマクロの呼び出し方
ExcelのVBAで、
シート上のボタンがクリックされた時に呼び出す
マクロ(プロシージャ)の引数が1個の時は、
コード1のようにできましたが、
引数が2個ある時は、コード2のように記述しても、
ボタンをクリックするとエラーになりますが、
【？】の部分をどのように記述すればよいのでしょうか。
(Windows10,Excel2010)
-------------------コード1----------------------------------------
Sub test1()
Dim row As Integer
Dim wave_file_path As String
For row = 1 To 2
wave_file_path = ThisWorkbook.Worksheets("Sheet1").Cells(row, 1).Value
Call ボタン作成(row, wave_file_path)
Next row
End Sub
Sub ボタン作成(ByVal row As Integer, ByVal wave_file_path As String)
Dim cell_loc As String
cell_loc = ThisWorkbook.Worksheets("Sheet1").Cells(row, 3).Address
ThisWorkbook.Worksheets("Sheet1").Select
With ActiveSheet.Buttons.Add(Range(cell_loc).Left, _
Range(cell_loc).Top, _
Range(cell_loc).Width, _
Range(cell_loc).Height)
.name = "ボタン_" & cell_loc
.OnAction = "'WAVE_PLAY """ & wave_file_path & "" & "'"
.Characters.Text = "再生"
End With
End Sub
Sub WAVE_PLAY(ByVal wave_file_path As String)
If Dir(wave_file_path) = "" Then
MsgBox wave_file_path & vbCrLf & "がありません。", vbExclamation
Exit Sub
End If
Shell "C:\Program Files\Windows Media Player\wmplayer.exe /play /close " & wave_file_path
End Sub
-------------------------------------------------------------------
-------------------コード2----------------------------------------
Sub test2()
Dim row As Integer
Dim wave_file_path As String
For row = 1 To 2
wave_file_path = ThisWorkbook.Worksheets("Sheet1").Cells(row, 1).Value
Call ボタン作成(row, wave_file_path)
Next row
End Sub
Sub ボタン作成(ByVal row As Integer, ByVal wave_file_path As String)
Dim cell_loc As String
cell_loc = ThisWorkbook.Worksheets("Sheet1").Cells(row, 3).Address
ThisWorkbook.Worksheets("Sheet1").Select
With ActiveSheet.Buttons.Add(Range(cell_loc).Left, _
Range(cell_loc).Top, _
Range(cell_loc).Width, _
Range(cell_loc).Height)
.name = "ボタン_" & cell_loc
.OnAction = "'WAVE_PLAY """ & wave_file_path & "" & "," & row & "'" <==【？】
.Characters.Text = "再生"
End With
End Sub
Sub WAVE_PLAY(ByVal wave_file_path As String, ByVal row As Integer)
If Dir(wave_file_path) = "" Then
MsgBox wave_file_path & vbCrLf & "がありません。", vbExclamation
Exit Sub
End If
Shell "C:\Program Files\Windows Media Player\wmplayer.exe /play /close " & wave_file_path
ThisWorkbook.Worksheets("Sheet1").Cells(row, 4).Value = "再生済"
End Sub
-------------------------------------------------------------------
お礼
有り難うございまました。 使えるようになりました。