iCell Value takes too long

0 votes

I have some straightforward code that iterates over 400 rows. It functions but takes 30 seconds. The iCell is the source of the issue.

stringaDescriptata line value

Can the code be made to run more quickly?

Sub Decr()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim counter As Integer
Dim stringaCriptata As String
Dim stringaDecriptata As String
Dim carattere_da_decriptare As String
Dim carattere_da_sostituire As String
Dim iCell As Range

For Each iCell In Range("A1:BK460")
    stringaDecriptata = ""
    lunghezza = Len(iCell.Value)
    indirizzo = iCell.Address
    
    For counter = 1 To lunghezza
        stringaCriptata = iCell.Value
        carattere_da_decriptare = Mid(iCell.Value, counter, 1)
        carattere_da_sostituire = Chr(Asc(carattere_da_decriptare) - 10)
        
        stringaDecriptata = stringaDecriptata & carattere_da_sostituire
    Next
    
    iCell.Value = stringaDecriptata
Next iCell

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
Mar 27, 2023 in Others by narikkadan
• 63,420 points
276 views

1 answer to this question.

0 votes

Please try the following modified code. The range that needs to be processed is put into an array, and everything is done in memory. Only at the end of the code does it immediately drop the processed result:

Sub Decr()
 Dim sh As Worksheet, i As Long, j As Long, rng As Range, arr As Variant, counter As Integer
 Dim stringaCriptata As String, stringaDecriptata As String, carattere_da_decriptare As String
 Dim carattere_da_sostituire As String, lunghezza As Long

 Set sh = ActiveSheet
 Set rng = sh.Range("A1:BK460")
 arr = rng.Value2  'place the range in an array for faster processing

 For i = 1 To UBound(arr)        'iterate between the array rows
    For j = 1 To UBound(arr, 2)  'iterate between the array columns
         stringaDecriptata = ""
         lunghezza = Len(arr(i, j))
         For counter = 1 To lunghezza
            stringaCriptata = arr(i, j)
            carattere_da_decriptare = Mid(arr(i, j), counter, 1)
            carattere_da_sostituire = Chr(Asc(carattere_da_decriptare) - 10)
            
            stringaDecriptata = stringaDecriptata & carattere_da_sostituire
        Next counter
        arr(i, j) = stringaDecriptata 'place the decrypted string back in the array element
    Next j
 Next i

 'drop the processed array result, at once:
 rng.Value2 = arr
End Sub

It takes time to iterate through each range cell, and it takes considerably longer to put back each processed value individually.

Then, it is wise to develop the practice of declaring all relevant variables. Option Explicit should be placed on top of the module in order to allow VBA to assist you if you forget it.

answered Mar 28, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Parsing a string with GetOpt::Long::GetOptions

Hey, have a look at the section ...READ MORE

answered Nov 15, 2018 in Others by nirvana
• 3,130 points
1,286 views
0 votes
0 answers

Wafcoin: Legalization is the cornerstone for long-term development

Wafcoin token trading platform has always insisted ...READ MORE

Jan 29, 2019 in Others by anonymous
393 views
0 votes
1 answer

Error: missing value where TRUE/FALSE needed

Hello @ nimarah, Your error seems to be in ...READ MORE

answered Aug 24, 2020 in Others by Niroj
• 82,880 points
1,116 views
0 votes
1 answer

How to retrieve the value of a text field in Flutter App?

Hi@akhtar, In your TextField, you can call one ...READ MORE

answered Sep 3, 2020 in Others by MD
• 95,440 points
1,519 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
896 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,212 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
504 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
750 views
0 votes
1 answer

How to query a matrix for multiple values and receive value

Try: =IFERROR(INDEX(SORT(SORT(FILTER(MATRIX, (LENGTHS>length)*(WEIGHTS>weight)),1,1),2,1),1,3),1) , where MATRIX, LENGTHS, and WEIGHTS ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
319 views
0 votes
1 answer

Excel formula to get certain cell if the value is between 2 numbers

So, first with vlookup(): A formula so you ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
719 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP