Public Array Not Avaiable In Sub?
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
ADVERTISEMENT
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 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
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
Apr 22, 2014
i'm trying to develop an array that is populated when a userform is activated. The userform has a command button that when pressed, will cross check the information filled out in the userform with the entries in the array. If there are no matches then the array is ReDim and the new information is added to the array. Once all the entries have been made. The array is then transposed to a sheet titled "Database". My problem is that vba is not allowing me declare a public array.Below is the first part of the code. Which is when the userform is activated.
Private Sub UserForm_Activate()
With Sheets("Resources")
cbZIP.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
cbBED.List = .Range("B2", .Range("B" & Rows.Count).End(xlUp)).Value
cbBATH.List = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
cbSTABRV.List = .Range("G2", .Range("G" & Rows.Count).End(xlUp)).Value
End With
[code]....
View 1 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
Aug 29, 2013
I understand how to create an array within a routine:
CurOptions = Array("M1", "M3", "M3 DSR", "L1", "L2", "CSA")
But I need the array to be public as the array can hold one of 6 different sets of values so I will declare the array to be only used within the module as:
Dim CurOptions(30)
Later I need to fill in the values manually, they will not be copied in, they need to be provided in a list.
It should be just one line.
View 1 Replies
View Related
Jan 16, 2010
I am trying to add a picture to a chart and need to format it, but the functions are not available.
What I do is:
1. Format Chart area
2. Fill Effects
3. Picture
4. Select Picture
After I select the picture the "Format" and "Apply to" are not available and I can not change anything.
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
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
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
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
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
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 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
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
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
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
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
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
Jul 31, 2008
how I can refer to a public constant in a different workbook's (which is already open) VBA from the workbook which I am currently working on's VBA?
View 9 Replies
View Related
