Set Public Variable
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
ADVERTISEMENT
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
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
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
Aug 20, 2014
I have a userform that is called within a sub in module. I declared a public string, "divisonb", in the module. When the userform's ok button is clicked, I define the public string through a "select case" method.
At the end of the private sub for the ok button click I have this:
[Code].....
a message box comes up with the correct string for divisonb. After the sub ends and it returns to the module I have the following:
[Code] ....
When this message box pops up, it is blank. Somehow, divisonb was redifined as blank within that 2 lines of code. All my other public strings are returned to the module with their correct values.
View 3 Replies
View Related
Mar 6, 2010
How can I make a dynamic array public?
I have to Dim it so it stays valid only inside the sub.
Public MyVar()
Sub test1()
Dim MyVar()
ReDim MyVar(1 To 4)
For x = 1 To 4
MyVar(x) = "ffffff"
Next x
End Sub
Sub test2()
For x = 1 To 4
Range("A" & x) = MyVar(x)
Next x
End Sub
Error I get is 'MyVar(x) = Subscript out of range'
View 9 Replies
View Related
Feb 10, 2007
I want to define a varible named MonthEnd that I will use in more than one project. In a normal example the variable would look like this:
Dim MonthEnd As String
MonthEnd = Format(Sheet1.Range("C3"), "MMYY")
The problem is that I will be using this more than one time so I figured I could define this a Public constant like
Public Const MontEnd As String = Format(Sheet1.Range("C3"), "MMYY")
View 3 Replies
View Related
Jul 27, 2007
Hopefully this isn't too vague, but almost every project I've worked on so far has not recognized Public variables in all modules. I've constantly been searching for exceptions to this rule that could be contributing to this issue but haven't been successful.
I have read a million times that declaring a variable as Public makes it visible to all modules and preserves the value throughout. I've also read that if you edit code or reset your project, the values may be lost. I also know that if you try to use a variable before you give it a value (or for object variables set it = to something), it doesn't have a value yet. But all this applied, I'm still not getting Public variables to be consistently recognized or stay with the value I want them to have throughout the lifetime of my project (while the workbook is open)
My specific current issue (one of many so far) is that I have 4 command buttons within a worksheet object. Each triggers a different group of procedures, some of these are user forms. In the first button, I create two worksheets one to contain the current fiscal period "Options" the other for "Summary" of all the totals etc... (all financial data). In this first userform, triggered by the button, I allow the user to name these worksheets: like Summer2007Options or Summer2007Summary and I declare these publically as object variables OptSheet and SumSheet. These variable names and sheets are recognized for the next 2 buttons but now that I'm on the fourth, I have object variable not set errors when I try to refer to this variable.
View 9 Replies
View Related
Mar 14, 2008
I've got the following code, setting up and initializing a Public variable for a sheet, but I've found that it's has no value after the Auto_Open is run,
though it works fine in the procedure Auto_Open calls.
I can get around it by reSetting it in the module I need it in,
but that kind of defeats the purpose of having a Public variable:
code in Module1
Public parSheet As Worksheet
Sub Auto_Open()
Set parSheet = Worksheets("Params")
Call Initialization.BuySellInit
End Sub
View 8 Replies
View Related
Jan 29, 2009
I have an Excel 07 spreadsheet containing multiple tabs, modules, and userforms.
In Module1, I have a public variable declared as boolean - will call it X. When X is selected from a combo box in Userform 1, X is set to TRUE. However I've noticed that when the user enters Userform 2, X is somehow set to FALSE.
I can't set it back to TRUE at that point because more often than not, it SHOULD be FALSE, thereby sending the macro down a different path. Any idea how I can retain the "TRUE" value for X. I've tried changing the Public Variables to Global, but am still having the problem.
View 2 Replies
View Related
Oct 15, 2008
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View 5 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 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
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
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
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
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
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
Jul 17, 2007
I've got two public dynamic arrays being declared in a module attached to my workbook. I then have a sub that runs off a userform in that workbook which populates and redims the arrays preserving the data. Next I activate a PowerPoint application and insert some tables into which I want to put the data in the arrays but when I refer to the arrays (admittedly in a Sub called from the first Sub) I'm getting a 'subscript out of range error'. I can print out the data before I call the second sub and it's fine. I've worked with public variables before and never had this problem. Do I have to call the public variable differently if I'm curretnly in a different application (i.e. PowerPoint)?
View 2 Replies
View Related
Jul 8, 2009
I am Having trouble calling an macro that is not in the module that is calling it. I thought that all I had to do was label the Macro as Public instead of private, but it is not working. If I copy and Paste the Macro code into the module that is calling it, everything works just fine, but this is making it difficult to keep things organized.
View 8 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 3, 2014
I need to define a string in a specific workbook that can be called across any worksheet in that workbook.
The code in ThisWorkbook would be similar to:
Sub Workbook_Open()
MyID = "FCF"
End Sub
Within any Worksheet in ThisWorkbook...
Sub SomeMacro()
MsgBox = MyID
EnD Sub
The MsgBox should read FCF
View 4 Replies
View Related
May 19, 2008
I have a public array in a module in my spreadsheet. The array is an array of a class type (not integers, strings, etc). When I run the macro that uses the array for processing the first time, everything seems to work properly. However, if I run the macro a second time WITHOUT getting out of Excel and coming back in, it doesn't come back with the proper results. It seems like either the array isn't being cleared out properly between the macro runs, OR that something in the macro is overwriting memory. Is there a way to make sure the array is being cleared out between runs of the macro?
View 9 Replies
View Related
