VBA公共字符串未保存

Question

我试图在两个单独的函数中使用公共变量自动保存文件，但由于某些原因，我的公共变量似乎不能保存。

在ImportGammaData()中为我的公共变量gammaSheet赋值后，该函数可以正确地为Sheets(wkbkSheet).Name = gammaSheet赋值。

MsgBox(gammaSheet)还会显示正确的字符串。

不幸的是，当我尝试在SaveAs()中使用gammaSheet时，它没有任何价值。

MsgBox(gammaSheet)绝对不显示任何内容。

到底怎么回事？

Public gammaSheet As String
Public radionuclide As String
Public radioligand As String
Public studyCond As String
Public studyDate As String
Public folderName As String
Public studyName As String
Public gammaFile As Excel.Workbook
____________________________________________________ 
Sub ImportGammaData()

Dim wkbkSheet As String
wkbkSheet = ThisWorkbook.Sheets(6).Name
ThisWorkbook.Sheets(wkbkSheet).Range("A1:L105").Value = ""
ThisWorkbook.Sheets(wkbkSheet).Range("A1:L105").Interior.ColorIndex = 0

Dim gammaFileName As String

Dim starting_file_directory As String
starting_file_directory = Range("E3").Text
ChDrive starting_file_directory
gammaFileName = Application.GetOpenFilename(, , "Gamma counter file for study sheet")

Set gammaFile = Workbooks.Open(gammaFileName)

With ActiveWorkbook

gammaSheet = ActiveSheet.Name

Range("A1").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(17, 1), Array(41, 1)), TrailingMinusNumbers _
    :=True

Range("A3:A8").TextToColumns Destination:=Range("A3"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(20, 1), Array(31, 1)), TrailingMinusNumbers _
    :=True

Range("A10").Select
Range(Selection, Selection.End(xlDown)).TextToColumns Destination:=Range("A10"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(11, 1), Array(17, 1), Array(23, 1), _
    Array(32, 1), Array(42, 1), Array(51, 1), Array(61, 1)), TrailingMinusNumbers:=True

ThisWorkbook.Sheets(wkbkSheet).Range("A1:I105").Value = gammaFile.Sheets(gammaSheet).Range("A1:I105").Value

End With

gammaFile.Close savechanges:=False

Sheets(wkbkSheet).Name = gammaSheet

Sheets(gammaSheet).Range("A10:I10").Font.Bold = True
Sheets(gammaSheet).Range("J10") = "Sample ID"
Sheets(gammaSheet).Range("J11") = "Cs-137"
Sheets(gammaSheet).Range("J12") = "Ge"
Sheets(gammaSheet).Range("J13") = "Background"
Sheets(gammaSheet).Range("K10") = "Volume (µL)"
Sheets(gammaSheet).Range("L10") = "ACN (µL)"

Sheets(1).Range("A16").Value = gammaSheet

Sheets("Decay Correction").Range("C2").Value = Sheets(gammaSheet).Range("C1").Value

If Sheets(gammaSheet).Range("B6").Value = "C-11" Then
    Sheets("Decay Correction").Range("B3").Value = "t(1/2) of C-11"
    Sheets("Decay Correction").Range("C3").Value = "20.385"
End If

If Sheets(gammaSheet).Range("B6").Value = "F-18" Then
    Sheets("Decay Correction").Range("B3").Value = "t(1/2) of F-18"
    Sheets("Decay Correction").Range("C3").Value = "109.77"
End If

Sheets(1).Range("B6").Value = "GAMMA COUNTER FILE IMPORTED!"
ThisWorkbook.Sheets(1).Range("B6").Interior.ColorIndex = 50

radionuclide = "[" + Sheets(gammaSheet).Range("B6").Value + "]"

End Sub
____________________________________________________ 
Sub SaveAs()
Sheets("Initial").Select
folderName = Range("A20").Value
studyName = Range("A16").Value + " " + Range("B16").Value + " " + Range("C16").Value + " " + Range("D16").Value + " " + CStr(Range("E16").Value) + " " + Range("F16").Value

'Dim strPath As String
'strPath = "C:\Users\tyeg\Documents\Gamma Counter Processing" & folderName
'Dim elm As Variant
'Dim strCheckPath As String
'
'strCheckPath = ""
'For Each elm In split(strPath, "\")
'    strCheckPath = strCheckPath & elm & "\"
'    If Len(Dir(strCheckPath, vbDirectory)) = 0 Then MkDir strCheckPath
'Next

ThisWorkbook.SaveAs Filename:= _
    "C:\Users\tyeg\Documents\Gamma Counter Processing\" + folderName + "\" + studyName + ".xlsm"

MsgBox (gammaSheet)

gammaFile.SaveAs Filename:= _
    "C:\Users\tyeg\Documents\Gamma Counter Processing\" + folderName + "\" + gammaSheet + ".T"

End Sub

Answer 1

使gammaSheet成为全局变量。全局变量在执行后保持其值不变。在这种情况下，在获取ImportGammaData()中的值之后，从SaveAs()中调用gammaSheet的值将不会有任何问题。

更改此设置：

    Public gamaSheet As String

要这样做：

    Global gamaSheet As String