Subscript out of range effor for public variable

[VBAva]

Hello

i keep getting a subscript out of range error for a public variable in my program.
the variable is defined in one module and called from another.
when i use a message box to print the value of the variable it is empty when it previously had a value, so idont know why its now empty.

a global variable should keep its value while the project is open, right?

im quite confused my this

 

[SQLBI]

You're right, the variable should be stored in memory until the app exits.

Can you step throught the code and work out at which point the value of the variable becomes empty?

Alternatively, why not try a Public Function to return your value for the MsgBox?



Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[VBAva]

there is something very strange going on.
FileOpenVersion only has a value straight after it is used.

if there is any break in the program it looses its value.
for example, when my program opens it creates a menubar, and uses the code below, this works fine.

FileOpenVersion = ActiveWorkbook.name
MsgBox FileOpenVersion

i then wait for the user to click a button on the bar, however the contents of the variable are lost in the mean time. i use other variables in the same way and they dont reset

 

[SQLBI]

Sounds very odd.

Create a new module and copy the following code into it.

Public Function WorkbookName() AS Variant

ActiveWorkbook.Name

End Function

Then reference your msgbox to the function to return the value.



Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[VBAva]

I dont konw what it is doing!
the code you gave me worked but then the value just disappeared again.

as soon as execution stops, the variable goes back to "".
i will have to just go around it and code the workbook name into the code. it will not change too often so it should be ok

thanks anyway Leigh