-
May 3rd, 2007, 12:56 PM
#1
Thread Starter
New Member
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.
-
May 4th, 2007, 10:15 AM
#2
Re: Excel 2000 VB not working in 2003
Moved to Office Development
-
May 4th, 2007, 04:26 PM
#3
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
May 23rd, 2007, 10:47 AM
#4
Thread Starter
New Member
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!!!
-
May 24th, 2007, 05:30 AM
#5
Lively Member
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.
-
May 24th, 2007, 07:29 AM
#6
Thread Starter
New Member
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.
-
May 24th, 2007, 07:50 AM
#7
Lively Member
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.
-
May 24th, 2007, 09:53 AM
#8
Thread Starter
New Member
Re: Excel 2000 VB not working in 2003
That doesn't do it. It never gets to the goto statement.
-
May 25th, 2007, 05:47 AM
#9
Lively Member
Re: Excel 2000 VB not working in 2003
Hi,
God your code looks fine. Did you try to trap an error. Any result?
-
May 25th, 2007, 08:29 AM
#10
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|