Announcement

Collapse
No announcement yet.

Putting formulae into excel from Power Basic

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Putting formulae into excel from Power Basic

    For many years I've done all the additions, multiplications etc in PB and just fed the results into excel spread sheets without any formula in the excel sheets.
    I needed to put the formula into some of the excel reports for other users to populate.
    I followed the usual path of creating a macro in an excel spread sheet to see the VBA code format and then adapted this to produce my PB code.

    In the example below I'm looping thro' a listview with X rows and creating an excel sheet from that.
    Once the cell range is defined the count for formula elements is in the square brackets e.g -2 and -1 for the addition formula.

    I hope this may assist other forum users.


    Code:
          ' A typical macro from EXCEL for addition
          ' Range("A4:D4").Select
          ' Range("D4").Activate
           ' ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
           ' Range("A4:D4").Select
    
      'Putting formula into Excel ADDITION
      'add cells 10 and 11 and put into 12 for each row
      'for each row (X) add the contents of cells J (10)and K (11) into L (12)
          LET vRange = ("L"+TRIM$(STR$(X)))
          LET vVnt5 =  "=SUM(RC[-2]:RC[-1])"     'cells  -1 and -2 from the reference L (12)
         OBJECT LET oExcelWorkSheet.Range(vRange).Cells.FormulaR1C1 = vVnt5
    
    
            'Putting formulae into Excel MULTIPLICATION
            'Mutilply cells D (4) and L (12) and put into  M (13)  for each row
          LET vRange = ("M"+TRIM$(STR$(X)))
          LET vVnt5 =  "=SUM(RC[-9]*RC[-1])"    'cells  -1 and -9 from the reference M (13)
       OBJECT LET oExcelWorkSheet.Range(vRange).Cells.FormulaR1C1 = vVnt5
    “Oh wad some power the giftie gie us To see oursel's as others see us! It wad frae monie a blunder free us, And foolish notion”

    Robert Burns (1759-96)

  • #2
    I had reason to revisit this today to remember how to place formula in when running an excel report from PB

    'From VB Macro adding vertical cells

    Range("L6:L12").Select
    Range("L12").Activate
    ActiveCell.FormulaR1C1 = "=SUM(R[-6]C:R[-1]C)"
    Range("L6:L12").Select

    EXCR = is excel current row

    ' Adding up a vertical column of values in Colum L of excel
    ' Adds cells from -EXCR& to -1 from the reference cell L:STR$(EXCR&+6)
    ' and puts this total in cell L:STR$(EXCR&+6)
    '


    Code:
    LET vRange = ("L"+TRIM$(STR$(EXCR&+6)))
    LET vVnt5 =  "=SUM(R[-"+TRIM$(STR$(EXCR&))+"]C:R[-1]C)"                          
    OBJECT LET oExcelWorkSheet.Range(vRange).Cells.FormulaR1C1 = vVnt5
    “Oh wad some power the giftie gie us To see oursel's as others see us! It wad frae monie a blunder free us, And foolish notion”

    Robert Burns (1759-96)

    Comment


    • #3
      Have a look at http://libxl.com/
      They have a wrapper for the library for use with PowerBASIC.
      /Fim W.
      Fim Wästberg

      Comment

      Working...
      X