VBA formfield help

[Nate H.]

I've been trying to setup a document where the user enters two values (Height
and Width). They also choose a screen resolution from a drop-down form.
From these values, I'm trying to send a 'calculated' pixel size back in to
document. Here's my code. The Height and Width values aren't being assigned
correctly for some reason.

Sub calculateResolution()

Dim dbArea As Double
Dim dbHeight As Double
Dim dbWidth As Double
Dim dbPixelSize As Double
Dim dbPixels As Double

dbHeight = ActiveDocument.FormFields("FOVHeight")
dbWidth = ActiveDocument.FormFields("FOVWidth")
dbArea = dbHeight * dbWidth
dbPixelSize = dbPixels / dbArea


Select Case Selection.FormFields("Resolution").Result
Case "640x480"
dbPixelSize = Width / 640
ActiveDocument.FormFields("PixelSize").Result = dbPixelSize

Case "1024x768"
dbPixelSize = 1024 dbWidth / 1024
ActiveDocument.FormFields("PixelSize").Result = dbPixelSize

Case "1600x1200"
dbPixelSize = dbWidth / 1600
ActiveDocument.FormFields("PixelSize").Result = dbPixelSize

End Select
End Sub

 

[Perry]

From this

Select Case Selection.FormFields("Resolution").Result
Case "640x480"
dbPixelSize = Width / 640
ActiveDocument.FormFields("PixelSize").Result = dbPixelSize

might this line be a typo?

dbPixelSize = Width / 640

shouldn't that read
dbPixelSize = dbWidth / 640

--
Krgrds,
Perry

System:
Vista/Office Ultimate
VS2005/VSTO2005 SE

 

[Nate H.]

Yes, that was a typo. I had the case selection commented out in my test
runs. I'm just trying to get the dbHeight and dbWidth assigned with the
correct values. They aren't 'extracting' the correct value from the document.

I'm not sure if it makes a difference or not, but in the VB Editor I have
the code in 'This Document' of the Microsoft Word Objects folder. Do I need
to have it in the forms folder as a userform (These are in the Project parent
directory)? Should they be in the TemplateProject directory? Dunno, just
throwing out what my setup is like.

Any help would be greatly appreciated.
Nate

 

[Jay Freedman]

Hi Nate,

I can't quite make out what you're trying to get the macro to do, but
I can point out some places that are obviously wrong. Try fixing
those, and if your results are still out of whack then post back and
say what your input values are, what outputs you're seeing, and what
you expect to see.

Further comments in-line below...

I've been trying to setup a document where the user enters two values (Height
and Width). They also choose a screen resolution from a drop-down form.
From these values, I'm trying to send a 'calculated' pixel size back in to
document. Here's my code. The Height and Width values aren't being assigned
correctly for some reason.

Sub calculateResolution()

Dim dbArea As Double
Dim dbHeight As Double
Dim dbWidth As Double
Dim dbPixelSize As Double
Dim dbPixels As Double

dbHeight = ActiveDocument.FormFields("FOVHeight")
dbWidth = ActiveDocument.FormFields("FOVWidth")

Both of these should be in the format

dbXYZ = CDbl(ActiveDocument.FormFields("FOVXYZ").Result)

The .Result property is not the default property of a FormField
object. If you single-step through the code with F8, you'll find that
both of these variables are being assigned the value 70. You _must_
use the .Result property! Even if you did that right, assigning the
..Result directly to a Double relies on VBA's implicit data type
conversion, which could get you into trouble some day. Use the CDbl
conversion function.

dbArea = dbHeight * dbWidth
dbPixelSize = dbPixels / dbArea

Since you never assign a value to dbPixels, it's initialized to 0, and
then dbPixelSize = 0 at this point. That doesn't much matter because
this assignment of dbPixelSize will always be replaced by another one
in the Select Case.

Select Case Selection.FormFields("Resolution").Result
Case "640x480"
dbPixelSize = Width / 640

There is no declared variable named Width -- this should be dbWidth.
That's the trouble with not using the Option Explicit statement. See
http://word.mvps.org/FAQs/MacrosVBA/DeclareVariables.htm.

ActiveDocument.FormFields("PixelSize").Result = dbPixelSize

Case "1024x768"
dbPixelSize = 1024 dbWidth / 1024

This statement is nonsense and should have gotten a compile-time
syntax error. Take out the first '1024'.

ActiveDocument.FormFields("PixelSize").Result = dbPixelSize

Case "1600x1200"
dbPixelSize = dbWidth / 1600
ActiveDocument.FormFields("PixelSize").Result = dbPixelSize

End Select
End Sub

Finally, as a "purist" comment, using Double variables is overkill.
You'll get exactly the same answers with half the memory use if you
change to using Single variables. This won't make any practical
difference, but I just dislike waste.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Nate H.]

Thank you very much Jay. The CDbl function did the trick! As for my
mistakes in the Case Selection, I had commented those out at first trying to
get the variables assigned with the correct values in the first portion of
the code.

In the case:
dbPixelSize = 1024 dbWidth / 1024
I had dbWidth / 1024 commented out. I was looking to see if dbPixelSize
would spit back 1024. It did, but I posted a 'messy' question and failed to
delete some of the code.

I'll see what happens with the rest of the document. I have some other
forms to create.

Thanks for the help Jay, your time is appreciated. Here's the code now
(with Singles ).

Sub calculateResolution()

Dim sArea As Single
Dim sHeight As Single
Dim sWidth As Single
Dim sPixelSize As Single
Dim sPixels As Single

sHeight = CDbl(ActiveDocument.FormFields("FOVHeight").Result)
sWidth = CDbl(ActiveDocument.FormFields("FOVWidth").Result)

sArea = sHeight * sWidth

Select Case Selection.FormFields("Resolution").Result
Case "640x480"
sPixelSize = sWidth / 640
ActiveDocument.FormFields("PixelSize").Result = sPixelSize

Case "1024x768"
sPixelSize = sWidth / 1024
ActiveDocument.FormFields("PixelSize").Result = sPixelSize

Case "1600x1200"
sPixelSize = sWidth / 1600
ActiveDocument.FormFields("PixelSize").Result = sPixelSize

End Select
End Sub

 

[Jay Freedman]

Hi Nate,

I'm glad that helped. Just a comment on the new code: In case it's exactly
what you have in your form and not another typo, you aren't assigning a
value to sPixelSize, so it will always be zero. Since you also aren't
assigning a value to sPixels and you aren't doing anything with sArea after
you calculate it, I suspect you're missing a statement or two.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.