Jump to content



Photo

excel 2013 performance issue


  • Please log in to reply
3 replies to this topic

#1 capr

capr

    Neowinian Senior

  • Joined: 01-July 05

Posted 02 April 2013 - 19:28

Hey so I have a simple code I am using in excel. office 2010 ran through the code in seconds. 2013, takes forever... the code is really simple, I am including it here.

Sub M72_M73()
Dim i, j As Integer
Dim minM72, minM73 As Integer
Dim minB44 As Double
minM72 = ThisWorkbook.ActiveSheet.Cells(72, 13)
minM73 = ThisWorkbook.ActiveSheet.Cells(73, 13)
minB44 = ThisWorkbook.ActiveSheet.Cells(44, 2)
For i = 0 To 100
	For j = 0 To 100 Step 5
		ThisWorkbook.ActiveSheet.Cells(72, 13) = i / 100 '13 means M
		ThisWorkbook.ActiveSheet.Cells(73, 13) = j / 100 '13 means M
		DoEvents
		If (ThisWorkbook.ActiveSheet.Cells(44, 2) < minB44) Then
			minM72 = i
			minM73 = j
			minB44 = ThisWorkbook.ActiveSheet.Cells(44, 2)
		End If
	Next j
Next i
ThisWorkbook.ActiveSheet.Cells(72, 13) = minM72 / 100
If minM72 = 0 Then
	For i = 0 To 100
		ThisWorkbook.ActiveSheet.Cells(73, 13) = i / 100
		DoEvents
		If (ThisWorkbook.ActiveSheet.Cells(44, 2) < minB44) Then
			minM73 = i
		End If
	Next i
End If
ThisWorkbook.ActiveSheet.Cells(73, 13) = minM73 / 100
DoEvents
End Sub

Any idea why this is taking much much longer in office 2013? I would hate to have to switch back to 2010.


#2 BGM

BGM

    Wibble Wobble™

  • Joined: 30-March 03
  • Location: Farnborough, UK

Posted 02 April 2013 - 19:46

if you google vba excel 2013 performance, there are loads of similar queries... all without answers.

i have a good mind to think it's been done on purpose to ween people off of VBA! haha

sorry, i know it's not much help :(

also, i just ran it and it seems to run very quickly in my Excel 2013... what does 'DoEvents' actually do?

can you provide a workbook with sample data at all ?

#3 sc302

sc302

    Neowinian Senior

  • Tech Issues Solved: 36
  • Joined: 12-July 05
  • Location: NJ, USA

Posted 02 April 2013 - 20:11

could be a update hosing you.
http://social.msdn.m...b-d924838bfc63/

#4 OP capr

capr

    Neowinian Senior

  • Joined: 01-July 05

Posted 02 April 2013 - 23:56

ween people off of VBA??? oh no! it's so easy...

DoEvent writes the number into those cells and a bunch of cells dynamically update until the final cost is calculated and compared to the old cost.

I can't share the file since it's confidential information. I have a 365 subscription so I will bug MS till I get an answer.


updates: I found a few updates that really improved performance. it's still not where office 2010 was but it's usable now.

Edited by capr, 03 April 2013 - 00:20.