public static Excel.Range GetSheetRange(Worksheet sheet, string rangeString, Primer.RefType refType)
{
var parser = new Primer.RefParser(rangeString, ThisAddIn.MyApp, refType);
int rowNumber = parser.firstRowNumber;
int columnNumber = parser.firstColumnNumber;
int rowNumber2 = parser.secondRowNumber;
int columnNumber2 = parser.secondColumnNumber;
return(sheet.Range[sheet.Cells[rowNumber, columnNumber], sheet.Cells[rowNumber2, columnNumber2]]);
}
public void FormatRange()
{
/*
* Loop the rows to build the target range and clear range with Union
* Paste formats in the target range
*/
ThisAddIn.MyApp.DisplayAlerts = false;
ThisAddIn.MyApp.ScreenUpdating = false;
Excel.Range newTargetRange = targetCell.Offset[0, 1];
Excel.Range newMergeRange = targetCell;
Excel.Range newHeaderRange = targetCell;
Primer.RefParser targetParser = new Primer.RefParser(targetRange.Address, ThisAddIn.MyApp);
int rowNum_start = targetParser.firstRowNumber;
int colNum_start = targetParser.firstColumnNumber;
int rowNum_end = targetParser.secondRowNumber;
int colNum_end = targetRange.End[Excel.XlDirection.xlToRight].Column;
int shift = colNum_start;
Excel.Range formatRange;
ThisAddIn.MyApp.ScreenUpdating = false;
int rangeCount = 0;
for (int i = rowNum_start; i <= rowNum_end; i++)
{
Excel.Range row = targetSheet.Range[$"A{i}"].EntireRow;
if (shift + 1 <= colNum_end)
{
formatRange = targetSheet.Range[row.Cells[1, shift + 1], row.Cells[1, colNum_end]];
}
else
{
formatRange = targetCell.Offset[0, 1];
}
Excel.Range mergeRange = targetSheet.Range[row.Cells[1, colNum_start], row.Cells[1, shift]];
newTargetRange = ThisAddIn.MyApp.Union(newTargetRange, formatRange);
newMergeRange = ThisAddIn.MyApp.Union(newMergeRange, mergeRange);
Excel.Range headerRange = targetSheet.Range[row.Cells[1, shift], row.Cells[1, shift]];
newHeaderRange = ThisAddIn.MyApp.Union(newHeaderRange, headerRange);
mergeRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
rangeCount += 3;
if (rangeCount > 240)
{
ExecuteFormat(newTargetRange, newMergeRange, newHeaderRange, templateRange);
rangeCount = 0;
newTargetRange = targetCell.Offset[0, 1]; //resets the union ranges back to their defaults
newMergeRange = targetCell;
newHeaderRange = targetCell;
}
shift++;
}
ExecuteFormat(newTargetRange, newMergeRange, newHeaderRange, templateRange); //execute the final batch
ThisAddIn.MyApp.DisplayAlerts = true;
ThisAddIn.MyApp.ScreenUpdating = true;
}
public static void FollowLink()
{
//XlCall.Excel(XlCall.xlcAlert, "Follow Link Code Here!");
Excel.Application MyApp = (Excel.Application)ExcelDnaUtil.Application;
string link_reference = (string)MyApp.ActiveCell.Formula; //cell reference to go to
Primer.RefParser parser = new Primer.RefParser(link_reference, MyApp, Primer.RefType.A1);
if (parser.malformed == false)
{
parser.firstCell.Worksheet.Activate();
parser.firstCell.Select();
}
}
public static void SetArrayFormulas(string cellRange, string formula, Primer.RefType refType = Primer.RefType.A1)
{
var sheet = GetActiveSheet();
var parser = new Primer.RefParser(cellRange, ThisAddIn.MyApp, refType);
if (refType == Primer.RefType.R1C1)
{
//Need to convert back to A1
cellRange = parser.ConvertRangeA1_R1C1(cellRange);
}
Excel.Range setRange = GetSheetRange(sheet, cellRange, refType);
setRange.FormulaArray = formula;
sheet.Cells[1, 1].Calculate(); //this doesn't seem to do much..
}
public MenuBuilder()
{
Selection = ObjModel.GetSelection();
Parser = new Primer.RefParser(Selection.Address, ThisAddIn.MyApp);
CellRegion = GetRegion();
}
