Posts by Bjorn

  • VBA : formulas with defined names in excel

    Date: 2004-01-09
    Dear VBA experts
    I have run into the following trap:


    I have defined some "names" in excel, for example
    Name location for definition value stated at location
    NameParticipants ='def variable groupA'$E$22 .............with value=Mr. Smith
    NumberParticipants ='def variable groupA'$E$23 .............with value=5
    NumberFactors ='def variable groupA'$E$24.............. with value =0,7
    AmountContribution ='def variable groupA'$E$25...............with value = 45000


    Somewhere in the VBA program, I would like to define
    money= NumberParticipants * NumberFactors * AmountContribution


    ....... and then I would like to print
    Range("A2").Select : ActiveCell.FormulaR1C1 = NameParticipants & " has contributed with " &money
    With the the result ...........Mr. Smith has contributed with 157500


    This does not work !!
    I can print either of the definitions - one at a time, i.e.
    Range("A1").Select : ActiveCell.FormulaR1C1 = "=NameParticipants"........... with result 5
    Range("A2").Select : ActiveCell.FormulaR1C1 = "=NumberParticipants"...........with result 5
    Range("A3").Select : ActiveCell.FormulaR1C1 = "=NumberFactors "...........with result 0,7


    Range("A4").Select : ActiveCell.FormulaR1C1 = "=AmountContribution"...........with result 45000
    but I cannot define or print an arethmetic or alfanumeric combination (with ..&..) of these defined names.


    I neither find it to work with something like the following...
    ='def variable groupA'$E$22 &" has contributed with "& 'def variable groupA'$E$23 * 'def variable groupA'$E$24 * 'def variable groupA'$E$25
    or
    ='=def variable groupA'$E$22 &" has contributed with "& '=def variable groupA'$E$23 * '=def variable groupA'$E$24 * ='=def variable groupA'$E$25


    A ) How Come & what´s wrong???
    B) How to solve this ??
    C) Surely, it must be possible to use defined names in VBA formulas ??


    Very grateful for an answer


    regards Bjorn
    :)

  • Page-break in Words while running in Excel

    Hi,
    Please, can anyone help me with the shortcomings mentioned here about a "page-break" instruction in a Words document in a code when running in Excel.


    In addition, the normal code in Words for MoveUp" does not work, I want to get up into an imported table with data and adjust the characteristics of the table in order to fit the page in Words.


    Thanks in advance for kind assistance
    / bjorn


    This is my very first attempt to seek advice in programming VBA.
    Background: XP professional, Office 2000

    The context & the problem :

    Running a long program in Excel with many macros / coded subs in background.
    The program will result in a lot of reports, typically of 5-25 pages each & with a lot of conditional text created in excel macros / VBA
    I want (must) get print out in Words as such printout is smoother.
    Everything is - be the end of a lot of work - OK, except to make a page-break in the excel routine that runs what to be done in the created word document.
    Some instructions are OK, for example to rearrange margins, others, for example pagehead and pagefoot does not work, but WORST:
    In words, the "instruction" for page break is - .InsertBreak Type:=wdPageBreak - to be put within With wrd (= stands for Words..) and With .selection.
    In words, it works, but when running from Excel, it does NOT work.

    The code under Excel used for this small program sequence is found below
    ' Open a document
    Set wrd = GetObject(, "Word.Application"):


    With wrd:
    With .Selection
    .TypeParagraph
    .TypeParagraph
    .TypeText Text:="This is page 1"
    .TypeParagraph


    .TypeParagraph
    '.Collapse Direction:=wdCollapseEnd
    '.InsertBreak Type:=wdPageBreak ' tried - did not work

    '.InsertBreak Type:=wdSectionBreakContinuous, ' tried - did not work
    .TypeParagraph
    .TypeParagraph
    .TypeText Text:="Some text on page 2"
    .TypeParagraph
    .Sections.Add ' this works, but text instructions after this command IS NOT placed after a page break
    ' .MoveEndUntil Cset:="a ", Count:=wdForward, tried.... did not work
    ' .MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend
    .TypeText Text:="Here should be text, for example to state that we are now on page 2..."
    End With
    End With
    Stop


    Is it possible to have some help on this (trivial ??) matter ?
    Regards Bjorn