Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    [solved]EXcel 2013 - Code seems to be bypassed if I don't put a break in

    I have some code as shown below which selects a column and sets the format to 2 decimal places (all the data is numeric). If I don't put in a break at the "columns(I).select" line, the output doesn't seem to have the format applied. If I do put a break in and then use F5 to continue, it works.

    Basically, the code is looking at a parameter field - if it contains the word "num", then I know that column is numeric and therefore it needs to be set to 2 decimal places.

    Anyone know what on earth is going on here?

    Code:
            If StrConv(datefield(i), vbLowerCase) = "num" Then
                Columns(i).Select
                Application.CutCopyMode = False
                Selection.NumberFormat = "0.00"
            End If
    Thanks for any advice

    regards

    Alan
    Last edited by alan sh; 2014-12-18 at 13:56. Reason: solved

  2. #2
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Update - it seems to work OK if I use Excel 2010.

    Wonderful!!! (Not)

    Alan

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Alan

    try this:
    Code:
    If StrConv(datefield(i), vbLowerCase) = "num" Then
        Columns(i).NumberFormat = "0.00"
    End If
    zeddy

  4. #4
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Zeddy,

    That made no difference. Still the same issue.

    I've tried it on 3 machines with Excel 2013 - fails on all of them - and 2 machines with Excel 2010 - works on all of them

    Sorry

    Alan

  5. #5
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    I've written some horrible code to get around it - basically each time I write a cell, I check to see if the value should be a number or a date (it fails on both) and then format the cell accordingly.

    That works, but it makes it a bit slower to run.

    Alan

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    New York, New York, USA
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Try applying the latest December Office 2013 updates.
    I had a similar problem when I was testing Excel 2013 user instructions. Selecting a column and applying a decimal format did not work when at least one value was non-numeric (such as a row 1 header). Tonight after applying the latest updates, the problem has disappeared for me.

  7. #7
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    David - thanks for that. I will certainly look for updates, but I am not sure it's the same problem because it works if I put a break in there.

    I'll keep you all posted

    Alan

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Alan,

    Interesting thread. I had noticed issues similar to yours in the past which gave me them impression that perhaps multi-threading cores might be the issue. Code should have executed in a certain order but some lines of code seemed skipped. For example, a message box did not appear following a loop. Even though I was always lead to believe that VBA execution was linear, my thought was that perhaps some cores were involved in one process while others took over another and in the mix, something got missed. I went to options > Advanced and disabled multi-threading calculation and tried keeping it enabled but used manual with only one processor. And it helped. Both methods seemed to have the same effect. Only in the rarest occasion with the heaviest of codes will this not resolve the problem. Looking for a way to do this with VBA was right there. Seems others have had this issue since Excel 2007 on.

    HTH,
    Maud

    Code:
    Sub Somefunction()
    
    '--------code------------------
    
    Application.MultiThreadedCalculation.Enabled = False
    
    '--------code------------------
    
    Application.MultiThreadedCalculation.Enabled = True
     End Sub
    Whenever I experience that issue, placing these lines of code seems to work

  9. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    alan sh (2014-12-17),MartinM (2014-12-17)

  10. #9
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Maud,

    That's very interesting and something I was leaning to. I didn't know about the code you mentioned but I will try it out when I am back home tomorrow. I've also updated one of my Excel 2013 machines with the latest MS updates - so I will try the existing code on that one first (although I hold out little hope)


    It is a real pain having to put the extra code in at odd places as you don't know what won't work until it fails....

    I'll keep you posted

    Cheers

    Alan

  11. #10
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    OK - Just tried it (I have an excel 2013 machine here) and it made no difference - whether I did it from options or from the code.

    Very weird. I need to do more investigation

    Alan

  12. #11
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    And I've found the problem....... A series of coincidences and muddles.

    It appears that Excel 2013 handles which sheet/tab is the active one slightly differently to Excel 2010. When I put in "wodata.Activate" just before my code (wodata is my variable for the output sheet) the code worked just fine.

    Why did it work before if I put a break in? Probably because the break itself did something to make the correct sheet/tab the active one - or I had a "look" at the sheet before I pressed F5 which made it the active one.

    Why did it work in Excel 2010? As I said, it will be something slightly different in the code.

    Thanks for the thoughts everyone - it made me think differently about what I was doing.

    Cheers

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •