Nothing wrong with what you've done at all. Most people would chalk the
differences up to programming 'style'. I'm kind of a belt-and-suspenders
coder and like to keep 'control' of what's going on in the code, too many
years doing assembly language coding on life-critical systems, I suppose.
You have an advantage in knowing what your data looks like and how your
sheets are laid out. We here in these forums don't usually have that luxury,
so you can get away with less error testing and "what if they have this
situation?" checking. Initially I didn't catch on to the fact that you were
providing printed copy only and thought you were really concerned with
possibility of someone uncovering the real numbers and possibly even formulas
in your workbook, so that's where converting formulas to values got started.
Rather than un-write that, I just left it as was, since in the end even your
method is 'destructive' in that you cannot recover the original contents of
the cells.
I also try to write code here that's pretty straight-forward and may even
put in a couple of steps to show all of what's happening to the person
requesting help. Keeping it simple (but longer) makes it more likely that
they will be able to maintain it on their own if they have any
VB knowledge
at all. If I were going to be critical of anything you wrote, I'd recommend
changing the name of variable 'val' There is a
VB function VAL() that
returns the numeric value of a string parameter. That kind of thing is risky
at least, and potentially confusing or even fault-causing under the right
circumstances.
I'll also offer a recommendation, but I think you're probably already using
it:
Option Explicit
This statement, at the beginning of the declarations section of a module,
says that all variables/constants must be declared/defined before they are
used. This was a big step forward in (V)Basic programming - it offers a
method of helping you prevent logic errors through simple typo's of
variable/constant names, and if you'll put all your variables/constants at
the beginning of a routine or in their appropriate location (as Public Const
in declarations of one module) it also affords you some code maintenance and
management assistance.
Glad we didn't need to go much deeper or give consideration to performance.
I was concerned because a couple of things I've worked on through these
forums lately have turned out to be very processing intensive - I have one
that munches through around 1500 rows of data doing a lot of data
manipulation that takes around 5 minutes to complete, on average, and on a
fast dual-core system. Had another from another site that uncovered some
issues in Excel charting - I thought the fact we were building 51 charts with
8800 data points each was the reason it was taking 11-12 minutes in Excel
2007 --- but other issues pushed me back to Excel 2003 where it only took
about 1m40s for same job (charting engine differences). And I know these
days people are kind of used to, or expect to see results instantaneously and
a 3, 5 or 15 minute job is just a nightmare to them for some reason. I say
"it takes as long as it takes" - enjoy the break, hit the coffee pot or water
cooler, say Hi to your buddy in the next cubicle while waiting <g.
" wrote:
On Mar 11, 1:31 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
anyText = anyCell.Text
Yes, that is the key. Thanks. That was what I thought I should use,
but I misread the Help page and did not even test my intuition. Argh!
By the way, below is my final version. It worked in a blink of an eye
-- although efficiency was never my concern. My rendition is clearly
simpler than yours. Perhaps you could offer a critical review to
explain any oversights due to my simplification.
Thanks again. Your guidance is much appreciated.
It fairly well preserves the format and layout except when the Accounting
format has been chosen (that puts leading spaces between the $ and 1st digit
My rendition of the macro has no trouble preserving the Accounting
spacing without extra effort. I am using Office Excel 2003.
PS: I am a little surprised that the macro works for negative
numbers. All of my cells are formatted as Number with comma separator
and some number of decimal places (varies). When I manually type -
xxx,xxx into a cell, I get a #NAME! error. I have to prefix it with
an apostrophe. Yet the macro has no problem changing -123,456 to -
xxx,xxx. Go figure!
Sub Xover()
Dim cell As Object
Dim val As String
Dim i As Integer
Dim n As String
Application.ScreenUpdating = False
For Each cell In Selection
If IsNumeric(cell.Value) Then
val = cell.Text
For i = 1 To Len(val)
n = Mid(val, i, 1)
If "0" <= n And n <= "9" Then
Mid(val, i, 1) = "x"
End If
Next
cell.Formula = val
End If
Next
Application.ScreenUpdating = True
End Sub