I keep assigning a value to my variable but it stays empty

Question

I am currently working on a code that is supposed to assign a value to a certain variable and open a file with the name assigned to the variable. However, every time I debug the code, VBA warns me that no value is assigned to the variable even though the file is successfully opened. Here is what I have so far:

Sub StressTest()

Dim index As Integer
Dim dateColumn As Integer
Dim portfolioName As Variant
Dim portfolioDate As String
Dim ParametricVar As Double
Dim AuM As Double


portfolioDate = InputBox("Please enter date under the following form : YYYY-MM", "Date at the time of Stress Test", "Type Here")


For index = 3 To 32

portfolioName = ActiveSheet.Range("A" & index & "").Value

'Error happens on this line vvv
Workbooks.Open "G:\Risk\Risk Reports\VaR-Stress test\" & portfolioDate & "\" & portfolioName & ""

ParametricVar = Workbooks("" & portfolioName & "").Worksheets("VaR Comparison").Range("B19")

AuM = Workbooks("" & portfolioName & "").Worksheets("Holdings - Main View").Range("E11")




Sheet1.Cells(index, dateColumn).Value = ParametricVar / AuM
Sheet1.Cells(index, dateColumn + 2).Value = ParametricVar / AuM

Sheet1.Cells(index, dateColumn + 5).Value = Application.Min(Workbooks("" & portfolioName & "").Worksheets("VaR Comparison").Range("P11:AA11"))
Sheet1.Cells(index, dateColumn + 6).Value = Application.Max(Workbooks("" & portfolioName & "").Worksheets("VaR Comparison").Range("J16:J1000"))


Next index




End Sub

Answer 1

You have to switch on the Option Explicit setting, like described here.

In this case you'll notice, that variable Sheet1 is not declared, though it is used. You need either to:

• declare this variable
• or use ActiveSheet, Worksheets("Sheet1") instead of Sheet1
• or use Sheet1 codename reference in your language, e.g. for Russian:

where highlighted ????1 is used instead of Sheet1

Answer 2

Transform your code to this, and study immediate window (Ctrl + G in VBA editor) to see if generated file path is correct.

Sub StressTest()

Dim index As Integer
Dim dateColumn As Integer
Dim portfolioName As Variant
Dim portfolioDate As String
Dim ParametricVar As Double
Dim AuM As Double
Dim strPath As String


portfolioDate = InputBox("Please enter date under the following form : YYYY-MM", "Date at the time of Stress Test")
Debug.Print "InputBox provided value is: " & portfolioDate 


For index = 3 To 32

portfolioName = ActiveSheet.Range("A" & index & "").Value
Debug.Print "ActiveSheet Name is: " & ActiveSheet.Name
Debug.Print "portfolioName value is: " & portfolioName 
Dim strFilePath As String
strPath =  "G:\Risk\Risk Reports\VaR-Stress test\" & portfolioDate & "\" & portfolioName & ""
Debug.Print strPath
Workbooks.Open strPath

ParametricVar = Workbooks("" & portfolioName & "").Worksheets("VaR Comparison").Range("B19")

AuM = Workbooks("" & portfolioName & "").Worksheets("Holdings - Main View").Range("E11")




Sheet1.Cells(index, dateColumn).Value = ParametricVar / AuM
Sheet1.Cells(index, dateColumn + 2).Value = ParametricVar / AuM

Sheet1.Cells(index, dateColumn + 5).Value = Application.Min(Workbooks("" & portfolioName & "").Worksheets("VaR Comparison").Range("P11:AA11"))
Sheet1.Cells(index, dateColumn + 6).Value = Application.Max(Workbooks("" & portfolioName & "").Worksheets("VaR Comparison").Range("J16:J1000"))


Next index

Answer 3

I have the same problem, In the very simple script bellow, the value in 'Locals' is empty but it is surely not since the print to 'Immediate' does return the path!!!

Sub ChooseZipFile()

Dim fullpath As String
'Choose ZIP file
  With Application.FileDialog(msoFileDialogFilePicker)
        'Name of Window
        .Title = "Please Select Zip File"
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "ZIP File", "*.zip; *.TGZ", 1
        'Show the dialog box
        .Show
        
        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
        Debug.Print fullpath
    End With

End Sub