passing workbook from command button vba

Question

I have a question about something which is confusing me in VBA. I have a command button, which when clicked I want to set a variable = to a workbook object, then pass this object to another sub. I cannot work out why I can't pass it to the sub, I can't see why what I'm doing doesn't work. Code as follows:

Private Sub CommandButton2_Click()
Workbooks.Open Filename:=TextBox1.Text
Set wb = ActiveWorkbook

CreateChart wb
End Sub

Sub CreateChart(ByRef wb As Workbook)
'PURPOSE: Create a chart (chart dimensions are not required)

Dim rng As Range
Dim cht As Object

'Your data range for the chart
  Set rng = wb.ActiveSheet.Range("A24:A27")

'Create a chart
  Set cht = ActiveSheet.Shapes.AddChart2

'Give chart some data
  cht.Chart.SetSourceData Source:=rng

'Determine the chart type
  cht.Chart.ChartType = xlXYScatterLines

End Sub

NB I have found a workaround which is to set the workbook within another sub, but would like to understand why it doesn't work this way. Please can anyone offer advice?

Thankyou.

Did you try to declare wb in Private Sub CommandButton2 first? As in Dim wb As Workbook — AntiDrondert
– AntiDrondert, Commented Feb 14, 2018 at 10:41

Andre · Accepted Answer · 2018-02-14 10:47:11Z

1

In CommandButton2_Click() you don't declare wb. So it's a Variant data type. This isn't compatible to ByRef wb As Workbook.

So add

Dim wb As Workbook

and to avoid further errors like this, put Option Explicit at the top of each module.

It enforces variable declaration and reports undeclared or misspelled variables/constants at compile time. To have this automatically in new modules, set the Require Variable Declaration option in the VBA Editor.

Edit: Demo

'Option Explicit

Sub Demo_Fail()
    Set wb = ActiveWorkbook
    CreateChart wb      ' Compile error: ByRef argument type mismatch
End Sub

Sub Demo_Success()
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    CreateChart wb
End Sub

Sub CreateChart(ByRef wb As Workbook)
    Debug.Print wb.Name
End Sub

edited Feb 14, 2018 at 10:47

answered Feb 14, 2018 at 10:42

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christian T Over a year ago

Thankyou, this helps my understanding. Just found it weird as I was able to use the exact same code but in a sub that wasn't the button click and it worked...

DisplayName · Accepted Answer · 2018-02-14 10:45:33Z

1

It's because you don't declare explicitly wb variable, so it's assumed implicitly as being of Variant type, while CreateChart accepts a Workbook type parameter

use:

Private Sub CommandButton1_Click()
    Dim wb As Workbook ' declare wb of Workbook type

    Workbooks.Open Filename:=TextBox1.Text
    Set wb = ActiveWorkbook

    CreateChart wb
End Sub

but you can even shorten that down to:

Private Sub CommandButton1_Click()        
    CreateChart Workbooks.Open(Filename:=TextBox1.Text)
End Sub

answered Feb 14, 2018 at 10:45

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Collectives™ on Stack Overflow

passing workbook from command button vba

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related