|
each textbox has to have it's own instance
Dim NewTB As TextBox
Dim TBName As String
Dim TBindex As Integer = 1
For TBindex = 1 To 3
NewTB = New TextBox 'this belongs here
TBName = "Textbox" & TBindex
NewTB.Name = TBName
Me.Controls.Add(NewTB)
NewTB.Enabled = True
NewTB.Visible = True
NewTB.Height = 20
NewTB.Width = 100
NewTB.Top = 130 + ((TBindex - 1) * 25)
NewTB.Left = 20
NewTB = Nothing
Next TBindex
|
|
|
|
|
Problem:
Currently I have 12 Excel workbooks (each with 7 worksheets).
The data is identical in nature. The common part is the VBA code.
Every time I have to make a change to one of the worksheet's VBA code,
I need to copy the changes to the other workbooks.
I can use either the Excel buit-in VB editor, or any flavor of Visual
Studio.
I would like to be able to make the modification only once and have
the workbooks reference the code instead of copying and pasting to
each workbook.
Question(s):
1. Is there a way that I can have one central location for the VBA code?
2. Would an Excel add-in be appropriate?
3. Should I program using VS (VSTO)?
I am open to any approach to this problem.
Michael
If we knew what it was we were doing, it would not be called research, would it? --Albert Einstein
modified on Friday, December 07, 2007 3:25:20 PM
|
|
|
|
|
VBA doesn't include any "include" support. It can't import code from some other source. The code is part of the workbook itself.
I think you're taking the wrong approach. If the code needs to work with data from other files, it's the data that needs to be brought to the code, not the code to the data. Your code should be in one spot, and have the ability to use data from any workbook that you chose.
redjoy wrote: 1. Is there a way that I can have one central location for the VBA code?
Not in an external file, no. But, you can have an single workbook that can import data from other workbooks. Or, you can write the code as an Add-In to Excel.
redjoy wrote: 2. Would an Excel add-in be appropriate?
That depends on what your code is doing and what your business process dictates.
redjoy wrote: 3. Should I program using VS (VSTO)?
Probably. Do the research on VSTO here[^].
|
|
|
|
|
First of all thanks for your reply. I am new to add-ins and VSTO.
I only wrote the code behind the worksheets for my personal testing needs but my boss saw this and wants everyone to use this format. Hence the 15+ extra workbooks. I am now trying to re-write my code more efficiently (on my own time) as to make maintenance easier on me (and hopefully my test successors).
Dave Kreskowiak wrote: I think you're taking the wrong approach. If the code needs to work with data from other files, it's the data that needs to be brought to the code, not the code to the data. Your code should be in one spot, and have the ability to use data from any workbook that you chose.
The code does not need to work with data from other files. All the data needed is already contained within each workbook.
Dave Kreskowiak wrote:
redjoy wrote:
2. Would an Excel add-in be appropriate?
That depends on what your code is doing and what your business process dictates.
The format of each Workbook is the same. The code for each workbook is identical.
I use my workbook for analysis purpose and generate a report for each of my workbooks (CSCI).
I execute some code based of values in various columns for each row in worksheet 7.
unique to each workbook:
Columns A-D are used for showing my requirements, test cases and test conditions.
The same in each workbook:
Columns E and F are used to show the status of completeness and performance of current requirement.
Column G shows the platform the current test case runs on (A, B, Both).
Columns H and I shows the test result (P, F, N/A) for platform A chosen in G.
Columns J and K shows the test result (P, F, N/A) for platform B chosen in G.
Columns I-P: not important to code.
Code behind Worksheet 7
for example (pseudo code):
Sub Worksheet_Changed(target as range) 'changes the color coding of active row based on columns E through K.
if a2<>""then
if e2="Complete" and f3="Met" then set the background colors of a2:p2 to GREEN.
...code for color RED.
...code for color YELLOW.
else color WHITE.
end if
else
if g3 = "A" and h3="P" and i3="P" then set the background colors of b3:i3 to GREEN.
... code for color RED.
else color WHITE.
end if
end if
Code behind worksheet 8 copies portions of worksheet 7 to worksheet 2 and 6 and updates a database with the content of worksheet 7.
So right now when I improve/modify one workbook, I must copy the code to the other workbooks.
I was not able to find out any thing about add-ins using the VBA behind a worksheet. However, I will explore the websites you mentioned in your reply.
I hope this post makes sense.
Michael
If we knew what it was we were doing, it would not be called research, would it? --Albert Einstein
|
|
|
|
|
redjoy wrote: The code does not need to work with data from other files. All the data needed is already contained within each workbook.
...
The format of each Workbook is the same. The code for each workbook is identical.
You're missing the point. The code (all 15 copies of it) DOES work with the data from every workbook. You're trying to avoid maintaining 15 copies of the code, so you need to have only a single copy of it somewhere. That single copy needs to get at the data for all 15 workbooks.
But, as you post some of the code, you have a problem. You're handling the events fired by changes in the worksheet. This makes it very difficult to seperate the code from the data, and hence keep only a single copy of the code outside of the workbooks.
redjoy wrote: I was not able to find out any thing about add-ins using the VBA behind a worksheet.
That's because you can't. An Add-In is written in C++, C#, VB.NET, or something else that generates "real code" and is completely seperate from any workbooks.
I'd say your best bet is to write this as an Add-In to Excel.
|
|
|
|
|
I have my VBA script code in one XL file and then refer this file, from the file with the data.
If you clear the data sheets leaving only one (empty sheet) and saves this file as say MyCode.xls
Then open the VBA editor for this file, and goto project Explorer. rightclick on the the spreadsheet file, and choose properties.
That should enable you to give you code a specific name, its practical if it could start with 'a'.
If you then also open the spreadsheet containing only the data, again open the VBA editor, and in the project explorer select the data spreadsheet. Then goto "tools" and "references". This opens the ref's dialog. Here you should be able to locate your code among the many lines. (If the name of your code starts with 'a' it should sort to the top  Check the box.
That should make your code freely available from the datasheet.
I use this approach from excel 2003, and the only downside I have found is that the references have absolute paths.
Regrads *Henrik
|
|
|
|
|
Hi every one..
I have a VB 2005 application and I am doing error handling by “on error goto”.
I wanted to create a log file if there is any error..
I have done some work in C and linux and wondering if there is any way that I can just echo a string to a text log file the way we are able to do in c by just using ‘>’ or some thing similar so that I do not have to open and close the target file each time…
Regards
Nishkarsh
|
|
|
|
|
you should look at try/catch vs on error goto
Private Sub LogMessageToLocalFile(ByVal sMessage As String)
Try
Dim oFile As New System.IO.StreamWriter("c:\myapperrors.log", True)
oFile.WriteLine(sMessage)
oFile.Flush()
oFile.Close()
oFile = Nothing
Catch
End Try
End Sub
|
|
|
|
|
Don't use on error goto. Use structured exception handling.
And have a google for "log4net" - its a free library for logging, and its excellent.
|
|
|
|
|
I've created a basic User Control with VB.NET that works fine in both a Windows Form and a WEB Page.
I'm using the following code to load the User Control into the WEB Page.
<object id="MyUserControl" classid="http:MyUserControl.dll#MyUserControl.TimerControl"></object>
After I sign the User Control it stops working in the WEB Page but is fine in the Windows Form.
What must I now do to enable the Signed User Control to work in the WEB page?
Regards
Andy Dale
|
|
|
|
|
I have a form which has a button called "Modify Intrest" and when you click it, it brings up a password box and asks for the password. What I wanted was if password was typed in wrong three times, a message to display and then when you click OK, the application to close.
If you type the password in correctly, I want an intrest rate box to display and you have to type in a number and it is written to a file ir.txt
Here is code I have:
Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click<br />
<br />
If txtIntrest.Text = ("") Then<br />
MsgBox("Please Enter Intrest Rate", MsgBoxStyle.Exclamation)<br />
Exit Sub<br />
End If<br />
<br />
<br />
If Not txtIntrest.Text.Equals(myline) Then<br />
<br />
<br />
<br />
Static passnum As Integer<br />
Do<br />
Dim pass As String = InputBox("Enter the Password", "Enter Password", "", )<br />
<br />
<br />
If pass = "Password" Then<br />
<br />
Dim intrest As String = InputBox("Enter The New Intrest", "Enter Intrest Rate", "", )<br />
<br />
txtIntrest.Text = (intrest)<br />
<br />
Dim theFile As FileStream = File.Create("ir.txt")<br />
Dim writer As StreamWriter = New StreamWriter(theFile)<br />
writer.WriteLine(txtIntrest.Text)<br />
writer.Close()<br />
theFile.Close()<br />
Exit Sub<br />
Exit Do<br />
Else<br />
Loop Until passnum = 3<br />
passnum = +1<br />
End If<br />
<br />
MsgBox("You have had three attempts to input password, Program will now close for security reasons", MsgBoxStyle.Critical)<br />
Application.Exit()<br />
<br />
<br />
<br />
<br />
<br />
<br />
End If<br />
<br />
End Sub
I messed it up slightly and not sure how to fix it, any ideas?
In the end we're all just the same
|
|
|
|
|
Dave McCool wrote: Loop Until passnum = 3
passnum = +1
End If
You need to show the password dialog again inside this loop and check if it is entered right. This code doesn't allow anyone to try again.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Thanks, but I am not 100% sure how to do this. I took out the code so it looks like this:
Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click<br />
<br />
If txtIntrest.Text = ("") Then<br />
MsgBox("Please Enter Intrest Rate", MsgBoxStyle.Exclamation)<br />
Exit Sub<br />
End If<br />
<br />
<br />
If Not txtIntrest.Text.Equals(myline) Then<br />
<br />
Dim pass As String = InputBox("Enter the Password", "Enter Password", "", )<br />
<br />
<br />
If pass = "Password" Then<br />
<br />
Dim intrest As String = InputBox("Enter The New Intrest", "Enter Intrest Rate", "", )<br />
<br />
txtIntrest.Text = (intrest)<br />
<br />
Dim theFile As FileStream = File.Create("ir.txt")<br />
Dim writer As StreamWriter = New StreamWriter(theFile)<br />
writer.WriteLine(txtIntrest.Text)<br />
writer.Close()<br />
theFile.Close()<br />
Exit Sub<br />
<br />
End If<br />
<br />
End If<br />
<br />
End Sub
So the password box just closes when the wrong password is input. I want the password box to keep displaying istead of closing and if you type password wrong three times, this code to execute:
MsgBox("You have had three attempts to input password, Program will now close for security reasons", MsgBoxStyle.Critical)<br />
Application.Exit()
but I was told I could do this with a loop, but was not sure how I had code:
Static passnum As Integer<br />
Do<br />
<br />
passnum = +1<br />
Loop Until passnum = 3
but was not sure where to put it so it worked, any ideas?
In the end we're all just the same
|
|
|
|
|
What code can I use to end a VB 6.0 program from the same program or another program?
|
|
|
|
|
From the same program, see http://www.mvps.org/vbvision/visual_basic_tips.htm[^]
From another program you have to send a WM_CLOSE message to the application main window.
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
|
|
|
|
|
I am exporting a dataset to Excel.
I can successfully view the dataset in Excel however, there are
special characters that are appearing in the form of Â, â€" and
others.
My code in VB starts off like this:
Response.Clear()
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition",
"attachment;filename=GeneralInquiry.xls")
I've tried setting the Response.Charset = "" but that doesn't do
anything either.
Would anyone know what could be causing this and/or how to fix it?
Thank you.
|
|
|
|
|
Is it in ASP.NET ?
Try using HtmlEncode and HtmlDecode ? This solved my problem for hyphen character.
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
Please read the forum guidelines. Do not use urgent in your message titles.
|
|
|
|
|
Please don't use words like urgent. It is consider very rude around here.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi forum,
i like to implement my own DataTypes.
Sample:
DIM X as PARTNUMBER
DIM Y as LIST (OF PARTNUMBER) .....
where PARTNUMBER is my own Datatype with special checks, like PARTNUMBER must have 8 digits, first DIGIT must be character etc.
I like to use this own datatypes in the same way as the base types STRING, INTEGER, .....
Questions:
1) Implementation as CLASS or STRUCTURE ?
2) Which interfaces are necessary ?
3) Any samples available ?
regards,
bauer
|
|
|
|
|
Problem solved, using TypeConverter is the solution.
|
|
|
|
|
How to read an XML file, Which is stored as a resource file in a dll using VB 6.0.
DS
|
|
|
|
|
There should be plenty on a google search for this topic.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Dear Sir/Mam,
I have 1 question regarding xml file verification in vb
I have 1 application to create a .xml file in vb but after creating
this file I want to check whether is it in correct format or not?
& show to user whether error occured in that file.
In that I have not a xml schema.
How can I do it?
In advance Thanx
Have a nice time
|
|
|
|
|
kripa ostwal wrote: I want to check whether is it in correct format or not?
& show to user whether error occured in that file.
In that I have not a xml schema.
What do you mean by 'correct'? That the file is well-formed, or that the data structure adheres to your expectations?
Attempting to parse the XML file will determine whether it is well-formed. Checking that the data structure adheres to your expectations will be difficult if you don't have a schema to validate the document against.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
