Transfere a workbook's all cell's formulas and references to VBA "fingerprint library" ?

[Gunnar Johansson]

Hi

(Can you create code with code within VBA, by the way...?)

If you have formulas and links in several worksheets - is there a way - by
code or software - to "convert them to vba" to a kind of "library", a
fingerprint of all formulas mainly to be able to restore the formulas if you
want and when you need. The best is if it create a Sub with a list of all
formulas and their cell references as "library".

I mean if you in cell A1 has = B1 * C1 it should create a sub that give
like:
....
Worksheet1.Range("A1").Cell.Value = "=B1*C1"
....

....or something like that for each cell in the workbook.

I'm not asking "how to program cell formulas in vba, I ask for a routine to
get all links and formulas (values is not of interest) in the workbook.


/Kind regards

 

[Norman Jones]

Hi Gunnar,

(Can you create code with code within VBA, by the way...?)

See Chip Pearson's notes on using VBA to modify VBA components and code
modules:

http://www.cpearson.com/excel/vbe.htm

If you have formulas and links in several worksheets - is there a way - by
code or software - to "convert them to vba" to a kind of "library", a
fingerprint of all formulas mainly to be able to restore the formulas if
you want and when you need. The best is if it create a Sub with a list of
all formulas and their cell references as "library".

You may wish to consider adding a worksheet to list all formulae. The
following is adapted from a routine by John Walkenbach.

Sub ListFormulas()
' // Adapted from John Walkenbach's code to list
' // formulae for all worksheets

Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Long
Dim sh As Worksheet
Dim sStr As String
Dim blSheetCreated As Boolean
Dim blFormulasFound As Boolean

sStr = "Formulas in " & ActiveWorkbook.Name

'delete report sheet if it already exists
Application.DisplayAlerts = False
On Error Resume Next
Sheets(sStr).Delete
On Error GoTo 0
Application.DisplayAlerts = True

Row = 2
For Each sh In ActiveWorkbook.Worksheets
' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = sh.Range("A1").SpecialCells(xlFormulas, 23)
If Not FormulaCells Is Nothing Then blFormulasFound = True
If blFormulasFound Then
' Add a new worksheet
If Not blSheetCreated Then
Application.ScreenUpdating = False
sStr = "Formulas in " & ActiveWorkbook.Name
On Error GoTo 0
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = sStr
blSheetCreated = True

' Set up the column headings
With FormulaSheet
Range("A1") = "Sheet"
Range("B1") = "Address"
Range("C1") = "Formula"
Range("D1") = "Value"
Range("A11").Font.Bold = True
End With
End If

' Process each formula
If sh.Name <> FormulaSheet.Name Then
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / _
FormulaCells.Count, "0%")
With FormulaSheet
Cells(Row, 1) = sh.Name
Cells(Row, 2) = Cell.Address _
(RowAbsolute:=False, _
ColumnAbsolute:=False)
Cells(Row, 3) = " " & Cell.Formula
Cells(Row, 4) = Cell.Value
Row = Row + 1
End With
Next Cell
End If
End If
Next sh
' Adjust column widths
FormulaSheet.Columns("A").AutoFit

If blFormulasFound = False Then
MsgBox "No Formulas found!"
End If

Application.StatusBar = False
End Sub

 

[Gunnar Johansson]

Thanks, a great page to start with.
It will however probalbly be easier to use John's approach instead...

/Regards