Any suggestions or guidelines with using Class Modules in VBA for MS Word / Excel - Visual Basic Discussion Boards

doh! Yuppers!

"There's no such thing as a stupid question, only stupid people." - Mr. Garrison

Wow thanks for the reponse. I think I was going wrong as I was under the impresion that when you mark the row for deletion the acceptchanges removes it from the dataset then the update syncsthe source with the dataset. now I know! and yes, the tableadapter doesn't have a valid delete command.

thanks alot!

chris

Tha'ts why we have documentation on nearly every class, method and property in the .NET Framework. Just looking at a method name and trying to guess at what it does isn't doing you any favors.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

very true, but that was the impression I got from the documentation! Oh well, still very new to this. Here's how I fixed it for peoples reference:

Private Sub Deleteip_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Deleteip.Click
Dim item, port As Integer
Dim ip As String
item = IPlistbox.SelectedIndex
ip = DataSet.IP.Item(item).IPaddress
port = DataSet.IP.Item(item).Port
DataSet.IP.Rows(IPlistbox.SelectedIndex).Delete()
DataSet.IP.AcceptChanges()
IPTableAdapter.DeleteQuery(ip, port)
End Sub

Hi,

In am doing the simultaneouly two imports. Which export the data from excel to sql server.
There are some common tables, which are being approached by both these imports. and results in deadlock
Transaction (Process ID 53) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In the sql profiler, i have seen that a couple of queries are taking time, which are using common tables in imports. I have optimized it also.

Can you provide me any suggestion?

Thanks

Um...Don't do the imports simultaneously?

Do some kind of check to make sure the files not being used by another process before doing the import.

This would tell me that you can't do the imports simultaneously since they both add/modify to the same table(s).
You don't have a choice but to do them sequentially.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

Hi there,

This would be my first post that is basically asking/requesting points of view from various Bloggers/experts from this site whom I welcome to comment on coding principles rather than talking about code specific. Big Grin | :-D

Some background information to introduce this subject, basically I am working on a project that simplifies automating a summary form in MS Word that involves in several processes along the way. It has text forms fields and few check boxes that is associated to several bookmarks.

I can use VBA code to collect information from these bookmarks and make control structure decisions given input from users. I have a drop down lists that I will have to use MS Access instead of an array code to fill them up as I was shocked to find VBA telling me that 25 continuation lines is the maximum when I wanted to fill an array for over 120 items. bummer! WTF | :WTF:

Anyway, I know about object oriented programming and have seen the benefits, especially from books on Word / Excel VBA suggesting a Class Module using examples of employees with time sheet objects.

Trouble is, I couldn't confidently use it but I wanted to so I can organise the forms like objects and put the results into a collection like Word / Excel organises data. I tend to be cautious as I used to write code like I would write COBOL structured programming (I used to do this mind you!)

I would use those examples provided from books to develop my own patch of code that achieves a sort of re-factoring about approximately 40 lines of VBA module code when the user clicks the button that starts the module.

Let's give a fictional example that give you an idea or essence of my module project at work to show you what I mean and I wanted to re-factor these ideas from this CommandButton1 module into a Class Module that simplifies duplicate code.

Please forgive me for lacking any comments in the code.

Here are my questions - What's the best approach in converting this into a VBA class module?

What guidelines would people offer from their experience into turning modules into class modules?

This code works when a button on the form is pressed after entering information into three fields in Word.

<br />
Sub CommandButton1_Click()<br />
Dim objA as Document<br />
Dim strB as String<br />
Dim strC as String<br />
Dim strD as String<br />
Dim blnB as Boolan<br />
Dim blnC as Boolan<br />
Dim blnD as Boolan<br />
<br />
Set objA = ActiveDocument<br />
<br />
blnB = false<br />
blnC = false<br />
blnD = false<br />
<br />
strB = objA.FormField("FieldA").Result<br />
<br />
if strB = "Somebody" then<br />
blnB = True<br />
<br />
strC = objA.FormField("FieldB").Result<br />
<br />
if strC = "Anybody" then<br />
blnC = True<br />
<br />
strD = objA.FormField("FieldC").Result<br />
<br />
if StrD = "Otherbody" then<br />
blnD = True<br />
<br />
If blnB then<br />
  if blnC then<br />
     if blnD then<br />
       Save this file<br />
     else<br />
       Msgbox "Error entering Field C"<br />
     end if<br />
  else  <br />
    msgbox "Error enter Field B"<br />
  end if<br />
else<br />
  msgbox "Error enter Field A"<br />
end if<br />
  <br />
End Sub<br />

Regards, Psionic

<pre>
Hello ...

Problem :
problem regarding sending mail through CDO using Excel macro
For some attempts i sucussful sent mails with attachment but suddenly it droped. I dont know what was the problem but I think none of the errors were observed during the process.
More over I am not able to find mail in my inbox to which i had configured in the macro.

My system configuration is   -         Win XP Sp3 o/s &amp; working on Office 2003

Excel VBA Code is as follows....

Option Explicit

Private Sub CommandButton1_Click()

Dim objMsg As Object
Dim objConf As Object
Dim objFlds As Object
Dim strBody As String

Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2 'Must use this to use Delivery Notification
Const cdoAnonymous = 0
Const cdoBasic = 1 ' clear text
Const cdoNTLM = 2 'NTLM
'Delivery Status Notifications
Const cdoDSNDefault = 0 'None
Const cdoDSNNever = 1 'None
Const cdoDSNFailure = 2 'Failure
Const cdoDSNSuccess = 4 'Success
Const cdoDSNDelay = 8 'Delay
Const cdoDSNSuccessFailOrDelay = 14 'Success, failure or delay

Set objMsg = CreateObject("CDO.Message")
Set objConf = CreateObject("CDO.Configuration")

objConf.Load -1
Set objFlds = objConf.Fields
With objFlds
   .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
   .Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587 '465
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
   .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
   .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "mailserver4macro@gmail.com"
   .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "jose309930"
   .Update
End With

strBody = "This is a sample message." & vbCrLf
strBody = strBody & "It was sent using CDO." & vbCrLf

With objMsg
   Set .Configuration = objConf
   .To = "hai_jimson_jose@yahoo.com"
   .From = "mailserver4macro@gmail.com"
   .Subject = "Hai jimson!!   This is a CDO test message"
   .TextBody = strBody
   'use .HTMLBody to send HTML email.
   '.Addattachment "c:\temp\Scripty.zip"
   .Fields("urn:schemas:mailheader:disposition-notification-to") = "mailserver4macro@gmail.com"
   .Fields("urn:schemas:mailheader:return-receipt-to") = "mailserver4macro@gmail.com"
   .DSNOptions = cdoDSNSuccessFailOrDelay
   '.Fields.Update
   .Send
End With
End Sub
</pre>

Ew.

Check out the CodeProject forum Guidelines[^]

Hello everybody,

I hope someone can help me with a weird kind of home-made newbie problem...

I've been trying around with different solutions to create Outlook-Add-Ins (with VB Express only), which lead to installing various samples and testing them. Some had to be registered manually, with *.reg files as well as with regsvr32, others with regasm. Having found a way that works for my purpose, I tried to go back to my clean Outlook and found that I must have missed to uninstall any of the samples - Outlook shows a custom "MyBar" command with a "My Button" commandbutton, and I'm just unable to trace its origin $Shucks | :-\$ .

Resetting the standard bar in Outlook doesn't help, the custom bar is installed at each startup. And searching the registry didn't lead to any new insights since the different ...\outlook\AddIns keys seem to be all empty already. Also, there is no relevant reference or add-in listed in Outlook.

Does anyone of you know how Confused | :confused:

I can find out where that button came from? From where would Outlook load that UOO (= unidentified outlook object Cool | :cool:

)?

Thank you in advance,
Michael

In Outlook 2003, and I think 2007, it's under Tools -> Trust Center -> Add-ins tab.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

Hi Dave,

unfortunately I'm using Outlook XP in German... but I guess what you mean would be Tools --> Options --> Extended --> COM Add-Ins, where I had expected to find the path to a dll myself. But too sad: There's nothing there as well as in the References and Add-Ins of the VBIDE.

Meanwhile I found the responsible sample project, and it's an OLDER Microsoft KB sample written in C++:
COMADDIN.EXE (http://support.microsoft.com/download/support/mslfiles/COMADDIN.EXE)

Having no clue about C++ I obviously managed to compile the code (more or less by chance), and I'm afraid the sample produced any registry (or other?) system entries which outlook happens to load without showing it as an add-in... Maybe it's also important that the sample was made for OL2000.

Are you able to read C++ and have a look at the sample? I'm still optimistic that there must be a way to remove the "MyBar", but the task is just far beyond my limits WTF | :WTF:

Hoping for your help,
sincerely
Michael

You might want to try the C++ forum for that.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

Hi All,
How Can I Create digital sign for my application?

Need help.

Thanks

If you can think then I Can.

What do you mean ?

Christian Graus

Driven to the arms of OSX by Vista.

Please read this[^] if you don't like the answer I gave to your question.

"! i don't exactly like or do programming and it only gives me a headache." - spotted in VB forums.

In vista Microsoft Defender shows my application is shows
Digitally Signed By: NOT SIGNED

Microsoft application shows it
Digitally Signed By: Microsoft Windows Verification PCA

so how to set digital sign for my application.

If you can think then I Can.

You have to purchase a certificate from a third party company. Try googling Thawte or Verisign and taking a look at their websites. They have a lot more information on the topic.

I am trying to built the certificate for my softwares. Not Certified by Verisign or Microsoft CA etc.

We are developer pls give me the way for developing that.

Thanks

If you can think then I Can.

You have to purchase a digital signature from a certificate authority such as Verisign or Microsoft CA. You cannot develop it in-house if you want it to be certified by Microsoft. This is good; if virus-writers could do this, they could masquerade as a Microsoft-certified application. They would be trusted by users and allowed to run all over the system

Between the idea
And the reality
Between the motion
And the act
Falls the Shadow

Thanks for Help.

If you can think then I Can.

Hello everyone!

If we change the language from Regional Settings of Windows XP. Most specifically changing "preferred language" under standards & formats tab of Regional Settings dialog.

Now tell whether VB6.0 uses the same "User Default Locale ID" language for running applications using IDE? As When I run my application from Visual Studio 6.0 I get results in User Default Locale ID language or preferred language (as stated in first paragraph above). But after creating EXE of the application I get System Default UI Language.

Please tell me the standard rule VB6.0 uses.

Thanks.
Be Happy.

modified on Wednesday, June 3, 2009 2:43 AM

hi
i am using microsoft office (old version) excel visual basic editor.

currently i am stuck as i am trying to change the events of my form.
like form load, (top right corner of the form) close button and such

does anyone know how this can be done>?

as i understand in visual studio and such it is possible.

You cannot change the events inside Office. You just have to use what's exposed and understand that there is vast differences between Form events in a VB6 or VB.NET app and the events you find for UserForms in Office.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007, 2008

is there a way to disable it then? or attach an additional event to the close button?