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,600 points
559 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,000 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,668 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
676 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,840 points
1,423 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,460 points
1,810 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
1,240 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,688 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
948 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,690 points
1,079 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,000 points
638 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,000 points
1,119 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