2

I am perplexed why my global variable within a module fall out of scope at the conclusion of a sub procedure.

I declare the range at the top of the module out side of all subproc and functions as below 

Option Explicit

Dim TIMEDATA As Range
Dim FREQDATA As Range

Const StartLoc = "B4"
Const flowLoc = "F4"
Const dtLoc = "J8"

In my subproc I define one of the ranges. 

Public Sub PortandConvertData()

<SNIP> 
Set TIMEDATA = calcSheet.Range(Cells(2, 2).Address, Cells(2 + dataSize, 2).Address)

End Sub

After the sub completes in the watch window I see the variable TIMEDATA go from Range/Range to Range and the value go from correct to simply out of context.

I want to store data in the module rather than pasting in a sheet or something.

Any help is much appreciated

Community
  • 1
  • 1
TheCodeNovice
  • 750
  • 14
  • 35
  • Declare them as public. Public TIMEDATA As Range Public FREQDATA As Range – MatthewD Sep 25 '15 at 18:22
  • @MatthewD I tried Global and Public and neither work. Goes to out of context as soon as the sub proc finishes. – TheCodeNovice Sep 25 '15 at 18:42
  • @MatthewD that did not work either. – TheCodeNovice Sep 25 '15 at 18:47
  • Is your code execution stopping and returning focus back to the spreadsheet? – MatthewD Sep 25 '15 at 18:51
  • @MatthewD Yes. I have spreadsheet buttons calling these functions/procs – TheCodeNovice Sep 25 '15 at 18:52
  • It seems like you want a global static variable. I don't think this is available in VBA --- see [this](http://stackoverflow.com/questions/21806425/public-static-variable-in-excel-vba). My understanding is that variables go out of scope when there's no code running. You could use cells in a worksheet to implement static storage between subroutine calls. – xidgel Sep 25 '15 at 19:52
  • You need to show the full code for `PortandConvertData()` – SierraOscar Sep 25 '15 at 20:26

1 Answers1

5

Make sure that the Context in the Watch properties includes the Procedure/Module you are actually watching. You can make sure by setting the context to All Modules:

From the Watches panel: Right Click the Expression --> Edit Watch --> From the Context group set Procedure/Module to All.

If this is not the actual issue, then you are having the same issue I'm having from Access VBA.

This thread explains the same: ThisWorkbook not holding global variable value to cancel ontime()

Community
  • 1
  • 1
Carlos J. Jimenez
  • 53
  • 1
  • 6