|
Thanks for your quick reply, Eddy. Dumb q: How do I make Module1 public? Also, would it help to put the code in a Class Module?
|
|
|
|
|
Hoping, just the same as you would with a method;
Public Module1
Public Sub AddMenus()
Dim FormIndex As Integer
Dim ReportViewName(2 To 4) As String
‘create menu structure for the workbook
‘read variable values – FormIndex=2, etc.
End Sub
Tim Bostwick wrote: Also, would it help to put the code in a Class Module?
That would make it a class, and you'd have to create an instance before you use it;
Dim MyVar as ClassModule1
Set MyVar = New ClassModule1
What version of Excel are you in BTW?
I are Troll 
|
|
|
|
|
Wouldn't something like below allow you to access the value of the variable outside the function?
Public Module1
Public FormIndex As Integer
Public Sub AddMenus()
Dim ReportViewName(2 To 4) As String
‘create menu structure for the workbook
‘read variable values – FormIndex=2, etc.
End Sub
If so, do look into class modules and create a property with a getter 
I are Troll
|
|
|
|
|
Right, but when AddMenus() runs, it won't create the FormIndex variable, right?
The general issue is, How do you use Workbook_Open (in ThisWorkbook) to load public variables in Module1?
Possibility #1: Do as you indicate above, make FormIndex public with the problem already mentioned.
Possibility #2: Declare FormIndex public in ThisWorkbook. But I also have arrays that need to be read and ThisWorkbook won't allow arrays?
Is there any way to get all of Module1 read by Workbook_Open?
|
|
|
|
|
Hi
Have you actually tried the Possibility #1 you mention above? In the AddMenus in Module1, try assigning a value to the FormIndex variable and then get the value to display in your UserForm... I think you will find that it works!
|
|
|
|
|
Chandra: Yes I did but the problem is that FormIndex is outside the AddMenus sub so it won't be read into memory when I call AddMenus. I solved the problem, however, by realizing that I don't have to load all the arrays at once. I'm used to that approach from programming in C# where I just read everything up front as public and then have them available throughout other programming. I started thinking more of a per-Sheet approach for VBA and things work fine by putting the read-the-array code with each UserForm. So now I don't need to invoke from ThisWorkook.
Old code, quoting from a previous post:
Public Module1 ' static class
Public FormIndex As Integer ' outside the function, compareable to a static variable
Public Sub AddMenus() ' static method
Dim ReportViewName(2 To 4) As String
‘create menu structure for the workbook
‘read variable values – FormIndex=2, etc.
End Sub
|
|
|
|
|
Variables declared within procedures ("sub") are not available to be referenced outside the procedure.
To make it available, declare it as
Public FormIndex as Integer
before the code for any procedure.
For instance, your Module1 code should look like this:
Option Explicit
Public FormIndex as Integer
Public Sub AddMenus()
Dim ReportViewName(2 To 4) As String
‘create menu structure for the workbook
‘read variable values – FormIndex=2, etc.
End Sub
You will now be able to reference this variable from within your UserForm.
|
|
|
|
|
Hi Chandra: Thanks for your reply. I understand how to make variables public, ouside the sub. What I'm trying to do is declare certain variables public as part of Workbook_Open which calls the sub AddMenus(). But the only way to make the variables public is to declare them above AddMenus()in Module1 but that means they won't be read when Workbook_Open calls AddMenus()!
|
|
|
|
|
Hi
That is not entirely correct... all variables declared as public within modules are available to the entire VBA project, even at the WorkBook_Open sub. So, if you declare your variable as per my example, you will see that you can reference it within the WorkBook_Open sub too 
|
|
|
|
|
Let me try to explain it differently. Here's the structure:
In ThisWorkbook:
Workbook_Open()
Call Module1.AddMenus
In Module1:
Public Var1
Sub AddMenus()
private variables
End Sub
In UserForm1:
do something with Var1
When ThisWorkbook loads and reads Workbook_Open, it calls AddMenus but it *doesn't* read Var1 into memory. So UserForm1 can't access it. if Var1 is put inside AddMenus, it'll get read but it won't be public. And... if I put Var1 into ThisWorkbook as public, that's won't work if Var1 is an array.
|
|
|
|
|
Hello mates.
Need help.
I have datagridview which has paging like in gridview of asp.net and
its smoothly working. The problem when I add a new row
in the second page it goes to first page.
Goal:
User will able to add new row by clicking add new row button. Then if
the row is more than 12rows, the next/previous button will enabled
and if the user still click add new row button. the 13th row or more
will go to second page. Then if the user will go to second page then
add row, it should not go to fist page.
Thanks in advance.
C# コードMicrosoft End User
2000-2008
「「「「「「「「「「「「「「「「「「「「「「「「「「「「
The best things in life are free
」」」」」」」」」」」」」」」」」」」」」」」」」」」」
|
|
|
|
|
I have a database search function on a Windows form app that needs to be readily accessible, but I don't want it displayed on the form at all times as it is confusing since you can search the same fields that you can edit on screen.
Long story short is I would like the search menu to behave like the toolbox / server explorer / data source left docked tool strip in VS 2008. Right now I have created a tool strip and docked it left and have the buttons the way I want them, but I do not have an eloquent solution for the pop out and pop in functionality.
I put a panel into place that has the search functionality and I simply made it visible = false until the search button is pressed.
If pnlSrch.Visible = False Then
pnlSrch.Visible = True
gbPhoto.Visible = False
Else
pnlSrch.Visible = False
gbPhoto.Visible = True
End If
Is there a more eloquent method of creating a slide in slide out side menu without using WPF?
|
|
|
|
|
|
I have seen many articles on this website that could help you. Consider doing a search. The manual/experimental way would be to create a timer that will move/collapse the panel to the side of the screen by altering the width, height, x, and y.
Regards,
Thomas Stockwell
Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
Visit my Blog
|
|
|
|
|
|
I have a large application in VB6 which makes extensive use of control arrays as they are so easily used and referenced by the index value
With future support disappearing after win7 I am trying to code extensions in a way that will make the eventual transfer to vb.net easier but faced with a form that has 4 groups of 10 simple list combo boxes that need to be set at runtime and then saved after the user has selected their content all the alternatives to control arrays significantly increase the amount of typing that I have to do and therefore the opportunity for a typo that compiles (each control is called cmbCAtab1, cmbCAtab2 etc which is easy for cut and paste but easier to miss changing one of the names afterwards and it would still compile)
Would it really hurt VB.net so much to have Control Arrays which it seams to me were quite a simple way of handling things
I appreciate there are ways to combine the event handlers and to set and read the "array" of controls in VB.net but WHY should I have to jump through all the extra hoops when I should just be able to use an index value???
It would also help VB6 applications move to VB.net which I would have thought was part of the aim of the .net version developers
Gets under desk and puts fingers in ears, ok people fire away!
|
|
|
|
|
Mike Deeks wrote: Would it really hurt VB.net so much to have Control Arrays
Yes, because the concept isn't OOP compliant.
VB6 taught way too many bad habits.
If you really want control arrays, just throw the controls in a generic collection of them.
|
|
|
|
|
Dave Kreskowiak wrote: Yes, because the concept isn't OOP complian
so having an array of objects breaks OOP compliancy?
I would have looked this up but struggled to find what the definitions are for OOP compliance (yes i did google but got bored after 2 pages of results)
mind you, I did see a number of references where people said they had to break OOP compliance to be able to make a particular program work so maybe it is the OOP definition that is wrong?
|
|
|
|
|
if the Controls are contained in a single Container (a Panel, a GroupBox, ...), you can always use myContainer.Controls(index)
|
|
|
|
|
Luc Pattyn wrote: you can always use myContainer.Controls(index)
true if they are the only controls in that container otherwise it wastes resources putting invisible frames under groups of controls
|
|
|
|
|
If you have that many identical controls, maybe you should be adding them through code instead of copy-pasting in the designer. Do that, and you can add them to a collection (Or even build a dictionary or tree if you get bored) at the same time.
You could even create the first one of each "array", then do something like this, off the top of my head:
Public Function CreateLotsOfStuff(FirstItem as Control, NumberToCreate as Integer) as List(Of Control)
Dim ctlList as New List(Of Control)
Dim previousItem as Control = FirstItem
For x as Integer = 0 To NumberToCreate - 2 'First one's there already
Dim c as Control = previousItem .Clone()
ctlList.Add(c)
previousItem .Parent.Controls.Add(c)
c.Top = previousItem.Top + previousItem.Height + 5
Next x
Return ctlList
End Function
(Haven't done any VB lately, only C#, so my syntax may be a little off)
|
|
|
|
|
Hi
Ever body How to find Particular Drive to the filename i have Temp.txt in my system but i need now
which drive i do know so how to find in my vb6 application or vb.net application with windows
search i need my application only like a windows methods
|
|
|
|
|
I think you are probably looking for one of these[^] methods.
It's time for a new signature.
|
|
|
|
|
dear all
how to use compare in vb 6? thanks a lot
|
|
|
|
|
What do you want to compare?
Objects, strings, numbers, give us a clue!
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
