2 questions: sheet reference and adding chart

[moradisndat]

I hope it's ok to ask 2 questions in 1 post if they are not related.

Is there any difference in how fast the statements below will run? I'm just trying to find the best practice.

ThisWorkbook.Sheets("NHL").Visible=False
or
Sheets("NHL").Visible=False

or can object variable work
wsNHL.Visible=False
---------------------------------------
2nd question:
I was trying to use object variables to speed up code but had problems adding charts. The only way I could make it work was by using "ActiveSheet." Is there a better way to add an embedded chart than what I have below?

LR = ActiveCell.CurrentRegion.End(xlDown).Row
wsNew.Range("H13", "I" & LR).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart. ' several lines follow to change ' chart properties

Thanks in advance

 

[Domenic]

Is there any difference in how fast the statements below will run? I'm just trying to find the best practice.

ThisWorkbook.Sheets("NHL").Visible=False
or
Sheets("NHL").Visible=False

or can object variable work
wsNHL.Visible=False

My guess is that any difference in efficiency would be negligible.

However, there's a difference between ThisWorkbook.Sheets("NHL").Visible=False and Sheets("NHL").Visible=False. The former refers to the workbook running the code, whereas the latter refers to the active workbook since the workbook isn't specified.

Also, it's always a good idea to assign your sheet to an object variable so that you can easily refer to it without having to make sure that the appropriate workbook is the active workbook, as would be the case with Sheets("NHL").Visible=False.

I was trying to use object variables to speed up code but had problems adding charts. The only way I could make it work was by using "ActiveSheet." Is there a better way to add an embedded chart than what I have below?

LR = ActiveCell.CurrentRegion.End(xlDown).Row
wsNew.Range("H13", "I" & LR).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart. ' several lines follow to change ' chart properties

Here's an example...

Option Explicit

Sub test()

    'get the source worksheet (change the workbook and sheet reference accordingly)
    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ThisWorkbook.Worksheets("Sheet1")
    
    'get the last used row in Column H from the source worksheet
    Dim lastRow As Long
    With sourceWorksheet
        lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
    End With

    'get the source range
    Dim sourceRange As Range
    Set sourceRange = sourceWorksheet.Range("H13:I" & lastRow)

    'create the chart (change the sheet reference accordingly)
    Dim shp As Shape
    With ActiveSheet
        Set shp = .Shapes.AddChart(XlChartType:=xlColumnClustered, Left:=.Range("B2").Left, Top:=.Range("B2").Top, Width:=360, Height:=220)
    End With
    
    'set the properties
    With shp
        'set Shape object properties
        .LockAspectRatio = msoTrue
        '
        '
        With shp.Chart
            'set Chart object properties
            .SetSourceData sourceRange
            '
            '
            '
        End With
    End With

End Sub

Hope this helps!

 

[RoryA]

I'm just trying to find the best practice.

Best practice would be to use xlSheetHidden, xlSheetVisible or xlSheetVeryHidden, since Worksheet.Visible is not a Boolean property.

 

[moradisndat]

My guess is that any difference in efficiency would be negligible.

However, there's a difference between ThisWorkbook.Sheets("NHL").Visible=False and Sheets("NHL").Visible=False. The former refers to the workbook running the code, whereas the latter refers to the active workbook since the workbook isn't specified.

Also, it's always a good idea to assign your sheet to an object variable so that you can easily refer to it without having to make sure that the appropriate workbook is the active workbook, as would be the case with Sheets("NHL").Visible=False.


Here's an example...

Option Explicit

Sub test()

    'get the source worksheet (change the workbook and sheet reference accordingly)
    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = ThisWorkbook.Worksheets("Sheet1")
   
    'get the last used row in Column H from the source worksheet
    Dim lastRow As Long
    With sourceWorksheet
        lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
    End With

    'get the source range
    Dim sourceRange As Range
    Set sourceRange = sourceWorksheet.Range("H13:I" & lastRow)

    'create the chart (change the sheet reference accordingly)
    Dim shp As Shape
    With ActiveSheet
        Set shp = .Shapes.AddChart(XlChartType:=xlColumnClustered, Left:=.Range("B2").Left, Top:=.Range("B2").Top, Width:=360, Height:=220)
    End With
   
    'set the properties
    With shp
        'set Shape object properties
        .LockAspectRatio = msoTrue
        '
        '
        With shp.Chart
            'set Chart object properties
            .SetSourceData sourceRange
            '
            '
            '
        End With
    End With

End Sub

Hope this helps!

Thanks so much for your help. Have a wonderful day.

 

[moradisndat]

Best practice would be to use xlSheetHidden, xlSheetVisible or xlSheetVeryHidden, since Worksheet.Visible is not a Boolean property.

I didn't know that. It seemed to work, but there is a long delay. Maybe that's why it's running so slowly. I will make the changes. Thanks for your advice. Much appreciated!