Excel with VBA codes, how to optimise

[ODPaul82]

It's kind of. Without going full geek, each line as you enter it gets turned into whats called op-code in the background and then when you run or compile it gets turned into p-code.
Have been coding in VBA for 20+ years now with it being my primary language. Day job is in financial systems for insurance company.

[Derek27]

It's kind of. Without going full geek, each line as you enter it gets turned into whats called op-code in the background and then when you run or compile it gets turned into p-code.
Have been coding in VBA for 20+ years now with it being my primary language. Day job is in financial systems for insurance company.

I worked in insurance as well, programming in COBOL running on an IBM mainframe under MVS. If you can write in English you can code in COBOL:

MULTIPLY A BY B GIVING C

All capital letters as well, with a maximum 80-character lines, presumably because just before my time, code was typed onto punch cards.

[ODPaul82]

I worked in insurance as well, programming in COBOL running on an IBM mainframe under MVS. If you can write in English you can code in COBOL:

MULTIPLY A BY B GIVING C

All capital letters as well, with a maximum 80-character lines, presumably because just before my time, code was typed onto punch cards.

Ha, we still have a COBOL based mainframe (AS400 virtualised) running with the vast majority of our data saved on it
There's currently a project in place to move the whole business onto Azure and not have any physical servers on premises
Want everything 64bit, I've thrown the proverbial spanner into the works by pointing out half of finance will stop working as our instance of Excel is 32bit with thousands of workbooks referencing 32bit DLL code

[rain91]

Hi guys, can you help me please?

I have this code on the Modul (just a detail):

Code: Select all

Sub Program()

Application.EnableEvents = False

For i = 9 To 67 Step 2
'''''''''''''''''''''''''''''''''
If Range("O" & i) = "" And Range("DD" & i) <> Range("DC" & i) And Range("DD" & i) <> "" Then
Range("L" & i) = Range("DD" & i)
Range("DE" & i) = Range("F4")
Range("DC" & i) = Range("L" & i)
End If

If Range("O" & i) = "PLACED" Or Range("O" & i) = "OK" Then
Range("L" & i).ClearContents
Range("O" & i).ClearContents
End If

Application.EnableEvents = True

End Sub

And this code on all sheet (now 32 sheet)

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$C$2:$C$6" Then
     Call Program
   End If
End Sub

The performance is a bit bitter, and the refresh rate (in Guardian 20ms) is ~12sec.

But the problem, the excel do nothing, just on the active sheet (what i watch actually). Why?

[Bobajob]

It's kind of. Without going full geek, each line as you enter it gets turned into whats called op-code in the background and then when you run or compile it gets turned into p-code.
Have been coding in VBA for 20+ years now with it being my primary language. Day job is in financial systems for insurance company.

I worked in insurance as well, programming in COBOL running on an IBM mainframe under MVS. If you can write in English you can code in COBOL:

MULTIPLY A BY B GIVING C

All capital letters as well, with a maximum 80-character lines, presumably because just before my time, code was typed onto punch cards.

"programming in COBOL running on an IBM mainframe under MVS."
Those were the days, how we miss them

[ODPaul82]

Hi guys, can you help me please?

I have this code on the Modul (just a detail):
...

Try using Intersect rather doing this

Code: Select all

 If Target.Address = "$C$2:$C$6" Then
 

Also where you have "Call Program" the need to use Call was deprecated about 20 years ago.

[ShaunWhite]

running on an IBM mainframe under MVS."

And then some Silicon Valley marketing guy called it a 'Cloud' and now everyone thinks it's new.

We started with dumb terminals and mainframes, and we'll end up there again. And the interveving 50yrs of wasted money and environmental impact of more and more powerful desktops will have been a waste of time.

[Bobajob]

running on an IBM mainframe under MVS."

And then some Silicon Valley marketing guy called it a 'Cloud' and now everyone thinks it's new.

We started with dumb terminals and mainframes, and we'll end up there again. And the interveving 50yrs of wasted money and environmental impact of more and more powerful desktops will have been a waste of time.

You're right - hadn't realised that's effectively what's happening - we are all going back to being dumb and Bet Angel will be running on the cloud