Making this code more efficient.... 3

[DodgyDavo]

Hi guys, bit of a n00b when it comes to VBA but i had a dabble and created a wizard for a standard letter for Word. It works fine but the code is rather long winded. Could anyone help me streamline this code please...

Private Sub CMDok_Click()
Selection.GoTo what:=wdGoToBookmark, Name:="companyname"
Selection.TypeText TXTcompname.Text
Selection.GoTo what:=wdGoToBookmark, Name:="address1"
Selection.TypeText TXTaddress1.Text
Selection.GoTo what:=wdGoToBookmark, Name:="address2"
Selection.TypeText TXTaddress2.Text
Selection.GoTo what:=wdGoToBookmark, Name:="address3"
Selection.TypeText TXTaddress3.Text
Selection.GoTo what:=wdGoToBookmark, Name:="Postcode"
Selection.TypeText TXTpostcode.Text
Selection.GoTo what:=wdGoToBookmark, Name:="FAOtitle"
Selection.TypeText CMBOXtitle.Text
Selection.Font.Name = "Arial"
Selection.Font.Size = 11
Selection.Font.Bold = False
Selection.GoTo what:=wdGoToBookmark, Name:="FAOname"
Selection.TypeText TXTfaoname.Text
Selection.Font.Name = "Arial"
Selection.Font.Size = 11
Selection.Font.Bold = False
Selection.GoTo what:=wdGoToBookmark, Name:="subject"
Selection.TypeText TXTsubject.Text
Selection.GoTo what:=wdGoToBookmark, Name:="signoff"
Selection.TypeText CMBOXsignoff.Text
Selection.GoTo what:=wdGoToBookmark, Name:="sendername"
Selection.TypeText TXTsendername.Text
Selection.GoTo what:=wdGoToBookmark, Name:="Ourref"
Selection.TypeText TXTourref.Text
Selection.GoTo what:=wdGoToBookmark, Name:="Yourref"
Selection.TypeText TXTyourref.Text
Selection.GoTo what:=wdGoToBookmark, Name:="sendertitle"
If CMBOXsendertitle.Text = "Other" Then
Selection.TypeText TXTothertitle.Text
Else
Selection.TypeText CMBOXsendertitle.Text
End If
If CHKBXencs.Value = "True" Then
Selection.GoTo what:=wdGoToBookmark, Name:="Enclosements"
Selection.TypeText Text:="encs."
Else
Selection.TypeText Text:=""
End If
Selection.GoTo what:=wdGoToBookmark, Name:="Date"
Selection.InsertDateTime DateTimeFormat:="dd MMMM, yyyy", _
InsertAsField:=True
Frminformation.Hide
End Sub

Seems a bit much for me but if thats the best its going to get then ill live with it

Thanks in advance

Dave

 

[Chance1234]

HI when im dealing with bookmarks i normally do the following,


give all my bookmarks the same name with a cnt so for example

TEST1
TEST2
TEST3
TEST4
TEST5


etc etc then in my code

Sub pleasecheckoutmylinks() 
Dim str_bk as string 
Dim int_cnt as integer

int_cnt = 1 

do until int_cnt = 5 
      str_bk = "TEST" & int_cnt 
      ActiveDocument.Bookmarks(str_bk).Range.Text = "BOB"
      int_cnt = int_cnt + 1 
loop 

end sub

note the above will put "bob" at every bookmark, normally what i do is i match the data going in with my cnt so

i might have in addition to the above

Dim str_file(5) as string 
str_file(1) = "Got" 
str_file(2) = "A" 
str_file(3) = "Damm Working" 
str_file(4) = "working lunch meeting" 
str_file(5) = "damm it"

then i would change the line to

    ActiveDocument.Bookmarks(str_bk).Range.Text = str_file(int_cnt)

note this is extremly fast and is the fastest method i have come across.



Filmmaker, gentleman and i have a new site half working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[MakeItSo]

Hi Dave,

another way to do it is to fill arrays and cycle through them. With Chance's proposal of sequential numbering of the bookmarks (a really good proposal!), the solution for e.g. 10 bookmarks could be:

Dim myVals as Variant
Dim i as Integer

myVals=Array(TXTcompname.Text,TXTaddress1.Text,...)
For i=1 to 10
 str_bk = "TEST" & i 
 ActiveDocument.Bookmarks(str_bk).Range.Text = myVals(i-1)
Next i

Plus: One of the crucial points that make Chance's way so fast is that you don't actually GoTo the bookmark, but you assign the value directly. --> No flickering, no scrolling, just plain speed!

Hope that helps,
Andy

[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de

 

[Loomah]

Hi
I'm not too sure about bookmarks (and Word VBA in general) but this should work as a (slightly) shortened version of what you're starting with.

If you can understand what's happening I think I'd go with what Chance has suggested. The only problem I'd have with it is knowing what each bookmark is but I could live with that!!

My code is only here now coz I bothered my a$$ to do it!! It'll be quicker than what you had but only because you're not selecting everything.

Private Sub CMDok_Click()
With ThisDocument
    .Bookmarks("companyname").Range.Text = TXTcompname.Text
    .Bookmarks("address1").Range.Text = TXTaddress1.Text
    .Bookmarks("address2").Range.Text = TXTaddress2.Text
    .Bookmarks("address3").Range.Text = TXTaddress3.Text
    .Bookmarks("Postcode").Range.Text = TXTpostcode.Text
    With .Bookmarks("FAOtitle").Range
        .Text = CMBOXtitle.Text
        .Font.Name = "Arial"
        .Font.Size = 11
        .Font.Bold = False
    End With
    With .Bookmarks("FAOname").Range
        .Text = TXTfaoname.Text
        .Font.Name = "Arial"
        .Font.Size = 11
        .Font.Bold = False
    End With
    .Bookmarks("subject").Range.Text = TXTsubject.Text
    .Bookmarks("signoff").Range.Text = CMBOXsignoff.Text
    .Bookmarks("sendername").Range.Text = TXTsendername.Text
    .Bookmarks("Ourref").Range.Text = TXTourref.Text
    .Bookmarks("Yourref").Range.Text = TXTyourref.Text
    With .Bookmarks("sendertitle").Range
        If CMBOXsendertitle.Text = "Other" Then
            .Text = TXTothertitle.Text
            Else
            .Text CMBOXsendertitle.Text
        End If
    End With
    With .Bookmarks("Enclosements").Range
        If CHKBXencs.Value = "True" Then
            .Text = "encs."
            Else
            .Text ""
        End If
    End With
    .Bookmarks("Date").Range.InsertDateTime _
        DateTimeFormat:="dd MMMM, yyyy", _
        InsertAsField:=True
    Frminformation.Hide
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[DodgyDavo]

Thanks a bunch guys.

@Chance1234: I understand how you did the code and although dont have time to streamline it that much (ill need to start again to get it right) i understand how you did it and will be using that method for a few more pieces of code in the future

@Makeitso: I looked at arrays and the examples that it shows on how to use them in the help file and it baffled me completely. Although attributing it to what i am doing has greatly helped me understand how the array works. I dont mean to be picky but....shouldnt the str_bk be declares as Dim str_bk as string?? I was trying to go through step by step and noticed it.

@Loomah: I also looked at with statements and was about to attempt it before i posted here. The code there has increased the speed a lot in the execution of the program and i have also attributed to other parts of the program like filling list boxes as the form initialises. Also noticed the:

Else
.Text CMBOXsendertitle.Text
End If

and

Else
.Text ""
End If

The = are missing from both of those but a minor noticeability as it has heped me so much.

Muchos Thanks guys

Dave

 

[DodgyDavo]

Also

With .Bookmarks("FAOtitle").Range
        .Text = CMBOXtitle.Text
        With .Font
             .Name = "Arial"
             .Size = 11
             .Bold = False
        End With
    End With
    With .Bookmarks("FAOname").Range
        .Text = TXTfaoname.Text
        With .Font
             .Name = "Arial"
             .Size = 11
             .Bold = False
        End With
    End With

Is taking the WITH statements 1 step further but im just trying to demonstrate that im trying to understand what u have done and not come in here for a quick fix )

Dave