0

I'm creating a macro form in Word VBA that allows users to enter a week and a weight value which will calculate a percentile for them. I'm building this off of a table from a book that looks like this (Sorry, I'm not sure how to add tables to Stack Overflow questions yet):

Percentile 10th 50th 90th

Week1........3....12....34

Week2........5....17....39

Week3........8....21....42

Currently my VBA code looks like this:

If Week = 1 then
   If Weight < 3 then
   Selection.TypeText "less than 10th percentile for age"
   ElseIf Weight = 3 then
   Selection.TypeText "10th percentile for age"
   ElseIf Weight < 12 then
   Selection.TypeText "less than 50th percentile for age"
   ElseIf Weight = 12 then
   Selection.TypeText "50th percentile for age"
   ElseIf Weight < 34 then
   Selection.TypeText "less than 90th percentile for age"
   ElseIf Weight = 34 then
   Selection.TypeText "90th percentile for age"
   ElseIf Weight > 34 then
   Selection.TypeText "greater than 90th percentile for age"
   End If
ElseIf Week = 2 then
   If Weight < 5 then
   Selection.TypeText "less than 10th percentile for age"
...

The table is actually much bigger than 3 columns and 3 rows, so you can imagine this is very tedious. The table itself is not to be visible to the user, but coded behind the scenes. I'm wondering if instead of typing out all the code like I've done above, if there's a way to add a table to this so that future maintenance of the values is more straightforward. Thank you!

I'm using Word 2016.

hal
  • 1
  • 1
    Data structures in VBA are certainly possible. You can have [arrays](https://www.tutorialspoint.com/vba/vba_arrays.htm) and even [dictionaries](https://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure). – Robert Harvey Dec 05 '18 at 19:09
  • @RobertHarvey Of course, that would still mean typing things out... – Cindy Meister Dec 05 '18 at 19:23
  • Do you propose generating this array somehow? – Robert Harvey Dec 05 '18 at 19:24
  • I'm not really sure what you have in mind, since any solution will involve typing in the data that's currently in a book... Keep the data in an Excel table or a database that VBA can read? Type it into a Word table? Put it in an XML file that can be imported to a Custom XML Part so that the data travels with the document (or template)? You don't really provide enough information to give you good advice... – Cindy Meister Dec 05 '18 at 19:25
  • I don't mind typing out the DIM ARR stuff since that would keep all the numbers in one spot, and the comparisons can be done hopefully with just one chunk of code instead of code for every single week. However, How would I make comparisons to the array? – hal Dec 05 '18 at 19:30
  • @hal Imo, the easiest way to do this would be to have a separate Excel file where on the main worksheet, you have your 52 weeks or so written out in columns A through C (or B through D, if that's easier). Then when you've gotten your week #, you open that file and pull your 3 numbers that you need in. – dwirony Dec 05 '18 at 19:41
  • I'm a little hesitant to start with the excel file if I can just Dim and array. I Imagine I'd start with "Dim Arr(2,3) as Percentile", and store each week number as the arr(x,0) value as "1", "2", "3") and each weight number as the arr(x,1), arr(x,2), and arr(x,3) values. To first identify the correct week in my comparisons, is there a wild-card I'd need to use for the first dimension in the array? – hal Dec 05 '18 at 20:46
  • @hal Keeping the data in an external file makes it much easier to update when your criteria change; it doesn't need to be an Excel workbook, though - a plain text file with tabs between 'columns' and paragraph breaks between 'rows' would do. – macropod Dec 06 '18 at 00:39

0 Answers0