Very urgent Excel 2007 Problem

[gaj_cod]

Dear all

We are executing certain simulations in excel based applications. I am encountering two issues. I have searched a lot but could not find any satisfactory response. Here are the problems:

1. The code runs very slow in Excel 2007 and it runs fine in Excel 2003. Number of users have reported the same problem but no satisfactory response.

2. Second point is a thought of mine. I am just a beginner in VB but I would like to ask if we can restrict the events to the grid only?? I mean if I lock, it locks the entire sheet via events. Can event be triggered only for my data grid in the sheet?

This is urgent!!

Please reply !!

Regards
Gajendra

 

[VoG]

1. You need to post the code.

2. To restrict event code to a particular range you can do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B5:G15")) Is Nothing Then Exit Sub
'
'your code here
'
End Sub

 

[gaj_cod]

Hi Vog

Thanks a lot for your response.

We guys here are dying because of a code that runs well in 2003 but eating time like anything in 2007.

I learned that VB has not been updated. Is there any way to make the same code run fast in 2007?

This is really killing us as we have developed the applications in 2003 and our users are having 2007 installed in their systems.

Any help will be cordially appreciated.

Regards
Gajendra

 

[TFH]

When I close the developer TAB my code runs much faster. When its open, it runs very slow.

 

[gaj_cod]

Dear TFH

Thanks for your input but that did not work out for us.

We need to optimize the code. I have tried to incorporate various practices. The code runs well within the limit in 2003 but it is taking time in 2007.

The application is a workbook on SAP BEx (business explorer).

I am not sure if you guys have great idea about it.

But anyways, all I am looking is that are there any specific measures to be taken while writing the code in excel 2007 than in excel 2003 to ensure at par performance.


This is very urgent for us.


Regards
Gajendra

 

[gaj_cod]

Dear all

We are assigning formulae to the cells as shown below:

Cells(vFRow + i * vRowPerModel + 1, j).Formula = "=" + Cells(vFRow + i * vRowPerModel + 0, j).Address + "*" + Cells(vFRow + i * vRowPerModel + 2, j).Address

Is there any other better way to do the same?

Does it make difference if we use "While" loop instead of "For" loop?

Regards
Gajendra

 

[tusharm]

Searched Google? I found several interesting leads though no definitive answers including at msdn.microsoft.com and dailydoseofexcel.com

 

[gaj_cod]

Hi Tushar

I have searched on Google also.........

I am awaiting responses.....

Any luck? Any trick? Any clue?


Regards
Gajendra

 

[Sal Paradise]

If you won't post your code for troubleshooting, then you'll have to isolate the problem portion of your code if you want people to optimize it. I suggest you add a bunch of timers and see where it runs slow. Once you find that portion, post it here so people can improve it.