Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfere a workbook's all cell's formulas and references to VBA "fingerprint library" ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfere a workbook's all cell's formulas and references to VBA "fingerprint library" ?
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("A1:D1").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:D").AutoFit If blFormulasFound = False Then MsgBox "No Formulas found!" End If Application.StatusBar = False End Sub --- Regards, Norman "Gunnar Johansson" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfere a workbook's all cell's formulas and references to VBA "fingerprint library" ?
Thanks, a great page to start with.
It will however probalbly be easier to use John's approach instead... /Regards "Norman Jones" skrev i meddelandet ... 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("A1:D1").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:D").AutoFit If blFormulasFound = False Then MsgBox "No Formulas found!" End If Application.StatusBar = False End Sub --- Regards, Norman "Gunnar Johansson" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Combining formulas, "and" & "or" to verify content of multiple cel | Excel Discussion (Misc queries) | |||
Trim: Excel 97 gives error "Can't find project or library" | Excel Programming | |||
References.Remove References(1) DOES NOT WORK for "MISSING:" Refs | Excel Programming | |||
"Library not registered" message accessing an HTMLDocument object | Excel Programming |