• 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

TypeScript, VBA, and the Graham Scan

Excel Version
  1. 365
Typescript is the language of Excel scripts, available at certain Office 365 licenses. Unfortunately, my company chose to disable this feature, so at this time I cannot use it.
However, we can write TS outside of Excel. This article shows how to output TS data to a text file that Excel can read.
The code finds the convex hull for a bidimensional set of points; read more about the Graham algorithm in the link below.
Here are the main features of this project:

  • The points can be code generated or read from file.
  • The results are written to a text file so that they can be retrieved by other applications.
  • I used Visual Studio to edit the TS code, the TS Compiler to generate the corresponding JavaScript and finally Node to run it.
  • A VBA macro imports the data and creates a chart to visually represent the solution.

graham.png


VBA Code:
Sub Graham()
Dim ob As Shape, rng As Range, sht As Worksheet, lr%, fp%
Workbooks.OpenText Filename:="C:\Users\en_sa\helloworld\example.txt", Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), TrailingMinusNumbers:=True
Set sht = ActiveSheet
Set rng = sht.Columns("a:a").Find("end", LookIn:=xlValues)
fp = Split(rng.Address, "$")(2)
lr = Evaluate("=MAX((B:B<>"""")*(ROW(B:B)))")
Range(Cells(fp + 1, 1), Cells(fp + 1, 2)).Copy Cells(lr + 1, 1)
Set ob = sht.Shapes.AddChart2(240, xlXYScatterLines)
ob.Chart.SetSourceData Source:=Range("example!$A$1:$B$" & (fp - 1))
Application.CutCopyMode = False
ob.Chart.SeriesCollection.NewSeries
With ob.Chart.FullSeriesCollection(2)
    .Name = "=""sec"""
    .XValues = "=example!$A$" & (fp + 1) & ":$A$" & lr + 1
    .Values = "=example!$B$" & (fp + 1) & ":$B$" & lr + 1
    .ChartType = xlXYScatterLines
End With
With ob.Chart
    .FullSeriesCollection(1).ChartType = xlXYScatter
    .Axes(xlValue).MinimumScale = -1
    .Axes(xlCategory).MaximumScale = 11
    .HasTitle = False
End With
End Sub

JavaScript:
interface point {
    x:number
    y:number
    angle:number
    discarded:boolean
    zero:boolean
}
import { readFileSync, writeFileSync } from 'fs';
import { join } from 'path';
let st=""
let xarr:number[]=[]
let yarr:number[]=[]
const ipt:string="file"   // data from file or not
if(ipt==="file"){reader()}
else{
for(let i=0;i<35;i++){
    xarr[i]=Math.random()*10
    yarr[i]=Math.random()*10
    st=st+xarr[i]+"\t"+yarr[i]+"\n"
}
}
st=st+"end\n"
let points:point[]=[]
for (let i = 0; i < xarr.length; i++) {
    const el: point = {
        x: Number(xarr[i]),
        y: Number(yarr[i]), angle:0 ,
        discarded: false, zero:false
    };
    points.push(el)
}
var vsort = (a: point,b: point) => {
    if (b.y < a.y) return 1;
    if (b.y > a.y) return -1;
    else {
        if (b.x < a.x) return 1;
        if (b.x > a.x) return -1;
        return 0
    }
  };
  points.sort(vsort)
  console.log("point zero="+points[0])
      for(let i=0;i<xarr.length;i++){
         points[i].angle=arcos(Number(points[0].x),Number(points[i].x),Number(points[0].y),Number(points[i].y))
        }
    var byangle=(a:point,b:point)=>{
        if(b.angle<a.angle)return 1
        if(b.angle>a.angle)return -1
        else return 0
    }
    points.sort(byangle)
    points[0].zero=true
console.log(points[points.length-1])
let curr=0
let i=0     // safety
do {
    let c1=ccw(points[curr].x,points[curr+1].x,points[curr+2].x,points[curr].y,points[curr+1].y,points[curr+2].y)
    if(c1){curr++}
    else{console.log("removed"+points.splice(curr+1,1))   // remove point
        curr-=1
  }  
    i++;
  }
  while (i < 250 && curr<points.length-2);
  for(i=0;i<points.length;i++){
    console.log(points[i].x+"  "+points[i].y)
    st=st+points[i].x+"\t"+points[i].y+"\n"
  }
  syncWF('./example.txt',st)
// end of main
function arcos(x1: number, x2: number, y1: number, y2: number) {
  let x = x2 - x1
  let y = y2 - y1
      let res= Math.acos(x / Math.sqrt(x * x + y * y))
  if(isNaN(res))res=0
      return res}
function ccw(x1:number,x2:number,x3:number,y1:number,y2:number,y3:number ) // check if it is a left turn
{let bvar= (x2-x1)*(y3-y1)-(y2-y1)*(x3-x1)>0
return bvar
}
function reader(){
  let rv=syncread('./input.txt');
  let sarr=rv.split("\n")
  console.log("len="+sarr.length)
  let i:number
  for(i=0;i<sarr.length;i++){
    let ele=sarr[i].split("\t")
    let temp1=!isNaN(Number(ele[0]))
    let temp2=!isNaN(Number(ele[1]))
    if(temp1&&temp2){
      xarr[i]=Number(ele[0])
      yarr[i]=Number(ele[1])
      console.log(i+":  "+xarr[i]+"  "+yarr[i])
      st=st+xarr[i]+"\t"+yarr[i]+"\n"
    }
  }
}
  function syncWF(fname: string, data: any) {
    /**
     * flags:
     *  - w = Open file for reading and writing. File is created if not exists
     *  - a+ = Open file for reading and appending. The file is created if not exists
     */
    writeFileSync(join(__dirname, fname), data, {      flag: 'w',    });
      return 1;
  }  
  function syncread(filename: string) {
      const cont = readFileSync(join(__dirname, filename), 'utf-8');
      return cont;
  }
Author
Worf
Views
255
First release
Last update
Rating
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