How to address ' in cell formula? 1

[N1GHTEYES]

It occurred to me it would be usefull to be able to "comment out" cells in a spreadsheet, the same way one can comment lines of code. So I wrote a couple of subs to do it. See below:

Public Sub Comment_out()
Dim rng As Range
Dim mode As Variant
mode = Application.Calculation
Application.Calculation = xlCalculationManual
For Each rng In Selection
    rng.Formula = "'" & rng.Formula
Next rng
Application.Calculation = mode
End Sub

Public Sub Undo_Comment_out()
Dim rng As Range
Dim mode As Variant
mode = Application.Calculation
Application.Calculation = xlCalculationManual
For Each rng In Selection
    If Mid(rng.Formula, 1, 1) = "'" Then rng.Formula = Mid(rng.Formula, 2, Len(rng.Formula - 1))
Next rng
Application.Calculation = mode
End Sub

Commenting out the cells works fine. Unfortunately, the reverse operation does not work, because, even when they are there, apostrophes do not seem to be recognised as part of a cell's formula.

Does anyone know what cell property which actually gives the true contents, including any apostrophes?

Tony

 

[SkipVought]

hi,

dim r as range

for each r in [YourRange]
  with r
    .formula = .formula
  end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

you can also say

dim r as range

for each r in [YourRange]
  with r
    if .value = .formula then
      'not a formula
    else
      'got a formula
    end if
  end with
next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Thanks for the response Skip, but I'm a bit puzzled. How do either of these posts help?

The problems I'm having are:
1 I cannot identify ' as the first character in the formula string because it is not recognised using the line:

If Mid(rng.Formula, 1, 1) = "'" Then rng.Formula = Mid(rng.Formula, 2, Len(rng.Formula - 1))

2 Even if there was some other means of recognizing the presence of ' then I would still not be able to delete it because it does not seem to be recognized by the MID function.

Tony

 

[N1GHTEYES]

OK, so one way to solve the problem is to use _ instead of '.

Mid seems to have no trouble finding _ so it all works OK, at least, it does after fixing the minor coding error in the 2nd sub and tweaking the range definition.

Here is the code if anyone is interested.

Public Sub Comment_out()
Dim rng As Range, validrange As Range
Dim mode As Variant
mode = Application.Calculation
Application.Calculation = xlCalculationManual
Set validrange = Application.Intersect(Selection, ActiveSheet.UsedRange)
For Each rng In validrange
    rng.Formula = "_" & rng.Formula
Next rng
Application.Calculation = mode
End Sub

Public Sub Undo_Comment_out()
Dim rng As Range
Dim mode As Variant
mode = Application.Calculation
Application.Calculation = xlCalculationManual
Set validrange = Application.Intersect(Selection, ActiveSheet.UsedRange)
For Each rng In validrange
    If Mid(rng.Formula, 1, 1) = "_" Then rng.Formula = Mid(rng.Formula, 2, Len(rng.Formula) - 1)
Next rng
Application.Calculation = mode
End Sub

I've fixed a minor typo in the Len() and included using the intersection of the selection with the used range, so if the user is lazy and just selects all the cells on the sheet before calling the uncomment sub, then it only checks the necessary cells, not all of them, which would take ages.

Now that this is working, it actually seems like quite a handy little pair of routines.

I tend to write workbooks which do quite complex analyses and after a while they can grow to be quite slow. Sometimes, you still need the workbook to be able to calculate when you are testing new stuff you are adding, but there are parts which are superfluous to the current cells' calculations. In those circumstances, it will be nice to be able to comment them out temporarily then reinstate them at the touch of a button afterwards (assuming the above macros are assigned to buttons).

Tony

 

[N1GHTEYES]

OK, so in the uncomment sub, the line:

Dim rng As Range

should read:

Dim rng As Range, validrange As Range

Tony

 

[SkipVought]

I do not understand your handstands and back flips when this simply identifies when you have a formula as text

Dim r As Range

For Each r In [YourRange]
  With r
    If .Value = .Formula Then
      'not a formula
      '[b][highlight]undo formula showing as formula!!!
      .Formula = .Formula   '[/highlight][/b]
    Else
      'got a formula
    End If
  End With
Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Skip,

You are of course correct. Have a well-deserved star.

I could not see what the line:

.formula = .formula

was meant to be doing.

I was assuming that I had to actually identify the presence of a ' and actively remove it, whereas the .Formula = .Formula method gets rid of it implicitly. Too cute for my pedestrian brain to pick up.

In fact, your idea is even better than you've given it credit for. Performing .Formual=.Formula on cells which have not been commented out does not seem to have any effect, so I don't need the individual checks. I can simply replace the whole of the for loop with:

validrange.formula = validrange.formula

I've checked it and it works.

BTW what do you think of the idea? It seems quite handy to me to be able to easily and reversibly "comment out" cells.

Tony

 

[SkipVought]

In fact, that is how I tested the procedure, applying it to cells with and without formulas, literal strings and literal numbers

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

So, do you think it looks like a useful technique?

Or are you going to tell me now that Excel already has an innate means of doing it, which I was just not aware of? <grin>

Tony

 

[SkipVought]

No. Only inseven. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

chuckles quietly...

 

[SkipVought]

I take full irresponsibility for your levitation.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

I refuse to rise to that.