为什么givingType Dim会变成空白，尽管Range("B3").Value是“停车场”呢？

Question

Sub PullPriorYearActualsByMonth()
'
' Pull prior year actuals by month for a given budget category
'
Workbooks.Open ("S:\Finance\Budget & Forecast\2023\2023 Budget\Consolidated\Finance Use Only\Updating 2022 Budget Macro File.xlsm")

Dim i As Integer
Dim VarCellValue As String
Dim VarCellValue2 As String
Dim VarCellValue3 As String
Dim currwbk As Workbook
Dim givingType As String
Dim fileName As String


fileName = Application.InputBox("Please copy and paste the Other Revenue Filename here")

Application.EnableEvents = False
Application.DisplayAlerts = False

'Workbooks.Open ("I:\Calendar 2023\2023 Budget\2023 Working Budget\3. Other Revenue\[Enter File Name Here].xlsm")
Workbooks.Open ("I:\Calendar 2023\2023 Budget\2023 Working Budget\3. Other Revenue\" & fileName & ".xlsx")
'Workbooks.Open ("I:\Calendar 2023\2023 Budget\2023 Working Budget\3. Other Revenue\2023 Budget - Workplace Giving (3-year Average).xlsx")
Sheets(1).Activate
givingType = Range("B3").Value

直接看上面。它填充正确的值，但维度是空的。我需要用"Set“函数做些什么吗？

Answer 1

避免使用Activate，并将Range完全限定为其父Workbook/Worksheet。

Dim wb As Workbook
Set wb = Workbooks.Open("I:\Calendar 2023\2023 Budget\2023 Working Budget\3. Other Revenue\" & fileName & ".xlsx")

givingType = wb.Worksheets(1).Range("B3").Value