Public Variables Not Holding The Value
May 14, 2008
I am trying to use a public variable but it is not holding the value when I call another sub.
Here is basically what I have:
Public AcctName as Long
Sub Statments()
There is much more code in here but it is not relevent
AcctName = an account name lets say "frank"
If X XX then
Call Title
End If
End Sub
It then goes to the title sub and executes the code however it is supposed to write out the value held in acctname but it comes up blank instead of as Frank which is the value it held when it left the statments sub. I am new to this public thing and need any help I can get.
Just a note: the spelling is correct and the variable does hold the correct value before title sub is called.
View 9 Replies
ADVERTISEMENT
Jul 21, 2009
I've recently been experiencing some strange phenomena in relation to public variables. It seems that if I run separate routines in quick enough succession (e.g. press a button twice within a couple of seconds), the memory of the public variable "hangs over" into the second routine.
The routines, however, are completely separate events. And I'm not using userforms or anything like that in order to keep VBA running in the background. In which case it seems that Public variables don't always get set to nothing with the end of the final sub in a routine. Has anyone else experienced anything like this? I was under the impression only Static variables could do this.
View 9 Replies
View Related
Oct 20, 2006
I'm programming with multiple workbooks. So public variables in one workbook often reference ranges in another. Anyhow, I have found that, when closing a workbook, unless I nullify all public variables that reference this workbook, it closes in excel but remains visible in the VB Editor. When I open the workbook again, another instance of its VB project appears in the VB Editor..and so on and so on, which is obviously pretty annoying..
I've worked out a way of nullifying (setting equal to nothing) all public variables in a workbook, but it's quite arduous and I haven't tried it yet.. basically, it involves running a code which runs through the procedure which sets these public variables (they are all set in a single procedure), then having the code write a new subroutine setting all variables to nothing, then running this subroutine..
View 3 Replies
View Related
Apr 19, 2007
how do you declare public variables? In one of my forms I declare the variables but for some reason can not access them in other forms.
Public Rm_new As Integer
Public Angle_new As Integer
Public Pl_new As Integer
Public Sub cal_graph_Click()
'Stating variables
Rm_new = text_rm.Text
Angle_new = text_angle.Text
Pl_new = text_length.Text
View 3 Replies
View Related
Aug 6, 2009
Is tere a way to reset all public variables in a module. I have a number of sub routines withing the same module and need to carry a variable from one to another so I have decalared tham as public variables.
This works fine except for an instance when I expect the variable to be empty (because nothing has yet been assigned to it) when in fact it contains the value that was assigned to last time another sub was run. So I suppose what I need is a way to reset all the variables in the module when a particular sub ends
View 3 Replies
View Related
Jan 13, 2008
I make a userform called Option and now i want to call some of the variables of the userform in a module. First i try to copy this variables in a cell with the next
ActiveSheet.Cells(2, 3) = Me.MediaP.Value
and it works. But now, i want to call to MediaP in a module. For do that I try to do this:
With Worksheets("Hoja2")
.Range("B1").Value = Option.MediaP.Value
but it doesn't work. I don't want to use public variables or copy/use the information of the cell(2,3), i only want to know how use the information on the textbox called MediaP in a module.
View 3 Replies
View Related
Aug 18, 2006
In my VBA project I've declared several public variables as normal (ahead of
all procedures) ... Public simolo() As double etc. They are declared in normal modules, and arent declared twice. I set the values in one procedure and then execute a second procedure but, when the variable is encountered in the second procedure, it appears to be empty and I get a "Subscript out of range" error. Clearly, the public variable isn't public since no data is stored in it.
What is going on?
View 6 Replies
View Related
Nov 17, 2007
When working in an userform, and you call another sub routine within the userform, will all variables passed automatically be byref (no way to do byval?)?
View 3 Replies
View Related
Jan 22, 2014
Excel 2010
I am trying to set public variables from an event handling procedure based in a worksheet so I can use that variable in a userform. Nothing I have tried works no matter where I declare the variable. I am using a msgbox to display the variable (a range) but it shows as blank regardless of whether I place the variable in a module, this workbook object or in the sheet object where the event code is placed.
I am sure there is a simple way to transfer variables from the sheet's code (where it must remain as the variable depends on the target cell's position that triggers the event).
View 2 Replies
View Related
Aug 14, 2012
I am trying to bring up a form after clicking the "ok" button on another form. Both forms are defined in my public sub, so basically, the module creates both forms, calls the first form, and hides the second form. Then the first form hides itself and shows the second form. However, when second form is defined using "dim", the first form can't find it to show it, and when I make the second form "public" in my public sub, I get the error that it is an invalid attribute or function.
Here is the code from my sub that applies to this error:
Code:
Public Sub PutInEngine()
Dim InputForm As New FrmInputs
InputForm.Show
[Code].....
View 3 Replies
View Related
Apr 23, 2007
I wish to enter a number in cell D5 and have the entry time recorded in A5.
The code below does that, however, each time I add subsequent data, the original times update to the most recent entry.
I want each entry to remain static or fixed.
Col A Col D
8:56PM 6520
=IF(D5>0,NOW(),"")
=IF(D6>0,NOW(),"")
=IF(D7>0,NOW(),"")
=IF(D8>0,NOW(),"")
View 14 Replies
View Related
Sep 23, 2009
I have some fairly simple conditional statements to format date fields based on current date and upcoming dates. They work perfect on the mac, but when my co-workers open the excel sheet on their PC's it all whacks out?
how to create or save these so they open properly on both platforms?
View 14 Replies
View Related
Mar 10, 2014
I have a problem now with my new spreadsheet. I need to use vba to automate a process.
Date
Item
Holding Days
01/03/2014
A
1
[Code] ........
Scenario: On 01/03/2014, I bought A and hold it therefore holding days is 1. Then I sold it on 02/03.2014 so it became 0. Until 04/03/2014, I bought A back, therefore holding 1 day. 05th I didn't sell so I am still holding it, meaning holding days =2. Then on 06th, I sold A and buy B, resetting the holding days to 1 for a new item.
From the table above, how can I use vba to automate the last column?
View 4 Replies
View Related
Feb 6, 2007
If I'm starting in Sub #1 and I have turned screenupdating off, then later in the procedure I call a function that also has screenupdating off (and also back on at the end of the function), will I get a slight flicker here? I'm noticing a slight one, and I can only imagine that it's where the screenupdating is being triggered again. (removing the updating from the function isn't an option, because another procedure calls the same function & needs it turned off)
View 2 Replies
View Related
Jan 5, 2007
I am given a number say 155 and want to find out which range the number falls in. For example there are ranges like 142-151,152-161, and 162-171. I need a function that tells me my number 155 belongs to the labeled range 152-161.
View 9 Replies
View Related
Sep 27, 2006
I try to write the function that gets some "ref" and returns appropriate item.
For example: if I give to function "C3", it returns "AAA"
if I give to function "R18", it returns "BBB" (cause it between R15 to R26)
if I give to function "R9", it returns "BBB" also.
View 9 Replies
View Related
Feb 5, 2009
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
View 2 Replies
View Related
Feb 12, 2008
Setting the Public Statement does not work. These are my codes:
Option Explicit
Public Ankis_makro As Boolean
Sub Makro2_Ankis_veckor()
On Error Resume Next
Application. ScreenUpdating = False
Sheets("Listan").Activate
Selection. AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9
Range(Range("J2"), Range("I65536").End(xlUp).Offset(0, 1)).FormulaR1C1 = "=TEXT(RC[-9],""ลลลล"")" ...................
What ever I do I can't get the Ankis_makro set to True.
View 2 Replies
View Related
Apr 3, 2014
I am trying to declare a public (or global) array and it's values so I don't have to keep dimensioning it for each function I create.
I was trying things such as:
Public whatever(10) as string
whatever(0) = "something"
whatever(1) = "somethin else"
....
And tried creating an initialize macro in "ThisWorkbook".
View 11 Replies
View Related
May 26, 2014
I have declared one variable outside of Sub. And in a Sub I gave it a value. Then I run a macro, which should take that value and print it to the cell. However, it is printing a blank cell, not even "0" (I run a macro and anything in that cell is removed).
[Code].....
and then my macro:
[Code]......
How to make so macro see my variable declared outside of this macro?
View 8 Replies
View Related
Mar 19, 2009
I am using a set of arrays in a module. The arryas are set as public as the module that loads the data is called many times from diffrent other modules that then use the data.
How so I reset the array at the start of the module so each level is "Empty"
so new data can be loaded with no data left over from the last time the array was used.
View 2 Replies
View Related
Aug 28, 2009
I'm using a public declaration for a worksheet but I seem to have to initialize it in each routine. Is there a way to set it and leave it? So in a module I have:
View 4 Replies
View Related
Jun 6, 2014
I have a combo box which is on a worksheet and it's always active/visible. I need to pass on the selection data to another project. I thought I had successfully converted it to a public variable but it doesn't work.
I've indicated that the Tr variable is public and it does populate with the results of the case select but to no avail.
What do I have to do?
Public Tr As String
Public Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim a As String
Select Case ComboBox1.Value
Case "NYF PT"
[Code] .........
View 6 Replies
View Related
Jul 8, 2008
I have a number of userforms that all reference the same worksheet. I wanted to create a public variable for that worksheet so I don't have to keep referencing it in each userform/commandbuttons etc. So I inserted a module and placed this declaration.
Option Explicit
Public MySh As Worksheet
Set MySh = ActiveWorkbook.Sheets("sheetName")
And I referenced it in a UserForm commandbutton
Private Sub CommandButton2_Click()
MySh.Range("i4") = Me.ComboBox1
MySh.Range("i5") = Me.TextBox1
End Sub
View 9 Replies
View Related
Feb 12, 2009
I have a VBA application that uses data from a sheet in the excel workbook. It accesses this data with an ADO connection. On workbook open I open this connection--I declare it as public variable so I can continue to use it throughout the session. My problem is that the connection really doesn't ever dissappear. I have the problem that once I close the workbook, the project still exists in the VB Editor. How do I get rid of the connection? I am stuck because I declared it as public? I want to keep it open, rather than opening every time I want to use it, because it is faster to keep the connection open.
My code is below.
Option Explicit 'requires variable declaration
'declare global connection
Public cn As ADODB.Connection
Sub OpenDBConnection()
'open db connection
'this happens on workbook open
If cn Is Nothing Then
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & WCHSBook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
.Open
End With
End If
End Sub
View 9 Replies
View Related
Oct 28, 2009
explain the difference between declaring a variable as global or as public. Aren't they both available to the entire project including forms?
View 9 Replies
View Related
Feb 22, 2010
I've got several proccedures all inside a single module where I declare 7 variables as Public before the start of the first proccedure.
My structure is something like this:
Public v1 as Variant
Public v2 as Variant
.
.
.
Public v7 as Variant
Sub Proc1
Call Proc2 (defines v2 as name of a file opened by a user; inside Proc1)
Call Proc3 (inside Proc1)
Call Proc4 (inside Proc3)
Call Proc5 (inside Proc5)
End Sub
Sub Proc6
Here I use v2
call Proc7 (inside Proc6)
End Sub
Variable v2 is uniquely named and is only used to hold a file's name so that I can call upon that file in various procedures. However, after Proc1 ends, Proc 1 retains the variable names of some of my Public variables, but not all, including v2, which I need!
I've tried going through this all step-by-step, no joy/nothing apparently obvious. I've also turned on Tools-Options-General-Notify Before State Loss but this isn't generating anything either.
View 9 Replies
View Related
Feb 10, 2007
I have a few lines of code as follows:
Private Sub Workbook_Open()
fileName = ThisWorkbook. Name
fileLocation = ThisWorkbook.Path
Dim strFound As Boolean, pos As Long
If Not fileName = "" Then
strFound = False
pos = 1
Do While strFound = False
stringFound = Mid(fileName, pos, 1)
If stringFound = "-" Then
productName = Mid(fileName, 1, pos - 1)
MsgBox "Name of Product Is: " & productName
strFound = True
Else
pos = pos + 1
End If
Loop
End If
End Sub
It would return "Test" if the file's name is "Test-Part1.xls". I then declare the productName variable as Public (Public productName As String) in one of the modules. But the productName only holds the value for a certain amount of time. After a while, it's empty.
View 4 Replies
View Related
Feb 17, 2007
I read the Excel VBA Variables Scope and Lifetime info, but I still don't understand b/c of the statement "It's value is retained unless the Workbook closes or the End Statement is used." Which "End Statement"?
I want to fill an array in a form module, then later use those values in a different form module. If I declare the public variable in a standard module, I know any of the modules can access it, but will it retain its value after form1 is no longer running?
If not, do I need to involve a class somehow? I know nothing about classes except that their variables retain their values as long as any of the members is running.
View 9 Replies
View Related
May 2, 2007
I have Three questions about debugging VBA code of an excel add-in.
-Breakpoints set in certain Public Subroutines are NOT hit. I have an application that writes data to the add-in. You cannot debug the app + the add-in. The add does things(e.g. writes data), then calls a main public macro in the add-in, then the addin takes over. If I comment out the main macro, the parent app halts, then I try to set breaks in the add-in.
After resumeing, some breaks are never hit. I know this because if I set a stack trace (output ing the func name to a temporary worksheet), I see these functions being hit. All the modules are public.
Usually in C, breakpoints are not hit because that code is NOT part of the address space of the program.
2. Very naive question (I am a C,C++ programmer)...some tiimes when I hit F5, the whole app runs, other times a list of macros comes up (just as if I had gone to the menu and went toolsmacros. Why is this?????.....placement of the cursor?????
3. If in the app I have 5 code modules, with two public subroutines each.
HOW COME, IF I GO TOOLSMACROSRUN (same as what I get sometimes when I go F5), I ONLY GET A PARTIAL LISTING OF THE SUBROUTINES, ie NOT ALL 10?
View 3 Replies
View Related
