Capture a worksheet in VBA

[Madiya]

Hi all,
I have a huge worksheet full of sumproduct formulas which fatches links
from various other files and creats summary reports.I do not want to
reveal formulas I have used which will be used against me (directly or
indirectly). Manually creating VBA code for the same for more then 300
formulas is quite tedius.

Is there a way by which I can capture the status of the whole sheet
(all formulas and links) in VBA which can be used later to creat the
sheet again when required?

Regards,
Madiya

 

[Bob Phillips]

Just use the same formulae in an Evaluate statement. Slow and inefficient,
but should work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Madiya]

Thank you for your reply.
But this essancially means that I write Evaluate statement for each of
these 300+ cells which is very tedius. That's the reason I was
requesting for some automated VBA code
which will pick up formula in each cell and when I run the code, it
will put the same formula in the same cell.

Regards,
Madiya

 

[Peter T]

Hi Madiya,

Why not simply hide your formulas.

- Select all cells on the sheet (square that intersects row.column headers)
- Format > Cells > Protection > uncheck locked and uncheck hidden
- F5 > Special > formulas
- With your formulas selected -
- Format > Cells > Protection > check locked and check hidden
- Right click sheet tab > view code > Properties > Enable selection
- Tools > Protection > Protect sheet

curiosity, why might your formulas be used against you !

Regards,
Peter T

 

[Madiya]

Hi Peter,
Right question....>>>>>> Why not simply hide your formulas.
It means that I will have to protect the sheet and after that I am not
free to modify any thing at will. Each time I will have to unprotect
the same which is a hinderance in working.
As regards to your curiosity, at present I am enjoying the freedom for
my saved time.
Once it is known to others, they will surely burden me with much more
additional work which may not be possible to automate(we can only
automate repitative tasks )

And if I do not get any solution here in NG, I will have to write code
for all 300+ cell formulas.

Regards,
Madiya.

 

[Bob Phillips]

A code writer

Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula Then
Debug.Print "Range(""" & cell.Address & _
""").Value = Evaluate(""" & Replace(cell.Formula, """",
"""""") & """)"
End If
Next cell


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Peter T]

How about a simple macro to protect / unprotect. If the only user on your
system is you this could run automatically on open by detecting username,
and protect in the save event. Or some other quick access to the macro known
only to you.

Whatever way you chose to write those cell formulas in VBA is going to
involve not only initial work but on-going to update for renamed links.
Also you say you want to carry on modifying things which I assume means the
formulas (only the formulas are protected the way I outlined). I would have
though a lot more work than un-protect / protect.

Once you've found a way to hide them don't be surprised if you lose yet more
of your freedom from the inquisitive pestering you to know how you did it
and to do similar for them <g>

Regards,
Peter T