PDA

View Full Version : Question about efficiency



Moragtao
07-05-2017, 10:09 AM
Currently, a lot of the macros I run in Excel require the same thing to do be done to multiple cells. For example, the loop below simply adds an L and a space to the beginning of everything in a particular column.



Do Until ActiveCell.Value = ""
ActiveCell.Value = "L " & ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop


What I'm wondering is, is there a more efficient way for me to go about doing this? I know selecting a cell isn't ideal, but this is the best way I currently know to complete the given task.

CROSSPOSTED:
https://www.excelforum.com/excel-programming-vba-macros/1191740-question-about-efficiency.html

mdmackillop
07-05-2017, 11:16 AM
Sub Test()
Dim cel As Range
For Each cel In Range("L:L").SpecialCells(2)
cel.Value = "L " & cel.Value
Next cel
End Sub

Paul_Hossler
07-05-2017, 04:21 PM
This approach can be a little finicky since you have to build the formula string

1. with the double quotes inside a VBA string to represent a 'quote as an embedded character'

2. and there are some 'empty string' double quotes in the formula

So I usually make it easier for me by using the Q = Chr(34) method

I like it since there's no looping required





Option Explicit
Sub test()
Dim sFormula As String, Q As String, QQ As String

Q = Chr(34)
QQ = Chr(34) & Chr(34)

With ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants)
s = "IF(" & .Address & "<>" & QQ & "," & _
Q & "L " & Q & "&" & .Address & " ," & _
QQ & ")"
.Value = Application.Evaluate(s)
End With

End Sub





The .Evaluate method I learned about here a long time ago, but I forgot the originator

mdmackillop
07-06-2017, 03:11 AM
Hi Paul
You have a problem if there is a blank cell in the data; however over 10k rows, your is 8 times quicker. If I ever find a nut and need a sledgehammer, I know who to ask. :devil2:
Regards
Malcolm

Paul_Hossler
07-06-2017, 08:14 AM
I made an assumption (from #1) that there were no empty cells (you'd think I'd know better)

This is a more robust approach






Option Explicit
Sub test()
Dim sFormula As String, Q As String, QQ As String
Dim rArea As Range

Q = Chr(34)
QQ = Chr(34) & Chr(34)

For Each rArea In ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants).Areas
With rArea
sFormula = "IF(" & .Address & "<>" & QQ & "," & _
Q & "L " & Q & "&" & .Address & " ," & _
QQ & ")"
.Value = Application.Evaluate(sFormula)
End With
Next

End Sub



Edit: For some reason, even with Option Explicit, using the 's' variable was not flagged as an error, even though sFormula was Dim-ed

I've see that happen a few times, but have not figured out why

mdmackillop
07-06-2017, 08:32 AM
Edit: For some reason, even with Option Explicit, using the 's' variable was not flagged as an error, even though sFormula was Dim-ed
It errors for me. Have you "s" as a Public variable/constant elsewhere?

Paul_Hossler
07-06-2017, 11:53 AM
19687

1. If I select the 's' and right-click for Definition, it throws the error box. But it's still run-able and Alt-d compliable

2. However, after running Rob Bovey's code cleaner, everything works as normal

I've never had a short macro with such a little bit of editing get confused.

It was a head scratcher, but thanks for confirming that it shouldn't work