//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); workbook.Names.Add("user", "='Case 3'!$C$4"); #region Case 1 IWorksheet ws = workbook.ActiveSheet; ws.Name = "Use case"; var rngB2E7 = new object[,] { { "=LET(data,Table1[Salary],calc,IFS(C9=\"Min\", 1,C9=\"Max\",2,C9=\"Average\",3),aggregate,CHOOSE(calc,5,4,1),SUBTOTAL(aggregate,data))", null, null, null}, { "Support Engineer", "Start Date", "Time with Company", "Salary"}, { "Bob", 41784d, "5+ years", 2790d}, { "Jim", 43666d, "1-4 years", 2216d}, { "Kevin", 42795d, "1-4 years", 2498d}, { "Sarah", 44026d, "1-4 years", 1989d} }; ws.Range["B1"].Value = "Use Let to make the formula easy to understand"; ws.Range["$B$2:$E$7"].Value = rngB2E7; var rngB9C9 = new object[,] { { "Salary Calculations", "Max"} }; ws.Range["$B$9:$C$9"].Value = rngB9C9; var table1 = ws.Tables.Add(ws.Range["$B$3:$E$7"], true); table1.TableStyle = ws.Workbook.TableStyles["TableStyleLight2"]; ws.Range["$D$9"].Formula2 = "=LET(data,Table1[Salary],calc,IFS(C9=\"Min\", 1,C9=\"Max\",2,C9=\"Average\",3),aggregate,CHOOSE(calc,5,4,1),SUBTOTAL(aggregate,data))"; ws.Range["$B$1"].Font.Bold = true; var range1 = ws.Range["$B$9"].Font; range1.Bold = true; range1.Color = Color.White; IInterior range2 = ws.Range["$B$9"].Interior; range2.ThemeColor = ThemeColor.Accent1; var range3 = ws.Range["$C$9:$D$9"].Interior; range3.ThemeColor = ThemeColor.Accent1; range3.TintAndShade = 0.6; unchecked { var sheet2B2 = ws.Range["$B$2"].Font; sheet2B2.Bold = true; sheet2B2.Color = Color.FromArgb((int)0xFFC00000); sheet2B2.Name = "Consolas"; sheet2B2.Size = 9d; } ws.Range["B:E"].ColumnWidthInPixel = 100d; ws.Range["C9"].Validation.Add(ValidationType.List, ValidationAlertStyle.Stop, ValidationOperator.Between, "Min,Max,Average"); #endregion #region Case 2 IWorksheet sheet2 = workbook.Worksheets.Add(); sheet2.Name = "Case 2"; var rngB2 = "LET basic usage"; sheet2.Range["$B$2"].Value = rngB2; var rngB4B6 = new object[,] { { "Sample pair varible"}, { "=LET(x,2,x+3)"}, { "Result"} }; sheet2.Range["$B$4:$B$6"].Value = rngB4B6; var rngB8B10 = new object[,] { { "Multiple pair varibles"}, { "=LET(x,1,y,2,z,3,x+y+z=x*y*z)"}, { "Result"} }; sheet2.Range["$B$8:$B$10"].Value = rngB8B10; var rngB12B14 = new object[,] { { "Invoked formula"}, { "=LET(x,1,y,2,SUM(x,y))"}, { "Result"} }; sheet2.Range["$B$12:$B$14"].Value = rngB12B14; var rngB16B19 = new object[,] { { "Dynamic Array"}, { "=LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))"}, { "Range"}, { "Result"} }; sheet2.Range["$B$16:$B$19"].Value = rngB16B19; var rngC18 = 1d; sheet2.Range["$C$18"].Value = rngC18; var rngD18E18 = new object[,] { { 2d, 3d} }; sheet2.Range["$D$18:$E$18"].Value = rngD18E18; sheet2.Range["$C$6"].Formula2 = "=LET(x,2,x+3)"; sheet2.Range["$C$10"].Formula2 = "=LET(x,1,y,2,z,3,x+y+z=x*y*z)"; sheet2.Range["$C$14"].Formula2 = "=LET(x,1,y,2,SUM(x,y))"; sheet2.Range["$C$19"].Formula2 = "=LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))"; unchecked { var range4 = sheet2.Range["$B$2"].Font; range4.Bold = true; range4.Color = Color.FromArgb((int)0xFF172B4D); range4.Size = 12d; } var range5 = sheet2.Range["$B$4,$B$8,$B$12,$B$16"].Font; range5.Bold = true; range5.Size = 9d; unchecked { var range6 = sheet2.Range["$B$5,$B$9,$B$13,$B$17"].Font; range6.ThemeFont = ThemeFont.None; range6.Bold = true; range6.Color = Color.FromArgb((int)0xFFC00000); range6.Name = "Consolas"; range6.Size = 9d; } var range7 = sheet2.Range["$B$6,$B$10,$B$14,$B$19"].Interior; range7.ThemeColor = ThemeColor.Accent1; var range7Font = sheet2.Range["$B$6,$B$10,$B$14,$B$19"].Font; range7Font.Color = Color.White; range7Font.Bold = true; var range8 = sheet2.Range["$B$18"].Interior; range8.ThemeColor = ThemeColor.Accent1; range8.TintAndShade = 0.6; #endregion #region Case 3 IWorksheet sheet3 = workbook.Worksheets.Add(); sheet3.Name = "Case 3"; var rngB2B4 = new object[,] { { "LET local varible & custom name"}, { "This sheet includes a custom name \"user\" which be assigned \"White\" value"}, { "user"} }; sheet3.Range["$B$2:$B$4"].Value = rngB2B4; var rngC4 = "White"; sheet3.Range["$C$4"].Value = rngC4; var rngB6B8 = new object[,] { { "Always use let local varible first"}, { "=LET(user,\"Andy\",\"The actual user is: \"&user)"}, { "Result"} }; sheet3.Range["$B$6:$B$8"].Value = rngB6B8; var rngB10B12 = new object[,] { { "Use custom name if not avalible local varible"}, { "=LET(user,user,\"The actual user is: \"&user)"}, { "Result"} }; sheet3.Range["$B$10:$B$12"].Value = rngB10B12; sheet3.Range["$C$8"].Formula2 = "=LET(user,\"Andy\",\"The actual user is: \"&user)"; sheet3.Range["$C$12"].Formula2 = "=LET(user,user,\"The actual user is: \"&user)"; unchecked { var range10 = sheet3.Range["$B$2"].Font; range10.Bold = true; range10.Color = Color.FromArgb((int)0xFF172B4D); range10.Size = 12d; } var range11 = sheet3.Range["$B$3"].Font; range11.Bold = true; range11.Color = Color.Gray; range11.Name = "Segoe UI"; range11.Size = 9d; var range12 = sheet3.Range["$B$6,$B$10"].Font; range12.ThemeFont = ThemeFont.None; range12.Bold = true; range12.ColorIndex = -4105; range12.Name = "Segoe UI"; range12.Size = 9d; unchecked { var range13 = sheet3.Range["$B$7,$B$11"].Font; range13.ThemeFont = ThemeFont.None; range13.Bold = true; range13.Color = Color.FromArgb((int)0xFFC00000); range13.Name = "Consolas"; range13.Size = 9d; } var range14 = sheet3.Range["$B$4"].Interior; range14.ThemeColor = ThemeColor.Accent1; range14.TintAndShade = 0.6; var range15 = sheet3.Range["$B$8,$B$12"].Interior; range15.ThemeColor = ThemeColor.Accent1; var range15Font = sheet3.Range["$B$8,$B$12"].Font; range15Font.Color = Color.White; range15Font.Bold = true; #endregion #region Case 4 IWorksheet sheet4 = workbook.Worksheets.Add(); sheet4.Name = "Case 4"; var sheet4B2 = "LET nested"; sheet4.Range["$B$2"].Value = sheet4B2; var sheet4B4B6 = new object[,] { { "Always use the current scope varible"}, { "=LET(var,\"First scope\",LET(var,\"Second scope\",var))"}, { "Result"} }; sheet4.Range["$B$4:$B$6"].Value = sheet4B4B6; var sheet4B8B10 = new object[,] { { "Use the top scope varible if not found the avalible varible in current scope"}, { "=LET(var,\"First scope\",LET(var,var,var&\" [from the second scope]\"))"}, { "Result"} }; sheet4.Range["$B$8:$B$10"].Value = sheet4B8B10; sheet4.Range["$C$6"].Formula2 = "=LET(var,\"First scope\",LET(var,\"Second scope\",var))"; sheet4.Range["$C$10"].Formula2 = "=LET(var,\"First scope\",LET(var,var,var&\" [from the second scope]\"))"; unchecked { var range41 = sheet4.Range["$B$2"].Font; range41.Bold = true; range41.Color = Color.FromArgb((int)0xFF172B4D); range41.Size = 12d; } var range42 = sheet4.Range["$B$4,$B$8"].Font; range42.Bold = true; range42.Size = 9d; unchecked { var range43 = sheet4.Range["$B$5,$B$9"].Font; range43.Bold = true; range43.Color = Color.FromArgb((int)0xFFC00000); range43.Name = "Consolas"; range43.Size = 9d; } var range44 = sheet4.Range["$B$6,$B$10"].Interior; range44.ThemeColor = ThemeColor.Accent1; var range44Font = sheet4.Range["$B$6,$B$10"].Font; range44Font.Color = Color.White; range44Font.Bold = true; #endregion #region Case 5 IWorksheet sheet5 = workbook.Worksheets.Add(); sheet5.Name = "Case 5"; var sheet5B2 = "LET simplify the complex formula"; sheet5.Range["$B$2"].Value = sheet5B2; var sheet5B4B13 = new object[,] { { "Filter the data to show one person"}, { "=LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange),\"-\",filteredRange))"}, { "Rep"}, { "Amy"}, { "Fred"}, { "Amy"}, { "Fred"}, { "Fred"}, { "Amy"}, { "Fred"} }; sheet5.Range["$B$4:$B$13"].Value = sheet5B4B13; var sheet5C6C13 = new object[,] { { "Region"}, { "East"}, { "South"}, { "West"}, { "North"}, { "West"}, { "East"}, { "North"} }; sheet5.Range["$C$6:$C$13"].Value = sheet5C6C13; var rngD6D9 = new object[,] { { "Product"}, { "Apple"}, { "Banana"}, { "Mango"} }; sheet5.Range["$D$6:$D$9"].Value = rngD6D9; var rngE6E13 = new object[,] { { "Profit"}, { 1.33d}, { 0.09d}, { 1.85d}, { 0.82d}, { 1.25d}, { 0.72d}, { 0.54d} }; sheet5.Range["$E$6:$E$13"].Value = rngE6E13; var rngG7G8 = new object[,] { { "Rep"}, { "Result"} }; sheet5.Range["$G$7:$G$8"].Value = rngG7G8; var rngH7 = "Fred"; sheet5.Range["$H$7"].Value = rngH7; var rngD11D13 = new object[,] { { "Banana"}, { "Apple"}, { "Mango"} }; sheet5.Range["$D$11:$D$13"].Value = rngD11D13; var sheet5B16B19 = new object[,] { { "Generate all dates between May 1, 2020 and May 15, 2020"}, { "=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))"}, { "Start"}, { "End"} }; sheet5.Range["$B$16:$B$19"].Value = sheet5B16B19; var rngC18C19 = new object[,] { { DateTime.Parse("5/1/2020 12:00:00 AM")}, { DateTime.Parse("5/15/2020 12:00:00 AM")} }; sheet5.Range["$C$18:$C$19"].Value = rngC18C19; var rngG18 = "Result"; sheet5.Range["$G$18"].Value = rngG18; sheet5.Range["$H$8"].Formula2 = "=LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange),\"-\",filteredRange))"; sheet5.Range["$H$18"].Formula2 = "=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))"; var sheet5Range1 = sheet5.Range["$C$18:$C$19,$H$18:$H$28"]; sheet5Range1.NumberFormat = "yyyy/m/d"; unchecked { var sheet5Range2 = sheet5.Range["$B$2"].Font; sheet5Range2.Bold = true; sheet5Range2.Color = Color.FromArgb((int)0xFF172B4D); sheet5Range2.Size = 12d; } var range51 = sheet5.Range["$B$4,$B$16"].Font; range51.Bold = true; range51.Name = "Segoe UI"; range51.Size = 9d; unchecked { var sheet5Range3 = sheet5.Range["$B$5,$B$17"].Font; sheet5Range3.Bold = true; sheet5Range3.Color = Color.FromArgb((int)0xFFC00000); sheet5Range3.Name = "Consolas"; sheet5Range3.Size = 9d; } var sheet5Range4 = sheet5.Range["$B$6:$E$6,$B$18:$B$19"].Font; sheet5Range4.Bold = true; unchecked { var range52 = sheet5.Range["$B$6:$E$6,$B$18:$B$19"].Interior; range52.Color = Color.FromArgb((int)0xFFD9E1F2); range52.Pattern = Pattern.Solid; } var sheet5Range5 = sheet5.Range["$G$7"].Interior; sheet5Range5.ThemeColor = ThemeColor.Accent1; var sheet5Range5Font = sheet5.Range["$G$7"].Font; sheet5Range5Font.Color = Color.White; sheet5Range5Font.Bold = true; var sheet5Range6 = sheet5.Range["$G$8,$G$18"].Interior; sheet5Range6.ThemeColor = ThemeColor.Accent1; sheet5Range6.TintAndShade = 0.6; sheet5.Range["$C:$C"].AutoFit(); sheet5.Range["$H:$H"].ColumnWidth = 10; #endregion // Save to an excel file workbook.Save("LetFunction.xlsx");
' Create a new Workbook Dim workbook As New Workbook workbook.Names.Add("user", "='Case 3'!$C$4") Dim ws As IWorksheet = workbook.ActiveSheet ws.Name = "Use case" Dim rngB2E7 = New Object(,) { {"=LET(data,Table1[Salary],calc,IFS(C9=""Min"", 1,C9=""Max"",2,C9=""Average"",3),aggregate,CHOOSE(calc,5,4,1),SUBTOTAL(aggregate,data))", Nothing, Nothing, Nothing}, {"Support Engineer", "Start Date", "Time with Company", "Salary"}, {"Bob", 41784.0R, "5+ years", 2790.0R}, {"Jim", 43666.0R, "1-4 years", 2216.0R}, {"Kevin", 42795.0R, "1-4 years", 2498.0R}, {"Sarah", 44026.0R, "1-4 years", 1989.0R}} ws.Range("B1").Value = "Use Let to make the formula easy to understand" ws.Range("$B$2:$E$7").Value = rngB2E7 Dim rngB9C9 = New Object(,) { {"Salary Calculations", "Max"}} ws.Range("$B$9:$C$9").Value = rngB9C9 Dim table1 = ws.Tables.Add(ws.Range("$B$3:$E$7"), True) table1.TableStyle = ws.Workbook.TableStyles("TableStyleLight2") ws.Range("$D$9").Formula2 = "=LET(data,Table1[Salary],calc,IFS(C9=""Min"", 1,C9=""Max"",2,C9=""Average"",3),aggregate,CHOOSE(calc,5,4,1),SUBTOTAL(aggregate,data))" ws.Range("$B$1").Font.Bold = True Dim range1 = ws.Range("$B$9").Font range1.Bold = True range1.Color = Color.White Dim range2 As IInterior = ws.Range("$B$9").Interior range2.ThemeColor = ThemeColor.Accent1 Dim range3 = ws.Range("$C$9:$D$9").Interior range3.ThemeColor = ThemeColor.Accent1 range3.TintAndShade = 0.6 Dim sheet2B2 = ws.Range("$B$2").Font sheet2B2.Bold = True sheet2B2.Color = Color.FromArgb(CInt(&HFFC00000)) sheet2B2.Name = "Consolas" sheet2B2.Size = 9.0R ws.Range("B:E").ColumnWidthInPixel = 100.0R ws.Range("C9").Validation.Add(ValidationType.List, ValidationAlertStyle.[Stop], ValidationOperator.Between, "Min,Max,Average") Dim sheet2 As IWorksheet = workbook.Worksheets.Add() sheet2.Name = "Case 2" Dim rngB2 = "LET basic usage" sheet2.Range("$B$2").Value = rngB2 Dim rngB4B6 = New Object(,) { {"Sample pair varible"}, {"=LET(x,2,x+3)"}, {"Result"}} sheet2.Range("$B$4:$B$6").Value = rngB4B6 Dim rngB8B10 = New Object(,) { {"Multiple pair varibles"}, {"=LET(x,1,y,2,z,3,x+y+z=x*y*z)"}, {"Result"}} sheet2.Range("$B$8:$B$10").Value = rngB8B10 Dim rngB12B14 = New Object(,) { {"Invoked formula"}, {"=LET(x,1,y,2,SUM(x,y))"}, {"Result"}} sheet2.Range("$B$12:$B$14").Value = rngB12B14 Dim rngB16B19 = New Object(,) { {"Dynamic Array"}, {"=LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))"}, {"Range"}, {"Result"}} sheet2.Range("$B$16:$B$19").Value = rngB16B19 Dim rngC18 = 1.0R sheet2.Range("$C$18").Value = rngC18 Dim rngD18E18 = New Object(,) { {2.0R, 3.0R}} sheet2.Range("$D$18:$E$18").Value = rngD18E18 sheet2.Range("$C$6").Formula2 = "=LET(x,2,x+3)" sheet2.Range("$C$10").Formula2 = "=LET(x,1,y,2,z,3,x+y+z=x*y*z)" sheet2.Range("$C$14").Formula2 = "=LET(x,1,y,2,SUM(x,y))" sheet2.Range("$C$19").Formula2 = "=LET(rows,0,cols,0,OFFSET(C18:E18,rows,cols))" Dim range4 = sheet2.Range("$B$2").Font range4.Bold = True range4.Color = Color.FromArgb(CInt(&HFF172B4D)) range4.Size = 12.0R Dim range5 = sheet2.Range("$B$4,$B$8,$B$12,$B$16").Font range5.Bold = True range5.Size = 9.0R Dim range6 = sheet2.Range("$B$5,$B$9,$B$13,$B$17").Font range6.ThemeFont = ThemeFont.None range6.Bold = True range6.Color = Color.FromArgb(CInt(&HFFC00000)) range6.Name = "Consolas" range6.Size = 9.0R Dim range7 = sheet2.Range("$B$6,$B$10,$B$14,$B$19").Interior range7.ThemeColor = ThemeColor.Accent1 Dim range7Font = sheet2.Range("$B$6,$B$10,$B$14,$B$19").Font range7Font.Color = Color.White range7Font.Bold = True Dim range8 = sheet2.Range("$B$18").Interior range8.ThemeColor = ThemeColor.Accent1 range8.TintAndShade = 0.6 Dim sheet3 As IWorksheet = workbook.Worksheets.Add() sheet3.Name = "Case 3" Dim rngB2B4 = New Object(,) { {"LET local varible & custom name"}, {"This sheet includes a custom name ""user"" which be assigned ""White"" value"}, {"user"}} sheet3.Range("$B$2:$B$4").Value = rngB2B4 Dim rngC4 = "White" sheet3.Range("$C$4").Value = rngC4 Dim rngB6B8 = New Object(,) { {"Always use let local varible first"}, {"=LET(user,""Andy"",""The actual user is: ""&user)"}, {"Result"}} sheet3.Range("$B$6:$B$8").Value = rngB6B8 Dim rngB10B12 = New Object(,) { {"Use custom name if not avalible local varible"}, {"=LET(user,user,""The actual user is: ""&user)"}, {"Result"}} sheet3.Range("$B$10:$B$12").Value = rngB10B12 sheet3.Range("$C$8").Formula2 = "=LET(user,""Andy"",""The actual user is: ""&user)" sheet3.Range("$C$12").Formula2 = "=LET(user,user,""The actual user is: ""&user)" Dim range10 = sheet3.Range("$B$2").Font range10.Bold = True range10.Color = Color.FromArgb(CInt(&HFF172B4D)) range10.Size = 12.0R Dim range11 = sheet3.Range("$B$3").Font range11.Bold = True range11.Color = Color.Gray range11.Name = "Segoe UI" range11.Size = 9.0R Dim range12 = sheet3.Range("$B$6,$B$10").Font range12.ThemeFont = ThemeFont.None range12.Bold = True range12.ColorIndex = -4105 range12.Name = "Segoe UI" range12.Size = 9.0R Dim range13 = sheet3.Range("$B$7,$B$11").Font range13.ThemeFont = ThemeFont.None range13.Bold = True range13.Color = Color.FromArgb(CInt(&HFFC00000)) range13.Name = "Consolas" range13.Size = 9.0R Dim range14 = sheet3.Range("$B$4").Interior range14.ThemeColor = ThemeColor.Accent1 range14.TintAndShade = 0.6 Dim range15 = sheet3.Range("$B$8,$B$12").Interior range15.ThemeColor = ThemeColor.Accent1 Dim range15Font = sheet3.Range("$B$8,$B$12").Font range15Font.Color = Color.White range15Font.Bold = True Dim sheet4 As IWorksheet = workbook.Worksheets.Add() sheet4.Name = "Case 4" Dim sheet4B2 = "LET nested" sheet4.Range("$B$2").Value = sheet4B2 Dim sheet4B4B6 = New Object(,) { {"Always use the current scope varible"}, {"=LET(var,""First scope"",LET(var,""Second scope"",var))"}, {"Result"}} sheet4.Range("$B$4:$B$6").Value = sheet4B4B6 Dim sheet4B8B10 = New Object(,) { {"Use the top scope varible if not found the avalible varible in current scope"}, {"=LET(var,""First scope"",LET(var,var,var&"" [from the second scope]""))"}, {"Result"}} sheet4.Range("$B$8:$B$10").Value = sheet4B8B10 sheet4.Range("$C$6").Formula2 = "=LET(var,""First scope"",LET(var,""Second scope"",var))" sheet4.Range("$C$10").Formula2 = "=LET(var,""First scope"",LET(var,var,var&"" [from the second scope]""))" Dim range41 = sheet4.Range("$B$2").Font range41.Bold = True range41.Color = Color.FromArgb(CInt(&HFF172B4D)) range41.Size = 12.0R Dim range42 = sheet4.Range("$B$4,$B$8").Font range42.Bold = True range42.Size = 9.0R Dim range43 = sheet4.Range("$B$5,$B$9").Font range43.Bold = True range43.Color = Color.FromArgb(CInt(&HFFC00000)) range43.Name = "Consolas" range43.Size = 9.0R Dim range44 = sheet4.Range("$B$6,$B$10").Interior range44.ThemeColor = ThemeColor.Accent1 Dim range44Font = sheet4.Range("$B$6,$B$10").Font range44Font.Color = Color.White range44Font.Bold = True Dim sheet5 As IWorksheet = workbook.Worksheets.Add() sheet5.Name = "Case 5" Dim sheet5B2 = "LET simplify the complex formula" sheet5.Range("$B$2").Value = sheet5B2 Dim sheet5B4B13 = New Object(,) { {"Filter the data to show one person"}, {"=LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange),""-"",filteredRange))"}, {"Rep"}, {"Amy"}, {"Fred"}, {"Amy"}, {"Fred"}, {"Fred"}, {"Amy"}, {"Fred"}} sheet5.Range("$B$4:$B$13").Value = sheet5B4B13 Dim sheet5C6C13 = New Object(,) { {"Region"}, {"East"}, {"South"}, {"West"}, {"North"}, {"West"}, {"East"}, {"North"}} sheet5.Range("$C$6:$C$13").Value = sheet5C6C13 Dim rngD6D9 = New Object(,) { {"Product"}, {"Apple"}, {"Banana"}, {"Mango"}} sheet5.Range("$D$6:$D$9").Value = rngD6D9 Dim rngE6E13 = New Object(,) { {"Profit"}, {1.33R}, {0.09R}, {1.85R}, {0.82R}, {1.25R}, {0.72R}, {0.54R}} sheet5.Range("$E$6:$E$13").Value = rngE6E13 Dim rngG7G8 = New Object(,) { {"Rep"}, {"Result"}} sheet5.Range("$G$7:$G$8").Value = rngG7G8 Dim rngH7 = "Fred" sheet5.Range("$H$7").Value = rngH7 Dim rngD11D13 = New Object(,) { {"Banana"}, {"Apple"}, {"Mango"}} sheet5.Range("$D$11:$D$13").Value = rngD11D13 Dim sheet5B16B19 = New Object(,) { {"Generate all dates between May 1, 2020 and May 15, 2020"}, {"=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))"}, {"Start"}, {"End"}} sheet5.Range("$B$16:$B$19").Value = sheet5B16B19 Dim rngC18C19 = New Object(,) { {DateTime.Parse("5/1/2020 12:00:00 AM")}, {DateTime.Parse("5/15/2020 12:00:00 AM")}} sheet5.Range("$C$18:$C$19").Value = rngC18C19 Dim rngG18 = "Result" sheet5.Range("$G$18").Value = rngG18 sheet5.Range("$H$8").Formula2 = "=LET(filterCriteria,H7,filteredRange,FILTER(B7:E13,B7:B13=filterCriteria),IF(ISBLANK(filteredRange),""-"",filteredRange))" sheet5.Range("$H$18").Formula2 = "=LET(dates,SEQUENCE(C19-C18+1,1,C18,1),FILTER(dates,WEEKDAY(dates,2)<6))" Dim sheet5Range1 = sheet5.Range("$C$18:$C$19,$H$18:$H$28") sheet5Range1.NumberFormat = "yyyy/m/d" Dim sheet5Range2 = sheet5.Range("$B$2").Font sheet5Range2.Bold = True sheet5Range2.Color = Color.FromArgb(CInt(&HFF172B4D)) sheet5Range2.Size = 12.0R Dim range51 = sheet5.Range("$B$4,$B$16").Font range51.Bold = True range51.Name = "Segoe UI" range51.Size = 9.0R Dim sheet5Range3 = sheet5.Range("$B$5,$B$17").Font sheet5Range3.Bold = True sheet5Range3.Color = Color.FromArgb(CInt(&HFFC00000)) sheet5Range3.Name = "Consolas" sheet5Range3.Size = 9.0R Dim sheet5Range4 = sheet5.Range("$B$6:$E$6,$B$18:$B$19").Font sheet5Range4.Bold = True Dim range52 = sheet5.Range("$B$6:$E$6,$B$18:$B$19").Interior range52.Color = Color.FromArgb(CInt(&HFFD9E1F2)) range52.Pattern = Pattern.Solid Dim sheet5Range5 = sheet5.Range("$G$7").Interior sheet5Range5.ThemeColor = ThemeColor.Accent1 Dim sheet5Range5Font = sheet5.Range("$G$7").Font sheet5Range5Font.Color = Color.White sheet5Range5Font.Bold = True Dim sheet5Range6 = sheet5.Range("$G$8,$G$18").Interior sheet5Range6.ThemeColor = ThemeColor.Accent1 sheet5Range6.TintAndShade = 0.6 sheet5.Range("$C:$C").AutoFit() sheet5.Range("$H:$H").ColumnWidth = 10 ' save to an excel file workbook.Save("LetFunction.xlsx")