生成所有excel单元格公式的平面列表 [英] Generate a flat list of all excel cell formulas

问题描述

我有一个用VBA和单元格公式编写的大量程序。我的任务是将其逆向工程到C＃winforms。我想一开始，我需要在平面列表中看到所有的单元格公式。

任何现有的方法来做？感谢提前！

编辑：只要分享，在回答者的帮助下，我设法提出了这一点：

解决方案

在VBA（轻松修改为vbscript）中，您可以快速将所有表格中的所有公式转储到一个平面的txt文件（更改您的路径适合）与高效的变体数组。 来自我的文章的代码

  const sFilePath =C：\test\myfile.txt
 
 Sub CreateTxt_Output（）
 Dim ws As Worksheet 
 Dim rng1 As Range 
 Dim X 
 Dim lRow As Long 
 Dim lCol As Long 
 Dim strTmp As String 
 Dim lFnum As Long 
 
 lFnum = FreeFile 
打开sFilePath For输出为lFnum 
 
对于每个ws在ActiveWorkbook.Worksheets 
打印#lFnum，*****& ws.Name& *****
'测试该表已被使用
设置rng1 = ws.UsedRange 
如果不是rng1是没有
'只有多单元格范围可以写入2D数组
如果rng1.Cells.Count> 1然后
 X = ws.UsedRange.Formula 
对于lRow = 1对于UBound（X，1）
对于lCol = 1对于UBound（X，2）
'行到txt文件
打印#lFnum，X（lRow，lCol）
下一页lCol 
下一页lRow 
 Else 
打印#lFnum，rng1.Formula 
结束如果
结束如果
下一个ws 
 
关闭lFnum 
 MsgBoxDone！，vbOKOnly 
 End Sub 
  

[更新部分 - 您可以使用SpecialCells在VBA中快速隔离公式。如果工作表中没有公式，则需要错误处理，请参阅下面的GetFormula

  Sub GetFormula（）
 Dim ws As Worksheet 
 Dim rng1 As Range 
 Dim rng2 As Range 
对于每个ws在ActiveWorkbook.Sheets 
设置rng1 = Nothing 
 On Error Resume Next 
设置rng1 = ws.Cells.SpecialCells（xlCellTypeFormulas）
错误GoTo 0 
如果不是rng1是没有然后
对于每个rng2在rng1.Areas 
'转储这里的单元格
下一页rng2 
如果
下一个ws 
 End Sub 
  

I have a massive program written with VBA and cell formulas. I am tasked to reverse engineer it into C# winforms. I figured for a start, I need to see all the cell formulas in a flat list.

Any existing way to do it? Thanks in advance!

EDIT: Just to share, with the help of answerers, I managed to come up with this:

解决方案

in VBA (easily modifiable to vbscript) you could quickly dump all formulae in all sheets to a flat txt file (change your path to suit) with an efficient variant array. code sourced from my article here

Const sFilePath = "C:\test\myfile.txt"    

Sub CreateTxt_Output()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim X
    Dim lRow As Long
    Dim lCol As Long
    Dim strTmp As String
    Dim lFnum As Long

    lFnum = FreeFile
    Open sFilePath For Output As lFnum

    For Each ws In ActiveWorkbook.Worksheets
    Print #lFnum, "*****" & ws.Name & "*****"
        'test that sheet has been used
        Set rng1 = ws.UsedRange
        If Not rng1 Is Nothing Then
            'only multi-cell ranges can be written to a 2D array
            If rng1.Cells.Count > 1 Then
                X = ws.UsedRange.Formula
                For lRow = 1 To UBound(X, 1)
                    For lCol = 1 To UBound(X, 2)
                        'write each line to txt file
                        Print #lFnum, X(lRow, lCol)
                    Next lCol
                Next lRow
            Else
                Print #lFnum, rng1.Formula
            End If
        End If
    Next ws

    Close lFnum
    MsgBox "Done!", vbOKOnly
End Sub

[Updated section - you can isolate formulae quickly in VBA by using SpecialCells. Error Handling is needed in case there are no formulae on a sheet, see GetFormula below

Sub GetFormula()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    For Each ws In ActiveWorkbook.Sheets
    Set rng1 = Nothing
        On Error Resume Next
        Set rng1 = ws.Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        If Not rng1 Is Nothing Then
            For Each rng2 In rng1.Areas
            'dump cells here
            Next rng2
        End If
    Next ws
End Sub

这篇关于生成所有excel单元格公式的平面列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！