Copy

Newsletter by jkp-ads.com

View this email in your browser

jkp-ads.com Newsletter for January 2017

Best wishes!

2016 has passed and a new year has started. I wish you all a very happy and prosperous 2017!

A bug in the Excel Styles Object (VBA)

I have recently discovered a bug in the Styles object in Excel VBA. The bug is reproduceable in Excel versions 2003 up to and including 2016. What it comes down to is that if you want to work with a style from VBA, you MUST make sure the workbook you want to have VBA make changes to is the active workbook.

If you try to add a style to workbook A whilst workbook B is active, the style is added to both workbooks A and B. If you try to modify a style in workbook A while workbook B is active, the style is modified in workbook B. If that style does not exist in workbook B, it is added to it too. See the demo code below.

Sub StyleBugDemo()
    Dim oSt As Style
    Dim oTempwb As Workbook
    'This code demonstrates bugs in Excel (VBA) where
    
    '1. Adding a style to a workbook causes that style to be added to the activeworkbook as well
    'if the workbook the VBA code is adding the style to is NOT the active workbook
    
    '2. changing a style for Thisworkbook
    'fails if another workbook is open which has a protected worksheet

    '1.
    'This works
    On Error Resume Next
    ThisWorkbook.Styles("foobar").Delete
    On Error GoTo 0
    With ThisWorkbook
        Set oSt = .Styles.Add("FooBar")
        oSt.Font.Name = "Arial"
    End With
    
    'This works, but the style gets added to both thisworkbook AND the active workbook!
    On Error Resume Next
    ThisWorkbook.Styles("foobar").Delete
    Set oTempwb = Workbooks.Add
    On Error GoTo 0
    With ThisWorkbook
        Set oSt = .Styles.Add("FooBar")
        '2. this now modifies the font of the style in oTempWb, NOT in Thisworkbook!!!
        oSt.Font.Name = "Arial"
    End With
    MsgBox "Font of Style in temporary wb: " & oTempwb.Styles("foobar").Font.Name
    'It even reports the wrong style's properties:
    MsgBox "Font of Style in Thiswb: " & ThisWorkbook.Styles("foobar").Font.Name & ", active workbook is NOT TWB"
    ThisWorkbook.Activate
    MsgBox "Font of Style in Thiswb: " & ThisWorkbook.Styles("foobar").Font.Name
    oTempwb.Close False

    '2.
    'This fails
    On Error Resume Next
    ThisWorkbook.Styles("foobar").Delete
    On Error GoTo 0
    Set oTempwb = Workbooks.Add
    ActiveWorkbook.Worksheets(1).Protect
    With ThisWorkbook
        Set oSt = .Styles.Add("FooBar")
        oSt.Font.Name = "Arial"
    End With

    oTempwb.Close False
    
    'This works
    On Error Resume Next
    ThisWorkbook.Styles("foobar").Delete
    On Error GoTo 0
    Set oTempwb = Workbooks.Add
    ActiveWorkbook.Worksheets(1).Protect
    ThisWorkbook.Activate
    With ThisWorkbook
        Set oSt = .Styles.Add("FooBar")
        oSt.Font.Name = "Arial"
    End With
    
    oTempwb.Close False
End Sub
 

The Amsterdam Excel Summit

It's been a while, but we've finally fixed the dates for our 2017 Amsterdam Excel Summit!

April 18th, 2017: The Amsterdam Excel Summit pre-conference Masterclass, an entire day of in-depth training by die-hard Excel experts

April 19, 2017: The Amsterdam Excel Summit. Featuring Excel MVPs from all over the world presenting in their core expertise areas. Also featuring: Product managers from the Microsoft Excel team.

If you would like to reserve your seats already, we haven't put up registration yet, but don't hesistate to send us an email with your details and we'll make sure you're on the list!

Program

The program will be announced on our event site topexcelclass.com

Auditing of Formulas made easy

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.
 

Mission-critical Excel Model Crashes

Do you have mission-critical Excel files that cause problems? Consider our Excel File Remediation Utility
.
 
Copyright © 2017 JKP Application Development Services, All rights reserved.


unsubscribe from this list    update subscription preferences 

Email Marketing Powered by Mailchimp