我幫朋友寫過這麼一個類似的VBA

可以把加減乘除之類的公式（引用數值必須在這張sheet裡）自動轉換成數值

我不知道你需要輸出什麼，所以把原本的程式碼給你，你可以自行加以修改

Private Sub CommandButton1_Click()

Dim fml As String

Dim pa(20) As String

Dim lg As Integer

Dim out As String

Dim aski As Integer

Dim Tsp As Boolean

Dim td As Boolean

Dim I, J, K As Integer

Dim cll As Range

Dim mf As String

Dim sz As Range

On Error GoTo 444

mf = Dir(ThisWorkbook.Path & "\output.txt")

If mf <> "" Then

Kill ThisWorkbook.Path & "\output.txt"

End If

Open ThisWorkbook.Path & "\output.txt" For Output As #1

For Each cll In Application.InputBox("Choose the zone you want", Type:=8)

fml = cll.FormulaLocal

" Debug.Print fml

lg = Len(fml)

Tsp = False

td = False

out = ""

For I = 1 To 20

pa(I) = ""

Next I

K = 1

For I = 1 To lg

aski = Asc(Mid(fml, I, 1))

If aski >= 65 And aski <= 90 Then

Tsp = Not Tsp

pa(K) = pa(K) & Mid(fml, I, 1)

GoTo 100

End If

If Tsp And aski >= 48 And aski <= 57 Then

pa(K) = pa(K) & Mid(fml, I, 1)

If I = lg Then

out = out & Range(pa(K))

" GoTo 300

End If

ElseIf Tsp And (aski <= 48 Or aski >= 57) Then

Tsp = Not Tsp

out = out & Range(pa(K))

K = K + 1

td = Not td

End If

100 If Not Tsp Then

out = out & Mid(fml, I, 1)

ElseIf Tsp And td Then

" out = out & Range(pa(K - 1))

td = Not td

End If

Next I

"300 Debug.Print out

Print #1, cll.Address(rowabsolute:=False, ColumnAbsolute:=False) & " " & out

Next

GoTo 999

444 MsgBox ("Something is wrong")

999 MsgBox ("over, (^o^)/~~~")

Close #1

End Sub

先把上月期末結存資料選中，ctrl-c（複製）。然後到新的位置，點中其第一個單元格就行，不能用ctrl-v（貼上），那就會把公式的引用貼上。應該點開“選擇性貼上”，選中“資料”，即只貼上資料形式的資訊，不貼上其它。

這就是我們通常實際說的，“把活資料（含有公式運算的資料）變成死資料”。一般，重要的資料都要這樣備份。活資料特別易被病毒或出錯攻擊。