Speeding Up VB code ?

Question

Speeding Up VB code ?

Anonymous

I have a dashboard Image above, it uses VB code and also some formulas in certain cells, at the moment when i use the buttons the formulas update almost immediately but the VB code takes 2 or 3 seconds to fully load up and change the graphs, which run off of VB code cells.

I was wondering if there is anyway of redoing the VB code to have it load the information quicker. I am not good with VB code and the basic code i am using was kindly given to me by Anna on this board, Ive listed the VB code for both buttons below in the hope that someone could assist in helping me take it from what i have to something that works faster. tx

Sub MoveForward()

'Declare variables

Dim wsMain As Worksheet

Dim wsDash As Worksheet

Dim i As Long

Dim lastRow As Long

Dim currentRow As Long

'Set worksheets

Set wsMain = Worksheets("Main")

Set wsDash = Worksheets("Dashboard")

'Find last row on main sheet

lastRow = wsMain.Cells(wsMain.Rows.Count, "A").End(xlUp).Row

'Find current row on dashboard sheet

currentRow = wsDash.Range("B2").Value

'Loop through rows on main sheet until next row with 0 is found or end of data is reached

For i = currentRow + 1 To lastRow

If wsMain.Cells(i, "A").Value = "0" Then

'Copy data from main sheet to dashboard sheet

    wsDash.Range("B2").Value = i 

    'Store row number in cell C1 

    wsDash.Range("C2").Value = wsMain.Cells(i, "B").Value 

    'Copy data from column B 

    wsDash.Range("D2").Value = wsMain.Cells(i, "C").Value 

    'Copy data from column C 

    wsDash.Range("E2").Value = wsMain.Cells(i, "D").Value 

    'Copy data from column D 

    wsDash.Range("F2").Value = wsMain.Cells(i, "E").Value 

    'Copy data from column E 

    wsDash.Range("Q4").Value = wsMain.Cells(i, "AG").Value 

    'Copy data from column AG 

    wsDash.Range("R4").Value = wsMain.Cells(i, "AH").Value 

    'Copy data from column AH 

    wsDash.Range("S4").Value = wsMain.Cells(i, "AI").Value 

    'Copy data from column AI 

    wsDash.Range("V4").Value = wsMain.Cells(i, "AJ").Value 

    'Copy data from column AJ 

    wsDash.Range("U4").Value = wsMain.Cells(i, "AK").Value 

    'Copy data from column AK 

    wsDash.Range("T4").Value = wsMain.Cells(i, "AL").Value 

    'Copy data from column AL 

    wsDash.Range("M3").Value = wsMain.Cells(i, "AC").Value 

    'Copy data from column AC 

    wsDash.Range("N3").Value = wsMain.Cells(i, "AD").Value 

    'Copy data from column AD 

    wsDash.Range("O3").Value = wsMain.Cells(i, "AE").Value 

    'Copy data from column AE 

    wsDash.Range("P3").Value = wsMain.Cells(i, "AF").Value 

    'Copy data from column AF 

    wsDash.Range("G3").Value = wsMain.Cells(i, "AM").Value 

    'Copy data from column AM 

    wsDash.Range("H3").Value = wsMain.Cells(i, "AN").Value 

    'Copy data from column AN 

    wsDash.Range("I3").Value = wsMain.Cells(i, "AO").Value 

    'Copy data from column AO 

    wsDash.Range("J3").Value = wsMain.Cells(i, "AP").Value 

    'Copy data from column AP 

    wsDash.Range("K3").Value = wsMain.Cells(i, "AQ").Value 

    'Copy data from column AQ 

     wsDash.Range("G19").Value = wsMain.Cells(i, "M").Value 

    'Copy data from column M 

     wsDash.Range("K19").Value = wsMain.Cells(i, "O").Value 

    'Copy data from column O 

     wsDash.Range("I19").Value = wsMain.Cells(i, "Q").Value 

    'Copy data from column Q 

     wsDash.Range("M19").Value = wsMain.Cells(i, "S").Value 

    'Copy data from column S 

     wsDash.Range("O19").Value = wsMain.Cells(i, "V").Value 

    'Copy data from column V 

    wsDash.Range("Q7").Value = wsMain.Cells(i, "BB").Value 

    'Copy data from column BB 

    wsDash.Range("S7").Value = wsMain.Cells(i, "BC").Value 

    'Copy data from column BC 

    wsDash.Range("U7").Value = wsMain.Cells(i, "BD").Value 

    'Copy data from column BD 

    wsDash.Range("Q10").Value = wsMain.Cells(i, "BE").Value 

    'Copy data from column BE 

    wsDash.Range("S10").Value = wsMain.Cells(i, "BF").Value 

    'Copy data from column BF 

    wsDash.Range("U10").Value = wsMain.Cells(i, "BG").Value 

    'Copy data from column BG 

    wsDash.Range("Q13").Value = wsMain.Cells(i, "BH").Value 

    'Copy data from column BH 

    wsDash.Range("S13").Value = wsMain.Cells(i, "BI").Value 

    'Copy data from column BI 

    wsDash.Range("U13").Value = wsMain.Cells(i, "BJ").Value 

    'Copy data from column BJ 

    wsDash.Range("Q19").Value = wsMain.Cells(i, "AU").Value 

    'Copy data from column AU 

    wsDash.Range("S19").Value = wsMain.Cells(i, "AV").Value 

    'Copy data from column AV 

    wsDash.Range("U19").Value = wsMain.Cells(i, "AW").Value 

    'Copy data from column AW 

    wsDash.Range("Q21").Value = wsMain.Cells(i, "CO").Value 

    'Copy data from column CO 

    wsDash.Range("S21").Value = wsMain.Cells(i, "CS").Value 

    'Copy data from column CS 

    wsDash.Range("U21").Value = wsMain.Cells(i, "CU").Value 

    'Copy data from column CU 

    wsDash.Range("W3").Value = wsMain.Cells(i, "CB").Value 

    'Copy data from column CB 

    wsDash.Range("X3").Value = wsMain.Cells(i, "CC").Value 

    'Copy data from column CC 

    wsDash.Range("Y3").Value = wsMain.Cells(i, "CD").Value 

    'Copy data from column CD 

    wsDash.Range("Z3").Value = wsMain.Cells(i, "CE").Value 

    'Copy data from column CE 

    wsDash.Range("W6").Value = wsMain.Cells(i, "CF").Value 

    'Copy data from column CF 

    wsDash.Range("X6").Value = wsMain.Cells(i, "CJ").Value 

    'Copy data from column CJ 

    wsDash.Range("Y6").Value = wsMain.Cells(i, "CM").Value 

    'Copy data from column CM 

    wsDash.Range("Z6").Value = wsMain.Cells(i, "CN").Value 

    'Copy data from column CN 

    wsDash.Range("X9").Value = wsMain.Cells(i, "EJ").Value 

    'Copy data from column EJ 

    wsDash.Range("X10").Value = wsMain.Cells(i, "EK").Value 

    'Copy data from column EK 

    wsDash.Range("X11").Value = wsMain.Cells(i, "EL").Value 

    'Copy data from column EL 

    wsDash.Range("X12").Value = wsMain.Cells(i, "EM").Value 

    'Copy data from column EM 

    wsDash.Range("X13").Value = wsMain.Cells(i, "EN").Value 

    'Copy data from column EN 

    wsDash.Range("X14").Value = wsMain.Cells(i, "EC").Value 

    'Copy data from column EC 

    wsDash.Range("X15").Value = wsMain.Cells(i, "ED").Value 

    'Copy data from column ED 

    wsDash.Range("X16").Value = wsMain.Cells(i, "EE").Value 

    'Copy data from column EE 

    wsDash.Range("X17").Value = wsMain.Cells(i, "EF").Value 

    'Copy data from column EF 

    wsDash.Range("X18").Value = wsMain.Cells(i, "EG").Value 

    'Copy data from column EG 

    wsDash.Range("Z9").Value = wsMain.Cells(i, "EH").Value 

    'Copy data from column EH 

    wsDash.Range("Z10").Value = wsMain.Cells(i, "EI").Value 

    'Copy data from column EI 

    wsDash.Range("Z11").Value = wsMain.Cells(i, "EO").Value 

    'Copy data from column EO 

    wsDash.Range("Z12").Value = wsMain.Cells(i, "EP").Value 

    'Copy data from column EP 

    wsDash.Range("Z13").Value = wsMain.Cells(i, "EQ").Value 

    'Copy data from column EQ 

    wsDash.Range("Z14").Value = wsMain.Cells(i, "ER").Value 

    'Copy data from column ER 

    wsDash.Range("Z15").Value = wsMain.Cells(i, "ES").Value 

    'Copy data from column ES 

    wsDash.Range("Z16").Value = wsMain.Cells(i, "ET").Value 

    'Copy data from column ET 

    wsDash.Range("Z17").Value = wsMain.Cells(i, "EU").Value 

    'Copy data from column EU 

    wsDash.Range("Q16").Value = wsMain.Cells(i, "Y").Value 

    'Copy data from column Y 

    wsDash.Range("S16").Value = wsMain.Cells(i, "Z").Value 

    'Copy data from column Z 

    wsDash.Range("U16").Value = wsMain.Cells(i, "AA").Value 

    'Copy data from column AA 

    wsDash.Range("R16").Value = wsMain.Cells(i, "EV").Value 

    'Copy data from column EV 

    wsDash.Range("T16").Value = wsMain.Cells(i, "EW").Value 

    'Copy data from column EW 

    wsDash.Range("V16").Value = wsMain.Cells(i, "EX").Value 

    'Copy data from column EX

If wsMain.Cells(i, "U").Value <> "" Then

wsDash.Range("M21").Value = wsMain.Cells(i, "U").Value + 1

Else

wsDash.Range("M21").Value = ""

End If

If wsMain.Cells(i, "X").Value <> "" Then

wsDash.Range("O21").Value = wsMain.Cells(i, "X").Value + 1

Else

wsDash.Range("O21").Value = ""

End If

    'Repeat for other columns as needed

'Exit loop

    Exit For 

End If

Next i

End Sub

Sub MoveBackward()

'Declare variables

Dim wsMain As Worksheet

Dim wsDash As Worksheet

Dim i As Long

Dim lastRow As Long

Dim currentRow As Long

'Set worksheets

Set wsMain = Worksheets("Main")

Set wsDash = Worksheets("Dashboard")

'Find last row on main sheet

lastRow = wsMain.Cells(wsMain.Rows.Count, "A").End(xlUp).Row

'Find current row on dashboard sheet

currentRow = wsDash.Range("B2").Value

'Loop through rows on main sheet until next row with 0 is found or end of data is reached

For i = currentRow - 1 To 1 Step -1

If wsMain.Cells(i, "A").Value = "0" Then

'Copy data from main sheet to dashboard sheet

    wsDash.Range("B2").Value = i 

    'Store row number in cell C1 

    wsDash.Range("C2").Value = wsMain.Cells(i, "B").Value 

    'Copy data from column B 

    wsDash.Range("D2").Value = wsMain.Cells(i, "C").Value 

    'Copy data from column C 

    wsDash.Range("E2").Value = wsMain.Cells(i, "D").Value 

    'Copy data from column D 

    wsDash.Range("F2").Value = wsMain.Cells(i, "E").Value 

    'Copy data from column E 

    wsDash.Range("Q4").Value = wsMain.Cells(i, "AG").Value 

    'Copy data from column AG 

    wsDash.Range("R4").Value = wsMain.Cells(i, "AH").Value 

    'Copy data from column AH 

    wsDash.Range("S4").Value = wsMain.Cells(i, "AI").Value 

    'Copy data from column AI 

    wsDash.Range("V4").Value = wsMain.Cells(i, "AJ").Value 

    'Copy data from column AJ 

    wsDash.Range("U4").Value = wsMain.Cells(i, "AK").Value 

    'Copy data from column AK 

    wsDash.Range("T4").Value = wsMain.Cells(i, "AL").Value 

    'Copy data from column AL 

    wsDash.Range("M3").Value = wsMain.Cells(i, "AC").Value 

    'Copy data from column AC 

    wsDash.Range("N3").Value = wsMain.Cells(i, "AD").Value 

    'Copy data from column AD 

    wsDash.Range("O3").Value = wsMain.Cells(i, "AE").Value 

    'Copy data from column AE 

    wsDash.Range("P3").Value = wsMain.Cells(i, "AF").Value 

    'Copy data from column AF 

     wsDash.Range("G3").Value = wsMain.Cells(i, "AM").Value 

    'Copy data from column AM 

    wsDash.Range("H3").Value = wsMain.Cells(i, "AN").Value 

    'Copy data from column AN 

    wsDash.Range("I3").Value = wsMain.Cells(i, "AO").Value 

    'Copy data from column AO 

    wsDash.Range("J3").Value = wsMain.Cells(i, "AP").Value 

    'Copy data from column AP 

    wsDash.Range("K3").Value = wsMain.Cells(i, "AQ").Value 

    'Copy data from column AQ 

     wsDash.Range("G19").Value = wsMain.Cells(i, "M").Value 

    'Copy data from column M 

     wsDash.Range("K19").Value = wsMain.Cells(i, "O").Value 

    'Copy data from column O 

     wsDash.Range("I19").Value = wsMain.Cells(i, "Q").Value 

    'Copy data from column Q 

     wsDash.Range("M19").Value = wsMain.Cells(i, "S").Value 

    'Copy data from column S 

     wsDash.Range("O19").Value = wsMain.Cells(i, "V").Value 

    'Copy data from column V 

    wsDash.Range("Q7").Value = wsMain.Cells(i, "BB").Value 

    'Copy data from column BB 

    wsDash.Range("S7").Value = wsMain.Cells(i, "BC").Value 

    'Copy data from column BC 

    wsDash.Range("U7").Value = wsMain.Cells(i, "BD").Value 

    'Copy data from column BD 

    wsDash.Range("Q10").Value = wsMain.Cells(i, "BE").Value 

    'Copy data from column BE 

    wsDash.Range("S10").Value = wsMain.Cells(i, "BF").Value 

    'Copy data from column BF 

    wsDash.Range("U10").Value = wsMain.Cells(i, "BG").Value 

    'Copy data from column BG 

    wsDash.Range("Q13").Value = wsMain.Cells(i, "BH").Value 

    'Copy data from column BH 

    wsDash.Range("S13").Value = wsMain.Cells(i, "BI").Value 

    'Copy data from column BI 

    wsDash.Range("U13").Value = wsMain.Cells(i, "BJ").Value 

    'Copy data from column BJ 

    wsDash.Range("Q19").Value = wsMain.Cells(i, "AU").Value 

    'Copy data from column AU 

    wsDash.Range("S19").Value = wsMain.Cells(i, "AV").Value 

    'Copy data from column AV 

    wsDash.Range("U19").Value = wsMain.Cells(i, "AW").Value 

    'Copy data from column AW 

    wsDash.Range("Q21").Value = wsMain.Cells(i, "CO").Value 

    'Copy data from column CO 

    wsDash.Range("S21").Value = wsMain.Cells(i, "CS").Value 

    'Copy data from column CS 

    wsDash.Range("U21").Value = wsMain.Cells(i, "CU").Value 

    'Copy data from column CU 

    wsDash.Range("W3").Value = wsMain.Cells(i, "CB").Value 

    'Copy data from column CB 

    wsDash.Range("X3").Value = wsMain.Cells(i, "CC").Value 

    'Copy data from column CC 

    wsDash.Range("Y3").Value = wsMain.Cells(i, "CD").Value 

    'Copy data from column CD 

    wsDash.Range("Z3").Value = wsMain.Cells(i, "CE").Value 

    'Copy data from column CE 

    wsDash.Range("W6").Value = wsMain.Cells(i, "CF").Value 

    'Copy data from column CF 

    wsDash.Range("X6").Value = wsMain.Cells(i, "CJ").Value 

    'Copy data from column CJ 

    wsDash.Range("Y6").Value = wsMain.Cells(i, "CM").Value 

    'Copy data from column CM 

    wsDash.Range("Z6").Value = wsMain.Cells(i, "CN").Value 

    'Copy data from column CN 

    wsDash.Range("X9").Value = wsMain.Cells(i, "EJ").Value 

    'Copy data from column EJ 

    wsDash.Range("X10").Value = wsMain.Cells(i, "EK").Value 

    'Copy data from column EK 

    wsDash.Range("X11").Value = wsMain.Cells(i, "EL").Value 

    'Copy data from column EL 

    wsDash.Range("X12").Value = wsMain.Cells(i, "EM").Value 

    'Copy data from column EM 

    wsDash.Range("X13").Value = wsMain.Cells(i, "EN").Value 

    'Copy data from column EN 

    wsDash.Range("X14").Value = wsMain.Cells(i, "EC").Value 

    'Copy data from column EC 

    wsDash.Range("X15").Value = wsMain.Cells(i, "ED").Value 

    'Copy data from column ED 

    wsDash.Range("X16").Value = wsMain.Cells(i, "EE").Value 

    'Copy data from column EE 

    wsDash.Range("X17").Value = wsMain.Cells(i, "EF").Value 

    'Copy data from column EF 

    wsDash.Range("X18").Value = wsMain.Cells(i, "EG").Value 

    'Copy data from column EG 

    wsDash.Range("Z9").Value = wsMain.Cells(i, "EH").Value 

    'Copy data from column EH 

    wsDash.Range("Z10").Value = wsMain.Cells(i, "EI").Value 

    'Copy data from column EI 

    wsDash.Range("Z11").Value = wsMain.Cells(i, "EO").Value 

    'Copy data from column EO 

    wsDash.Range("Z12").Value = wsMain.Cells(i, "EP").Value 

    'Copy data from column EP 

    wsDash.Range("Z13").Value = wsMain.Cells(i, "EQ").Value 

    'Copy data from column EQ 

    wsDash.Range("Z14").Value = wsMain.Cells(i, "ER").Value 

    'Copy data from column ER 

    wsDash.Range("Z15").Value = wsMain.Cells(i, "ES").Value 

    'Copy data from column ES 

    wsDash.Range("Z16").Value = wsMain.Cells(i, "ET").Value 

    'Copy data from column ET 

    wsDash.Range("Z17").Value = wsMain.Cells(i, "EU").Value 

    'Copy data from column EU 

    wsDash.Range("Q16").Value = wsMain.Cells(i, "Y").Value 

    'Copy data from column Y 

    wsDash.Range("S16").Value = wsMain.Cells(i, "Z").Value 

    'Copy data from column Z 

    wsDash.Range("U16").Value = wsMain.Cells(i, "AA").Value 

    'Copy data from column AA 

    wsDash.Range("R16").Value = wsMain.Cells(i, "EV").Value 

    'Copy data from column EV 

    wsDash.Range("T16").Value = wsMain.Cells(i, "EW").Value 

    'Copy data from column EW 

    wsDash.Range("V16").Value = wsMain.Cells(i, "EX").Value 

    'Copy data from column EX

If wsMain.Cells(i, "U").Value <> "" Then

wsDash.Range("M21").Value = wsMain.Cells(i, "U").Value + 1

Else

wsDash.Range("M21").Value = ""

End If

If wsMain.Cells(i, "X").Value <> "" Then

wsDash.Range("O21").Value = wsMain.Cells(i, "X").Value + 1

Else

wsDash.Range("O21").Value = ""

End If

    'Repeat for other columns as needed

'Exit loop

    Exit For 

End If

Next i

End Sub

As you can see its a bit long hand, i thought perhaps using ranges would work but im not sufficiently experienced at VB to do it

Thanks

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

7 answers

Answer 1

Anonymous

Hi M8, all the current code is above, it feeds into the dashboard in the picture

0 comments

Answer 2

How about this?

https://learn.microsoft.com/en-us/office/vba/api/excel.range.copyfromrecordset

Dim StrSQL$, Cn As Object, Sht As Worksheet

Set Cn = CreateObject("ADODB.connection")

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName

Answer 3

Anonymous

Can you share some data and expected result?

0 comments

Answer 4

Anonymous

Hi Adeyemi

As i don't know much on VB code, I'm sorry but this is way over my head, was hoping for a restructure of the code above to speed it up a bit, tx anyway.

0 comments

Answer 5

Hello

I’m Adeyemi and I’d be happy to help you with your question.

I understand you're looking for ways to speed up your VB code. Here are some tips that might help:

Turn off unnecessary features in VBA: One of the first things to do when speeding up VBA code is to turn off unnecessary features such as animations, screen updating, automatic calculations, and events while your macro is running
Use variables instead of controls to hold your data. Using TextBoxes or other controls to hold your data will make it much slower. Use variables instead
Use the TextFieldParser class: There is a TextFieldParser class available that might help improve the speed of your code
Optimize your loops: If you're using loops in your code, make sure they're optimized. For example, if you're searching for a specific SKU in a list, consider using a more efficient search algorithm or data structure.
Use the smallest viable data type size: Declare Variables with the smallest viable data type size to reduce memory usage.
Use vbNullString instead of “”: Use vbNullString instead of “” as it's faster and doesn't take up any memory

Remember, optimizing code often involves trade-offs between readability, maintainability, and performance. It's important to strike a balance that works best for your specific situation. I hope this helps!

Here are some references: https://stackoverflow.com/questions/21356252/how-can-i-speed-up-my-visual-basic-application. https://eident.co.uk/2016/03/top-ten-tips-to-speed-up-your-vba-code/. Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.'

I hope this helps

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below

Regards Adeyemi

Share via

Speeding Up VB code ?

7 answers