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

at what line do you get error message? Have you switched on `Option Explicit`? — Van Ng, Jun 24 '19 at 13:04
You can set the results of `Workbooks.Open` to a workbook variable, and use that to simplify the subsequent code. — John Coleman, Jun 24 '19 at 13:10
The error appears on the line : Workbooks.Open "G:\Risk\Risk Reports\VaR-Stress test\" & portfolioDate & "\" & portfolioName & "" — YaraK, Jun 24 '19 at 13:19

Van Ng · Answer 1 · 2019-06-24T13:18:58.837

0

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

edited Jun 24 '19 at 13:18

answered Jun 24 '19 at 13:06

Van Ng

773
1
7
17

See [this question](https://stackoverflow.com/questions/41477794/refer-to-sheet-using-codename), specifically number 3. – BigBen Jun 24 '19 at 13:12
Yes, I've understood. I've got localized version of MS Excel, so CodeName of mine is another language – Van Ng Jun 24 '19 at 13:16
1

Even if it isn't recommended, `Sheet1.` is unlikely to be the problem. For one thing, OP is explicitly referring to the value of one of their variables and they are not thinking about `Sheet1` as a variable at all (since it isn't, really). `Option Explicit` is a good recommendation. – John Coleman Jun 24 '19 at 13:18
He may have localized version of MS Excel, as in my case, so that would be obstacle – Van Ng Jun 24 '19 at 13:19
See the latest [comment](https://stackoverflow.com/questions/56737201/i-keep-assigning-a-value-to-my-variable-but-it-stays-empty#comment100033642_56737201) on which line throws the error. It has nothing to do with `Sheet1`. – BigBen Jun 24 '19 at 13:20

Van Ng · Answer 2 · 2019-06-24T14:14:20.503

0

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

edited Jun 24 '19 at 14:14

answered Jun 24 '19 at 13:22

Van Ng

773
1
7
17

Thanks, I tried this but it still gives me the same error on the line: Workbooks.Open StrPath – YaraK Jun 24 '19 at 13:31
The problem is with the portfolioDate variable, it isn't taking the value I am trying to assign to it. It says that it is empty even though the file is successfully opening – YaraK Jun 24 '19 at 13:32
And what do you get immediate window? Check this path, because error seems to be in this string concatenating? – Van Ng Jun 24 '19 at 13:33
Updated code, you can try to run it again and see what value is used further (in immediate). Other point is that I've removed default value for inputbox. – Van Ng Jun 24 '19 at 13:42
2

`strFilePath` is empty because you've assigned to `strPath`, not `strFilePath`, but you don't use `strFilePath`. Again, `Option Explicit` will prevent this possible source of error. `portfolioDate` is *definitely* not empty unless you cancel the InputBox. I don't think it is necessary to enclose `portfolioName` in escaped double quotes. try `Workbooks(portfolioName)` isntead. – David Zemens Jun 24 '19 at 13:45
Thanks, I just corrected the code but I still have the same error on the same line. portfolioName is still empty. – YaraK Jun 24 '19 at 14:12
Updated code. You are using `ActiveSheet` reference, I think you are addressing sheet, that contains nothing in desired cell. Check what ActiveSheet name you are addressing through studying immediate window – Van Ng Jun 24 '19 at 14:16

score 0 · Answer 3 · answered May 11 '22 at 14:25

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

If this is a question for which you did not find an answer, then issue it separately as an independent problem. — Vitalizzare, May 12 '22 at 08:18

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

3 Answers3