//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); #region BYROW IWorksheet sheet1 = workbook.Worksheets.Add(); sheet1.Name = "BYROW"; var sheet1rngB2B3 = new object[,] { { "Get row totals"}, { "Formula: =BYROW(C6:E10,LAMBDA(row,SUM(row)))"} }; sheet1.Range["$B$2:$B$3"].Value = sheet1rngB2B3; var sheet1rngB5E10 = new object[,] { { "Student", "Math", "English", "physics"}, { "Tom", 19d, 23d, 19d}, { "Jerry", 21d, 15d, 18d}, { "Mario", 19d, 22d, 22d}, { "Luigi", 17d, 15d, 23d}, { "Galen", 16d, 22d, 24d} }; sheet1.Range["$B$5:$E$10"].Value = sheet1rngB5E10; var sheet1rngG5 = "Total"; sheet1.Range["$G$5"].Value = sheet1rngG5; sheet1.Range["$G$6"].Formula2 = "=BYROW(C6:E10,LAMBDA(row,SUM(row)))"; sheet1.Range["$B$2"].Font.Bold = true; IFont sheet1Font1 = sheet1.Range["$B$3"].Font; sheet1Font1.ThemeColor = ThemeColor.Accent1; sheet1Font1.Italic = true; IFont sheet1Font2 = sheet1.Range["$B$5:$E$5,$G$5"].Font; sheet1Font2.ThemeColor = ThemeColor.Light1; sheet1Font2.Bold = true; IInterior sheet1Interior1 = sheet1.Range["$B$5:$E$5,$G$5"].Interior; sheet1Interior1.ThemeColor = ThemeColor.Accent1; sheet1Interior1.Pattern = Pattern.Solid; sheet1Interior1.PatternThemeColor = ThemeColor.Accent1; IInterior sheet1Interior2 = sheet1.Range["$B$6:$E$6,$G$6,$B$8:$E$8,$G$8,$B$10:$E$10,$G$10"].Interior; sheet1Interior2.ThemeColor = ThemeColor.Accent1; sheet1Interior2.Pattern = Pattern.Solid; sheet1Interior2.TintAndShade = 0.8; IRange sheet1Range1 = sheet1.Range["B5:E10,G5:G10"]; sheet1Range1.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.DashDot; sheet1Range1.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.DashDot; sheet1Range1.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.DashDot; sheet1Range1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.DashDot; sheet1Range1.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.DashDot; sheet1Range1.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.DashDot; #endregion #region IWorksheet sheet2 = workbook.Worksheets.Add(); sheet2.Name = "BYCOL"; var sheet2rngB2B3 = new object[,] { { "Get column totals"}, { "Formula: =BYCOL(C6:E10,LAMBDA(col,SUM(col)))"} }; sheet2.Range["$B$2:$B$3"].Value = sheet2rngB2B3; var sheet2rngB5E10 = new object[,] { { "Student", "Math", "English", "physics"}, { "Tom", 19d, 23d, 19d}, { "Jerry", 21d, 15d, 18d}, { "Mario", 19d, 22d, 22d}, { "Luigi", 17d, 15d, 23d}, { "Galen", 16d, 22d, 24d} }; sheet2.Range["$B$5:$E$10"].Value = sheet2rngB5E10; var sheet2rngB12 = "Column Total"; sheet2.Range["$B$12"].Value = sheet2rngB12; sheet2.Range["$C$12"].Formula2 = "=BYCOL(C6:E10,LAMBDA(col,SUM(col)))"; sheet2.Range["$B$2"].Font.Bold = true; IFont sheet2Font1 = sheet2.Range["$B$3"].Font; sheet2Font1.ThemeColor = ThemeColor.Accent1; sheet2Font1.Italic = true; IFont sheet2Font2 = sheet2.Range["$B$5:$E$5"].Font; sheet2Font2.ThemeColor = ThemeColor.Light1; sheet2Font2.Bold = true; IInterior sheet2Interior1 = sheet2.Range["$B$5:$E$5"].Interior; sheet2Interior1.ThemeColor = ThemeColor.Accent1; sheet2Interior1.Pattern = Pattern.Solid; sheet2Interior1.PatternThemeColor = ThemeColor.Accent1; IInterior sheet2Interior2 = sheet2.Range["$B$6:$E$6,$B$8:$E$8,$B$10:$E$10,$B$12:$E$12"].Interior; sheet2Interior2.ThemeColor = ThemeColor.Accent1; sheet2Interior2.Pattern = Pattern.Solid; sheet2Interior2.TintAndShade = 0.8; sheet2.Range["$B:$B"].ColumnWidthInPixel = 103d; sheet2.Range["$E:$E"].ColumnWidthInPixel = 74d; IRange sheet2Range1 = sheet2.Range["B5:E10,B12:E12"]; sheet2Range1.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.DashDot; sheet2Range1.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.DashDot; sheet2Range1.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.DashDot; sheet2Range1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.DashDot; sheet2Range1.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.DashDot; sheet2Range1.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.DashDot; #endregion #region SCAN IWorksheet sheet3 = workbook.Worksheets.Add(); sheet3.Name = "SCAN"; var sheet3rngB2B3 = new object[,] { { "Create a list of factorials"}, { "Formula: =SCAN(1,B6:D7,LAMBDA(a,b,a*b))"} }; sheet3.Range["$B$2:$B$3"].Value = sheet3rngB2B3; var sheet3rngB5D7 = new object[,] { { "Data", null, null}, { 1d, 2d, 3d}, { 4d, 5d, 6d} }; sheet3.Range["$B$5:$D$7"].Value = sheet3rngB5D7; var sheet3rngF5H5 = new object[,] { { "Result:", null, null} }; sheet3.Range["$F$5:$H$5"].Value = sheet3rngF5H5; sheet3.Range["$F$6"].Formula2 = "=SCAN(1,B6:D7,LAMBDA(a,b,a*b))"; sheet3.Range["$B$2"].Font.Bold = true; IFont sheet3Font1 = sheet3.Range["$B$3"].Font; sheet3Font1.ThemeColor = ThemeColor.Accent1; sheet3Font1.Italic = true; IFont sheet3Font2 = sheet3.Range["$B$5:$D$5,$F$5:$H$5"].Font; sheet3Font2.ThemeColor = ThemeColor.Light1; sheet3Font2.Bold = true; IInterior sheet3Font3 = sheet3.Range["$B$5:$D$5,$F$5:$H$5"].Interior; sheet3Font3.ThemeColor = ThemeColor.Accent1; sheet3Font3.Pattern = Pattern.Solid; sheet3Font3.PatternThemeColor = ThemeColor.Accent1; IInterior sheet3Font4 = sheet3.Range["$B$6:$D$6,$F$6:$H$6"].Interior; sheet3Font4.ThemeColor = ThemeColor.Accent1; sheet3Font4.Pattern = Pattern.Solid; sheet3Font4.TintAndShade = 0.8; sheet3.Range["$B$5:$D$5"].Merge(); sheet3.Range["$F$5:$H$5"].Merge(); sheet3.Range["$C:$D"].ColumnWidthInPixel = 84d; IRange sheet3Range1 = sheet3.Range["B5:D7,F5:H7"]; sheet3Range1.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.DashDot; sheet3Range1.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.DashDot; sheet3Range1.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.DashDot; sheet3Range1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.DashDot; sheet3Range1.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.DashDot; sheet3Range1.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.DashDot; #endregion #region REDUCE IWorksheet sheet4 = workbook.Worksheets.Add(); sheet4.Name = "REDUCE"; var sheet4rngB2B3 = new object[,] { { "Get cumulative results"}, { "Formula: =REDUCE(1,B6:D7,LAMBDA(a,b,a*b))"} }; sheet4.Range["$B$2:$B$3"].Value = sheet4rngB2B3; var sheet4rngB5D7 = new object[,] { { "Data", null, null}, { 1d, 2d, 3d}, { 4d, 5d, 6d} }; sheet4.Range["$B$5:$D$7"].Value = sheet4rngB5D7; var rngF5 = "Result:"; sheet4.Range["$F$5"].Value = rngF5; sheet4.Range["$G$5"].Formula2 = "=REDUCE(1,B6:D7,LAMBDA(a,b,a*b))"; sheet4.Range["$B$2"].Font.Bold = true; IFont sheet4Font1 = sheet4.Range["$B$3"].Font; sheet4Font1.ThemeColor = ThemeColor.Accent1; sheet4Font1.Italic = true; IFont sheet4Font2 = sheet4.Range["$B$5:$D$5"].Font; sheet4Font2.ThemeColor = ThemeColor.Light1; sheet4Font2.Bold = true; IInterior sheet4Interior1 = sheet4.Range["$B$5:$D$5"].Interior; sheet4Interior1.ThemeColor = ThemeColor.Accent1; sheet4Interior1.Pattern = Pattern.Solid; sheet4Interior1.PatternThemeColor = ThemeColor.Accent1; IInterior sheet4Interior2 = sheet4.Range["$B$6:$D$6"].Interior; sheet4Interior2.ThemeColor = ThemeColor.Accent1; sheet4Interior2.Pattern = Pattern.Solid; sheet4Interior2.TintAndShade = 0.8; sheet4.Range["$B$5:$D$5"].Merge(); IRange sheet4Range1 = sheet4.Range["B5:D7"]; sheet4Range1.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.DashDot; sheet4Range1.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.DashDot; sheet4Range1.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.DashDot; sheet4Range1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.DashDot; sheet4Range1.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.DashDot; sheet4Range1.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.DashDot; #endregion #region MAP IWorksheet sheet5 = workbook.Worksheets.Add(); sheet5.Name = "MAP"; var sheet5rngB2B3 = new object[,] { { "Square numbers above a vertain value"}, { "Formula: =MAP(B6:D7,LAMBDA(a,IF(a>4,a*a,a*1)))"} }; sheet5.Range["$B$2:$B$3"].Value = sheet5rngB2B3; var sheet5rngB5D7 = new object[,] { { "Data", null, null}, { 1d, 2d, 3d}, { 4d, 5d, 6d} }; sheet5.Range["$B$5:$D$7"].Value = sheet5rngB5D7; var rngF5H5 = new object[,] { { "Result:", null, null} }; sheet5.Range["$F$5:$H$5"].Value = rngF5H5; sheet5.Range["$F$6"].Formula2 = "=MAP(B6:D7,LAMBDA(a,IF(a>4,a*a,a*1)))"; sheet5.Range["$B$2"].Font.Bold = true; IFont sheet5Font1 = sheet5.Range["$B$3"].Font; sheet5Font1.ThemeColor = ThemeColor.Accent1; sheet5Font1.Italic = true; IFont sheet5Font2 = sheet5.Range["$B$5:$D$5,$F$5:$H$5"].Font; sheet5Font2.ThemeColor = ThemeColor.Light1; sheet5Font2.Bold = true; IInterior sheet5Interior1 = sheet5.Range["$B$5:$D$5,$F$5:$H$5"].Interior; sheet5Interior1.ThemeColor = ThemeColor.Accent1; sheet5Interior1.Pattern = Pattern.Solid; sheet5Interior1.PatternThemeColor = ThemeColor.Accent1; IInterior sheet5Interior2 = sheet5.Range["$B$6:$D$6,$F$6:$H$6"].Interior; sheet5Interior2.ThemeColor = ThemeColor.Accent1; sheet5Interior2.Pattern = Pattern.Solid; sheet5Interior2.TintAndShade = 0.8; sheet5.Range["$B$5:$D$5"].Merge(); sheet5.Range["$F$5:$H$5"].Merge(); IRange sheet5Range1 = sheet5.Range["B5:D7,F5:H7"]; sheet5Range1.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.DashDot; sheet5Range1.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.DashDot; sheet5Range1.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.DashDot; sheet5Range1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.DashDot; sheet5Range1.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.DashDot; sheet5Range1.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.DashDot; #endregion #region MAKEARRAY IWorksheet sheet6 = workbook.Worksheets.Add(); sheet6.Name = "MAKEARRAY"; var sheet6rngB2B3 = new object[,] { { "Multiplication table"}, { "Formula: =MAKEARRAY(9,9,LAMBDA(row,col,row&\"*\"&col&\" = \"&row*col))"} }; sheet6.Range["$B$2:$B$3"].Value = sheet6rngB2B3; sheet6.Range["$B$5"].Formula2 = "=MAKEARRAY(9,9,LAMBDA(row,col,row&\"*\"&col&\" = \"&row*col))"; sheet6.Range["$B$2"].Font.Bold = true; IFont sheet6Font1 = sheet6.Range["$B$3"].Font; sheet6Font1.ThemeColor = ThemeColor.Accent1; sheet6Font1.Italic = true; #endregion #region ISOMITTED IWorksheet sheet7 = workbook.Worksheets.Add(); sheet7.Name = "ISOMITTED"; var sheet7rngB2B3 = new object[,] { { "Check for a missing parameter and return a friendly string"}, { "Formula: =LAMBDA(x,y,IF(ISOMITTED(y),\"Missing second argument\",x+y))(1,)"} }; sheet7.Range["$B$2:$B$3"].Value = sheet7rngB2B3; var rngB5 = "Result:"; sheet7.Range["$B$5"].Value = rngB5; sheet7.Range["$C$5"].Formula2 = "=LAMBDA(x,y,IF(ISOMITTED(y),\"Missing second argument\",x+y))(1,)"; sheet7.Range["$B$2"].Font.Bold = true; IFont sheet7Font1 = sheet7.Range["$B$3"].Font; sheet7Font1.ThemeColor = ThemeColor.Accent1; sheet7Font1.Italic = true; #endregion workbook.Worksheets["Sheet1"].Delete(); // Save to an excel file workbook.Save("LambdaAdditionalFunction.xlsx");
' Create a new Workbook Dim workbook As New Workbook ' #Region "BYROW" Dim sheet1 As IWorksheet = workbook.Worksheets.Add() sheet1.Name = "BYROW" Dim sheet1rngB2B3 = New Object(,) { {"Get row totals"}, {"Formula: =BYROW(C6:E10,LAMBDA(row,SUM(row)))"} } sheet1.Range("$B$2:$B$3").Value = sheet1rngB2B3 Dim sheet1rngB5E10 = New Object(,) { {"Student", "Math", "English", "physics"}, {"Tom", 19.0R, 23.0R, 19.0R}, {"Jerry", 21.0R, 15.0R, 18.0R}, {"Mario", 19.0R, 22.0R, 22.0R}, {"Luigi", 17.0R, 15.0R, 23.0R}, {"Galen", 16.0R, 22.0R, 24.0R} } sheet1.Range("$B$5:$E$10").Value = sheet1rngB5E10 Dim sheet1rngG5 = "Total" sheet1.Range("$G$5").Value = sheet1rngG5 sheet1.Range("$G$6").Formula2 = "=BYROW(C6:E10,LAMBDA(row,SUM(row)))" sheet1.Range("$B$2").Font.Bold = True Dim sheet1Font1 As IFont = sheet1.Range("$B$3").Font sheet1Font1.ThemeColor = ThemeColor.Accent1 sheet1Font1.Italic = True Dim sheet1Font2 As IFont = sheet1.Range("$B$5:$E$5,$G$5").Font sheet1Font2.ThemeColor = ThemeColor.Light1 sheet1Font2.Bold = True Dim sheet1Interior1 As IInterior = sheet1.Range("$B$5:$E$5,$G$5").Interior sheet1Interior1.ThemeColor = ThemeColor.Accent1 sheet1Interior1.Pattern = Pattern.Solid sheet1Interior1.PatternThemeColor = ThemeColor.Accent1 Dim sheet1Interior2 As IInterior = sheet1.Range("$B$6:$E$6,$G$6,$B$8:$E$8,$G$8,$B$10:$E$10,$G$10").Interior sheet1Interior2.ThemeColor = ThemeColor.Accent1 sheet1Interior2.Pattern = Pattern.Solid sheet1Interior2.TintAndShade = 0.8 Dim sheet1Range1 As IRange = sheet1.Range("B5:E10,G5:G10") sheet1Range1.Borders(BordersIndex.EdgeTop).LineStyle = BorderLineStyle.DashDot sheet1Range1.Borders(BordersIndex.EdgeLeft).LineStyle = BorderLineStyle.DashDot sheet1Range1.Borders(BordersIndex.EdgeRight).LineStyle = BorderLineStyle.DashDot sheet1Range1.Borders(BordersIndex.EdgeBottom).LineStyle = BorderLineStyle.DashDot sheet1Range1.Borders(BordersIndex.InsideHorizontal).LineStyle = BorderLineStyle.DashDot sheet1Range1.Borders(BordersIndex.InsideVertical).LineStyle = BorderLineStyle.DashDot ' #End Region ' #Region "" Dim sheet2 As IWorksheet = workbook.Worksheets.Add() sheet2.Name = "BYCOL" Dim sheet2rngB2B3 = New Object(,) { {"Get column totals"}, {"Formula: =BYCOL(C6:E10,LAMBDA(col,SUM(col)))"} } sheet2.Range("$B$2:$B$3").Value = sheet2rngB2B3 Dim sheet2rngB5E10 = New Object(,) { {"Student", "Math", "English", "physics"}, {"Tom", 19.0R, 23.0R, 19.0R}, {"Jerry", 21.0R, 15.0R, 18.0R}, {"Mario", 19.0R, 22.0R, 22.0R}, {"Luigi", 17.0R, 15.0R, 23.0R}, {"Galen", 16.0R, 22.0R, 24.0R} } sheet2.Range("$B$5:$E$10").Value = sheet2rngB5E10 Dim sheet2rngB12 = "Column Total" sheet2.Range("$B$12").Value = sheet2rngB12 sheet2.Range("$C$12").Formula2 = "=BYCOL(C6:E10,LAMBDA(col,SUM(col)))" sheet2.Range("$B$2").Font.Bold = True Dim sheet2Font1 As IFont = sheet2.Range("$B$3").Font sheet2Font1.ThemeColor = ThemeColor.Accent1 sheet2Font1.Italic = True Dim sheet2Font2 As IFont = sheet2.Range("$B$5:$E$5").Font sheet2Font2.ThemeColor = ThemeColor.Light1 sheet2Font2.Bold = True Dim sheet2Interior1 As IInterior = sheet2.Range("$B$5:$E$5").Interior sheet2Interior1.ThemeColor = ThemeColor.Accent1 sheet2Interior1.Pattern = Pattern.Solid sheet2Interior1.PatternThemeColor = ThemeColor.Accent1 Dim sheet2Interior2 As IInterior = sheet2.Range("$B$6:$E$6,$B$8:$E$8,$B$10:$E$10,$B$12:$E$12").Interior sheet2Interior2.ThemeColor = ThemeColor.Accent1 sheet2Interior2.Pattern = Pattern.Solid sheet2Interior2.TintAndShade = 0.8 sheet2.Range("$B:$B").ColumnWidthInPixel = 103.0R sheet2.Range("$E:$E").ColumnWidthInPixel = 74.0R Dim sheet2Range1 As IRange = sheet2.Range("B5:E10,B12:E12") sheet2Range1.Borders(BordersIndex.EdgeTop).LineStyle = BorderLineStyle.DashDot sheet2Range1.Borders(BordersIndex.EdgeLeft).LineStyle = BorderLineStyle.DashDot sheet2Range1.Borders(BordersIndex.EdgeRight).LineStyle = BorderLineStyle.DashDot sheet2Range1.Borders(BordersIndex.EdgeBottom).LineStyle = BorderLineStyle.DashDot sheet2Range1.Borders(BordersIndex.InsideHorizontal).LineStyle = BorderLineStyle.DashDot sheet2Range1.Borders(BordersIndex.InsideVertical).LineStyle = BorderLineStyle.DashDot ' #End Region ' #Region "SCAN" Dim sheet3 As IWorksheet = workbook.Worksheets.Add() sheet3.Name = "SCAN" Dim sheet3rngB2B3 = New Object(,) { {"Create a list of factorials"}, {"Formula: =SCAN(1,B6:D7,LAMBDA(a,b,a*b))"} } sheet3.Range("$B$2:$B$3").Value = sheet3rngB2B3 Dim sheet3rngB5D7 = New Object(,) { {"Data", Nothing, Nothing}, {1.0R, 2.0R, 3.0R}, {4.0R, 5.0R, 6.0R} } sheet3.Range("$B$5:$D$7").Value = sheet3rngB5D7 Dim sheet3rngF5H5 = New Object(,) { {"Result:", Nothing, Nothing} } sheet3.Range("$F$5:$H$5").Value = sheet3rngF5H5 sheet3.Range("$F$6").Formula2 = "=SCAN(1,B6:D7,LAMBDA(a,b,a*b))" sheet3.Range("$B$2").Font.Bold = True Dim sheet3Font1 As IFont = sheet3.Range("$B$3").Font sheet3Font1.ThemeColor = ThemeColor.Accent1 sheet3Font1.Italic = True Dim sheet3Font2 As IFont = sheet3.Range("$B$5:$D$5,$F$5:$H$5").Font sheet3Font2.ThemeColor = ThemeColor.Light1 sheet3Font2.Bold = True Dim sheet3Font3 As IInterior = sheet3.Range("$B$5:$D$5,$F$5:$H$5").Interior sheet3Font3.ThemeColor = ThemeColor.Accent1 sheet3Font3.Pattern = Pattern.Solid sheet3Font3.PatternThemeColor = ThemeColor.Accent1 Dim sheet3Font4 As IInterior = sheet3.Range("$B$6:$D$6,$F$6:$H$6").Interior sheet3Font4.ThemeColor = ThemeColor.Accent1 sheet3Font4.Pattern = Pattern.Solid sheet3Font4.TintAndShade = 0.8 sheet3.Range("$B$5:$D$5").Merge() sheet3.Range("$F$5:$H$5").Merge() sheet3.Range("$C:$D").ColumnWidthInPixel = 84.0R Dim sheet3Range1 As IRange = sheet3.Range("B5:D7,F5:H7") sheet3Range1.Borders(BordersIndex.EdgeTop).LineStyle = BorderLineStyle.DashDot sheet3Range1.Borders(BordersIndex.EdgeLeft).LineStyle = BorderLineStyle.DashDot sheet3Range1.Borders(BordersIndex.EdgeRight).LineStyle = BorderLineStyle.DashDot sheet3Range1.Borders(BordersIndex.EdgeBottom).LineStyle = BorderLineStyle.DashDot sheet3Range1.Borders(BordersIndex.InsideHorizontal).LineStyle = BorderLineStyle.DashDot sheet3Range1.Borders(BordersIndex.InsideVertical).LineStyle = BorderLineStyle.DashDot ' #End Region ' #Region "REDUCE" Dim sheet4 As IWorksheet = workbook.Worksheets.Add() sheet4.Name = "REDUCE" Dim sheet4rngB2B3 = New Object(,) { {"Get cumulative results"}, {"Formula: =REDUCE(1,B6:D7,LAMBDA(a,b,a*b))"} } sheet4.Range("$B$2:$B$3").Value = sheet4rngB2B3 Dim sheet4rngB5D7 = New Object(,) { {"Data", Nothing, Nothing}, {1.0R, 2.0R, 3.0R}, {4.0R, 5.0R, 6.0R} } sheet4.Range("$B$5:$D$7").Value = sheet4rngB5D7 Dim rngF5 = "Result:" sheet4.Range("$F$5").Value = rngF5 sheet4.Range("$G$5").Formula2 = "=REDUCE(1,B6:D7,LAMBDA(a,b,a*b))" sheet4.Range("$B$2").Font.Bold = True Dim sheet4Font1 As IFont = sheet4.Range("$B$3").Font sheet4Font1.ThemeColor = ThemeColor.Accent1 sheet4Font1.Italic = True Dim sheet4Font2 As IFont = sheet4.Range("$B$5:$D$5").Font sheet4Font2.ThemeColor = ThemeColor.Light1 sheet4Font2.Bold = True Dim sheet4Interior1 As IInterior = sheet4.Range("$B$5:$D$5").Interior sheet4Interior1.ThemeColor = ThemeColor.Accent1 sheet4Interior1.Pattern = Pattern.Solid sheet4Interior1.PatternThemeColor = ThemeColor.Accent1 Dim sheet4Interior2 As IInterior = sheet4.Range("$B$6:$D$6").Interior sheet4Interior2.ThemeColor = ThemeColor.Accent1 sheet4Interior2.Pattern = Pattern.Solid sheet4Interior2.TintAndShade = 0.8 sheet4.Range("$B$5:$D$5").Merge() Dim sheet4Range1 As IRange = sheet4.Range("B5:D7") sheet4Range1.Borders(BordersIndex.EdgeTop).LineStyle = BorderLineStyle.DashDot sheet4Range1.Borders(BordersIndex.EdgeLeft).LineStyle = BorderLineStyle.DashDot sheet4Range1.Borders(BordersIndex.EdgeRight).LineStyle = BorderLineStyle.DashDot sheet4Range1.Borders(BordersIndex.EdgeBottom).LineStyle = BorderLineStyle.DashDot sheet4Range1.Borders(BordersIndex.InsideHorizontal).LineStyle = BorderLineStyle.DashDot sheet4Range1.Borders(BordersIndex.InsideVertical).LineStyle = BorderLineStyle.DashDot ' #End Region ' #Region "MAP" Dim sheet5 As IWorksheet = workbook.Worksheets.Add() sheet5.Name = "MAP" Dim sheet5rngB2B3 = New Object(,) { {"Square numbers above a vertain value"}, {"Formula: =MAP(B6:D7,LAMBDA(a,IF(a>4,a*a,a*1)))"} } sheet5.Range("$B$2:$B$3").Value = sheet5rngB2B3 Dim sheet5rngB5D7 = New Object(,) { {"Data", Nothing, Nothing}, {1.0R, 2.0R, 3.0R}, {4.0R, 5.0R, 6.0R} } sheet5.Range("$B$5:$D$7").Value = sheet5rngB5D7 Dim rngF5H5 = New Object(,) { {"Result:", Nothing, Nothing} } sheet5.Range("$F$5:$H$5").Value = rngF5H5 sheet5.Range("$F$6").Formula2 = "=MAP(B6:D7,LAMBDA(a,IF(a>4,a*a,a*1)))" sheet5.Range("$B$2").Font.Bold = True Dim sheet5Font1 As IFont = sheet5.Range("$B$3").Font sheet5Font1.ThemeColor = ThemeColor.Accent1 sheet5Font1.Italic = True Dim sheet5Font2 As IFont = sheet5.Range("$B$5:$D$5,$F$5:$H$5").Font sheet5Font2.ThemeColor = ThemeColor.Light1 sheet5Font2.Bold = True Dim sheet5Interior1 As IInterior = sheet5.Range("$B$5:$D$5,$F$5:$H$5").Interior sheet5Interior1.ThemeColor = ThemeColor.Accent1 sheet5Interior1.Pattern = Pattern.Solid sheet5Interior1.PatternThemeColor = ThemeColor.Accent1 Dim sheet5Interior2 As IInterior = sheet5.Range("$B$6:$D$6,$F$6:$H$6").Interior sheet5Interior2.ThemeColor = ThemeColor.Accent1 sheet5Interior2.Pattern = Pattern.Solid sheet5Interior2.TintAndShade = 0.8 sheet5.Range("$B$5:$D$5").Merge() sheet5.Range("$F$5:$H$5").Merge() Dim sheet5Range1 As IRange = sheet5.Range("B5:D7,F5:H7") sheet5Range1.Borders(BordersIndex.EdgeTop).LineStyle = BorderLineStyle.DashDot sheet5Range1.Borders(BordersIndex.EdgeLeft).LineStyle = BorderLineStyle.DashDot sheet5Range1.Borders(BordersIndex.EdgeRight).LineStyle = BorderLineStyle.DashDot sheet5Range1.Borders(BordersIndex.EdgeBottom).LineStyle = BorderLineStyle.DashDot sheet5Range1.Borders(BordersIndex.InsideHorizontal).LineStyle = BorderLineStyle.DashDot sheet5Range1.Borders(BordersIndex.InsideVertical).LineStyle = BorderLineStyle.DashDot ' #End Region ' #Region "MAKEARRAY" Dim sheet6 As IWorksheet = workbook.Worksheets.Add() sheet6.Name = "MAKEARRAY" Dim sheet6rngB2B3 = New Object(,) { {"Multiplication table"}, {"Formula: =MAKEARRAY(9,9,LAMBDA(row,col,row&""*""&col&"" = ""&row*col))"} } sheet6.Range("$B$2:$B$3").Value = sheet6rngB2B3 sheet6.Range("$B$5").Formula2 = "=MAKEARRAY(9,9,LAMBDA(row,col,row&""*""&col&"" = ""&row*col))" sheet6.Range("$B$2").Font.Bold = True Dim sheet6Font1 As IFont = sheet6.Range("$B$3").Font sheet6Font1.ThemeColor = ThemeColor.Accent1 sheet6Font1.Italic = True ' #End Region ' #Region "ISOMITTED" Dim sheet7 As IWorksheet = workbook.Worksheets.Add() sheet7.Name = "ISOMITTED" Dim sheet7rngB2B3 = New Object(,) { {"Check for a missing parameter and return a friendly string"}, {"Formula: =LAMBDA(x,y,IF(ISOMITTED(y),""Missing second argument"",x+y))(1,)"} } sheet7.Range("$B$2:$B$3").Value = sheet7rngB2B3 Dim rngB5 = "Result:" sheet7.Range("$B$5").Value = rngB5 sheet7.Range("$C$5").Formula2 = "=LAMBDA(x,y,IF(ISOMITTED(y),""Missing second argument"",x+y))(1,)" sheet7.Range("$B$2").Font.Bold = True Dim sheet7Font1 As IFont = sheet7.Range("$B$3").Font sheet7Font1.ThemeColor = ThemeColor.Accent1 sheet7Font1.Italic = True ' #End Region workbook.Worksheets("Sheet1").Delete() ' save to an excel file workbook.Save("LambdaAdditionalFunction.xlsx")