Results 1 to 10 of 10

Thread: Excel 2000 VB not working in 2003

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2007
    Posts
    14

    Excel 2000 VB not working in 2003

    I have a complex application that works fine in Excel 2000. It is cumbersome at times due to the massive amount of code that gets execute everytime cells change but I have gotten around that by manually performing the calculations via a Command Button to calculate the workbook.

    However, the issue I am having is that someone has tried to open this workbook in Excel 2003 and it just churns during the open process.

    I had many statements in the code to Protect/Unprotect the sheets in the workbook in order to hide the calculations and sheets where values were being sourced from. During the Open/SaveAs process many errors ensued for trying to write to protected areas of the wookbook and they never were able to save the workbook completely in 2003 (absolutely no issues in 2000). To that end, I removed all the Protect/Unprotect statements and now the workbook won't even open in 2003 (again no issues in 2000).

    Is anyone aware of compatability issues between 2000/2003 with respect to VB?

    Thanks.

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Excel 2000 VB not working in 2003

    Moved to Office Development

  3. #3
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Excel 2000 VB not working in 2003

    I dont believe so but it depends on the code you are using in the Workbook_OPen or autostart procedure. You would have to check if the functions, methods, and properties you are using are still used in 2003 and if the arguments (if any) are also the same.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2007
    Posts
    14

    Re: Excel 2000 VB not working in 2003

    I now have Excel 2003 on my platform and I have tracked down an issues as follows:

    Code:
    Private Sub Worksheet_Calculate()
    
    If Range("C93").Value = 0 Then
        Rows("93:93").EntireRow.Hidden = True
    Else
        Rows("93:93").EntireRow.Hidden = False
    End If
    If Range("C94").Value = 0 Then
        Rows("94:94").EntireRow.Hidden = True
    Else
        Rows("94:94").EntireRow.Hidden = False
    End If
    The first IF is true, so it executes the ROWS statement. However, what is happenning is that instead of falling thru to the End If statement, it goes back up to the Private Sub again thus causing a loop and never finishing.

    So what do you think the problem may be?

    Thanks!!!

  5. #5
    Lively Member
    Join Date
    Feb 2007
    Location
    Dublin, Ireland
    Posts
    120

    Re: Excel 2000 VB not working in 2003

    Why not just place an Exit Sub after each of the if statements? Therefore this stops the looping. I don't see any procedure to loop, therefore this should work.
    Thanks.

  6. #6

    Thread Starter
    New Member
    Join Date
    Apr 2007
    Posts
    14

    Re: Excel 2000 VB not working in 2003

    Unfortunately that does not work. In stepping thru the code (use of the F8 key), after the Rows statement is executed it returns immediately to the Private Sub statement again.

    Thanks.

  7. #7
    Lively Member
    Join Date
    Feb 2007
    Location
    Dublin, Ireland
    Posts
    120

    Re: Excel 2000 VB not working in 2003

    Try the Goto Label: statement to continue from where you wanted to previously.
    "Rows("94:94").EntireRow.Hidden = False"
    'put the Goto Label
    or
    'Exit Sub
    I meant after each condition in the if statement.
    Thanks.

  8. #8

    Thread Starter
    New Member
    Join Date
    Apr 2007
    Posts
    14

    Re: Excel 2000 VB not working in 2003

    That doesn't do it. It never gets to the goto statement.

  9. #9
    Lively Member
    Join Date
    Feb 2007
    Location
    Dublin, Ireland
    Posts
    120

    Re: Excel 2000 VB not working in 2003

    Hi,
    God your code looks fine. Did you try to trap an error. Any result?

  10. #10

    Thread Starter
    New Member
    Join Date
    Apr 2007
    Posts
    14

    Re: Excel 2000 VB not working in 2003

    Well, perhaps I didn't tell the entire story. The code for the sheet in question actually was as follows:

    Code:
    Private Sub CommandButton1_Click()
    Application.Calculation = xlCalculationAutomatic
    Application.Calculation = xlCalculationManual
    End Sub
    
    Private Sub Worksheet_Calculate()
    If Range("C93").Value = 0 Then
        Rows("93:93").EntireRow.Hidden = True
    Else
        Rows("93:93").EntireRow.Hidden = False
    End If
    If Range("C94").Value = 0 Then
        Rows("94:94").EntireRow.Hidden = True
    Else
        Rows("94:94").EntireRow.Hidden = False
    End If
    End Sub
    What I did was combine these two subroutines into one based on the execution of the Command Button and all is well now. However, this is a change from Excel 2000 to Excel 2003 because it worked fine (which may have been just a break on my part ) but of course fails in 2003. What other idiosyncrasies will I run into in 2007?

    Thanks for your help on this.

Posting Permissions

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



Click Here to Expand Forum to Full Width