Jak zrychlit práci/výkon excelu

[Zivan]

Ve VB nedelam, ale 24 000x tam do promenne vkladas vzorec s mezerami a pak je odstranujes. To bych dal pred cykly, pak to budes delat jen 3x.

[lubo.]

Krátká odpověď : Celé to předělej.

Pracuješ s větší oblastí po jednotlivých buňkách. V každém kroku vkládáš do buněk vzorce a excel to musí několikrát přepočítat. Ty vzorce se počítají dost dlouho. Volání vzorců z vba také není nejrychlejší.

[VOM]

xlnc - manuální přepočet již jsem doplnil do předloženého kódu, zkrátilo běh makra cca z 500 sekund na 150. Díky.
Zivan, Lubo - nevím jak udělat. Můžete být konkrétnější prosím?

[Zivan]

To cervene presunout na zelene (pred FOR) a ve vsech trech cyklech.
...
'Slozeni vzorce
vzorec = "=(INDEX(mat_costs!$A$2:$J$111;POZVYHLEDAT(E" + Str(i) + ";mat_costs!$A$2:$A$111;0);POZVYHLEDAT(G" + Str(i) + ";mat_costs!$A$1:$J$1;1)+1))/specification!$L$1"
' 'Odstraneni mezer
vzorec = Replace(vzorec, " ", "")
For i = 9 To pocet_radku
' Debug.Print i
'Slozeni vzorce
vzorec = "=(INDEX(mat_costs!$A$2:$J$111;POZVYHLEDAT(E" + Str(i) + ";mat_costs!$A$2:$A$111;0);POZVYHLEDAT(G" + Str(i) + ";mat_costs!$A$1:$J$1;1)+1))/specification!$L$1"
'Odstraneni mezer
vzorec = Replace(vzorec, " ", "")
...

[VOM]

Zhavaruje to na:

'Vlozeni hodnoty misto vzorce
Worksheets("specification").Cells(i, 9).Value = Round(Worksheets("specification").Cells(i, 9).Value, 2)

[Zivan]

Aha, prehledl jsem, ze do toho vzorce vkladas "Str(i)", takze to dat pred cyklus nepujde.

Proč je tam to odstraneni mezer? Na prvni pohled i prijde, ze tam zadne nemas.

[VOM]

když vyřadím:
vzorec = Replace(vzorec, " ", "")
tak to zhavaruje na stejném místě jak uvedeno výše

[lubo.]

Tohle je prkotina. Existuje notace R1C1. Stačí vzorce vytvořit jednou. Někde je to i použito.

Add. podrobněji:
Nemohu, kód je hodně nečitelný, velká část kódu je zakomentována něco se ale zřejmě zalomilo na nový řádek a luštit, co to má dělat se mi nechce.

Nicméně, odkazovat se na buňku celou cestou je hodně neefektivní.

'---------------------------------------
Worksheets("specification").Cells(i, 9).Value = Round(Worksheets("specification").Cells(i, 9).Value, 2)

'---------------------------------------
' nebo

with Worksheets("specification").Cells(i, 9)
.Value = Round(.Value, 2)
end with

'---------------------------------------
'nebo
dim w as worksheet
set w = Worksheets("specification")

...
with w.Cells(i, 9)
.Value = Round(.Value, 2)
end with

co třeba vyplnit vzorce v bloku najednou, hodnoty nahradit najednou, pracovat s daty v paměti, ...

Přepnutí na manuální přepočet je jistě základ, ale v makru vkládáš do buňky vzorec a hned z něho vracíš do buňky hodnotu ...? Možná to funguje, ale nepovažuji to za dobrý nápad.

[VOM]

díky
použití with ... nemá měřitelný vliv na dobu
použití dim w as worksheet ... zkrátilo čas z cca 150s na cca 90s

to vložení vzorce a vrácení hodnoty jsem si kontroloval a zatím to funguje správně, ale asi to změním

[VOM]

Pěkně zdravím¨
Po tom co jsem zrušit vložení vzorce a vrácení hodnoty (viz níže druhý řádek) se k mému překvapení čas prodloužil z 90s na cca 200s.

vzorec = "=(INDEX(mat_costs!$A$2:$J$111;POZVYHLEDAT(E" + Str(i) + ";mat_costs!$A$2:$A$111;0);POZVYHLEDAT(G" + Str(i) + ";mat_costs!$A$1:$J$1;1)+1))/specification!$L$1"

' vzorec = "=(INDEX(mat_costs!$r$2:$z$111;POZVYHLEDAT(E" + Str(i) + ";mat_costs!$r$2:$r$111;0);POZVYHLEDAT(G" + Str(i) + ";mat_costs!$r$1:$z$1;1)+1))"

To malé vytížení procesoru je způsobeno tím, že VBA pracuje jen s jedním jádrem?

[elninoslov]

Pošlite súbor, určite sa doberiete riešenia podstatne skôr, ako keď si máme vymýšľať data, ktoré by na to mohli sedieť. Citlivé údaje zmažte, ceny či názvy pozmeňte, o tie nám nejde. Len zachovajte rozmiestnenie, typ, a formáty dát.

[lubo.]

Čtení s porozuměním je těžká úloha.

Zkusím příklad.

Máme dvě makra.

MakroVOM

je zjednodušená ukázka zaslaného makra, tj. upravuje blok po jednotlivých buňkách.

MakroSTD

dělá totéž standardním postupem, tj. upraví blok najednou.

Pusť si obě a porovnej dobu (i třeba čitelnost kódu).

Sub MakroVOM()
Dim i As Long
Dim vzorec As String

  For i = 1 To 100000
    vzorec = "=A" & Str(i) & "+B" & Str(i)
    vzorec = Replace(vzorec, " ", "")
    Worksheets("List1").Cells(i, 9).FormulaLocal = vzorec
  Next
End Sub

Sub MakroSTD()
    Range("C1:C10000").FormulaLocal = "=A1+B1"
End Sub

Tj. nesmyslně napsanému kódu další jádra nepomohou.