• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
Worf

Office scripts, VBA and a text pivot table

Excel Version
  1. 365
One way to display text in the values area of a pivot table is to use conditional formatting with a custom number format. Unfortunately, Excel scripts currently cannot apply this kind of feature to pivot tables, only to ordinary cells.
On the other hand, good old VBA can do it. So, this is a hybrid project using these two different languages. Here are the main points:

  • It is done with a single click because the VBA code is event driven and will automatically run when the TypeScript code activates the sheet.
  • It starts from scratch, generating a normalized table that will be pivot table source data.
  • The two numerical limits are written to the worksheet making them available to the VBA code.
  • The pivot table is created, and a copy of its contents is placed at another location. This regular range will receive conditional formatting to show text instead of numbers, if a condition is met.
  • The VBA code applies conditional formatting to the real pivot table. We can have the whole values area with text, or only part of it.

cond.png

JavaScript:
interface tbl{tn:string}
interface ptbl extends tbl{lr:ExcelScript.Range}
interface cod { bg: string, sm: string }
function main(workbook: ExcelScript.Workbook) {
let sheet2 = workbook.getWorksheet("sheet2")
const headers=["region","employee","amount","reviewed"]
const reg=["central","east","west","north"]
const emp=["Elaine","Tom","Paula","Greg"]
const rev=["yes","no"] as const
sheet2.getRange("f3:i3").setValues([headers])
let r=sheet2.getRange("f4")
r.setValue(reg[Math.round( Math.random()*3)])
for (let i = 0; i < 25; i++) {
  r.getOffsetRange(i, 1).setValue(emp[Math.round(Math.random() * 3)])
  r.getOffsetRange(i, 0).setValue(reg[Math.round(Math.random() * 3)])
  r.getOffsetRange(i,2).setValue(Math.random()*100)
  r.getOffsetRange(i, 3).setValue(rev[Math.round(Math.random() )])
  }
const p = workbook.getWorksheet("Pivot");
const f = p.addPivotTable("Pivot2", /* The name of the PivotTable. */
sheet2.getRange("g5").getSurroundingRegion(), p.getRange("e5") /* The location to put the new PivotTable. */);
f.addRowHierarchy(f.getHierarchy("region"));
f.addRowHierarchy(f.getHierarchy("employee"));
f.addDataHierarchy(f.getHierarchy("amount"));
f.addFilterHierarchy(f.getHierarchy("reviewed"));
let cl = f.getLayout().getColumnLabelRange();
let s=f.getLayout().getRange()
let tl=p.getRange("z5")
tl.getAbsoluteResizedRange(30, 5).clear(ExcelScript.ClearApplyTo.all)
tl.copyFrom(s)
tl.getSurroundingRegion().getFormat().autofitColumns()
const pi={tn:f.getId(),lr:f.getLayout().getFilterAxisRange()} as ptbl
if(infot(pi)){console.log(pi.lr.getAddress())}
let dr = f.getLayout().getBodyAndTotalRange();
let arr:number[]=new Array() 
reg.push("Grand Total" as const)
let hc=f.getRowHierarchies()
hc.forEach((v,i)=>{
  console.log(i+") "+ v.getName())
})
let rt=tl.getSurroundingRegion()
let nc=rt.getColumn(1)
nc.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
tl.setRowHidden(true)
for (let i=0;i<reg.length;i++){
  let res=rt.find(reg[i], { completeMatch: true });
  res.setRowHidden(true)
  arr.push(res.getRowIndex()+1) 
}
const fc = nc.getSpecialCells(ExcelScript.SpecialCellType.visible);
let lim={bg:"150",sm:"80"} as cod
let ar=fc.getAreas() 
let bign=glim(lim,"bg")
let sml=glim(lim,"sm")
p.getRange("a1").setValue(bign)
p.getRange("a2").setValue(sml)
for(let i=0;i<fc.getAreaCount();i++){  
  CF(ar[i], "lightgreen", ">" + bign, '[> ' + bign + ']"big";General')
  CF(ar[i], "orange", "<"+sml, '[< ' + sml + ']"small";General')
}
sheet2.activate()
sleepy(2)
p.activate()
console.log("I am back.")
let ir = p.getRange("e1:f1")
ir.merge();
ir.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
ir.getFormat().getFont().setName("Century")
ir.getFormat().getFont().setSize(16)
}
function infot(v: tbl): v is ptbl {
return "lr" in v
}
function glim(L:cod, k:keyof typeof L ){
  return L[k]
}
function sleepy(sec: number): void {
  const wu: number = new Date().getTime() + sec * 1000;
  while (new Date().getTime() < wu) {
        // do nothing
  }
}
function CF(r:ExcelScript.Range,clr:string,fp:string,nf:string){
let pos2 = r.addConditionalFormat(ExcelScript.ConditionalFormatType.custom).getCustom();
pos2.getFormat().getFill().setColor(clr);
pos2.getFormat().getFont().setBold(true);
pos2.getRule().setFormula(`=${r.getCell(0, 0).getAddress()}` + fp);
pos2.getFormat().setNumberFormat(nf)
}
VBA Code:
Private Sub Worksheet_Activate()
Dim pv As PivotTable, r, i%, f, big$, small$
big = CStr(Me.[a1])
small = CStr(Me.[a2])
Me.[e3:g100].ClearFormats
Set pv = Me.PivotTables(1)
Set r = pv.DataBodyRange.SpecialCells(xlCellTypeVisible)
pv.DataBodyRange.HorizontalAlignment = xlCenter
For i = 1 To r.Areas.Count
    f = Replace(Split(r.Areas(i).Address, ":")(0), "$", "")
    r.Areas(i).FormatConditions.Add xlExpression, , "=" & f & ">" & big
    r.Areas(i).FormatConditions(1).NumberFormat = "[>" & big & "]""big"";General"
    r.Areas(i).FormatConditions(1).Interior.Color = RGB(5, 250, 5)
    r.Areas(i).FormatConditions.Add xlExpression, , "=" & f & "<" & small
    r.Areas(i).FormatConditions(2).Interior.Color = RGB(225, 160, 40)
    r.Areas(i).FormatConditions(2).NumberFormat = "[<" & small & "]""small"";General"
Next
Me.Rows("1:30").EntireRow.Hidden = False
Application.Wait Now + #12:00:01 AM#
End Sub
Author
Worf
Views
304
First release
Last update

Ratings

0.00 star(s) 0 ratings

More Excel articles from Worf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top