0

My Add-In sometimes crashes and takes up huge amounts of memory and is just generally bad performance while I iterate over a range and add hyperlinks.

My hyperlinks almost always follow the formula of URL + CellValue and I wonder if I can add this somehow to an entire range without looping to increase performance?

Here is an example of what I do, note, Excel doesn't seem to "respect" the screen updating, or maybe its just slow after sync, but I can watch it change cells row by row visually its so slow..

const Hyperlink_Settings_Obj = {
    address: null,
    documentReference: null,
    screenTip: null,
    textToDisplay: null,
}
    
context.workbook.application.suspendScreenUpdatingUntilNextSync();
var key_index = 0
for (var ri = 0; ri < Used_Rng_And_Props.rowCount; ++ri) {
    var val = Used_Rng_And_Props.values[ri][key_index]
    var cell = Used_Rng_And_Props.getCell(ri, key_index)
    var hlink_obj = JSON.parse(JSON.stringify(Hyperlink_Settings_Obj))
    hlink_obj.address = URL + val
    hlink_obj.textToDisplay = val
    cell.hyperlink = hlink_obj
}
FreeSoftwareServers
  • 2,271
  • 1
  • 33
  • 57
  • Have you tried using the =HYPERLINK(…) formula option. That way you can set a larger number of cells at the same time and also compute the URL and display text using other cell values. – Jakob Nielsen-MSFT Jan 30 '23 at 20:36
  • @JakobNielsen-MSFT Would I not have to jump through the cells anyway to get the value? I did look at it, but it didn't seem like I could make it work without a loop. Might still be worth trying to see if its performance is better tho. – FreeSoftwareServers Jan 30 '23 at 21:30
  • @JakobNielsen-MSFT I mean, that would work, but it would require a "helper" column, which feels hacky and wrong. Hyperlinks are a "feature" in my spreadsheets that aren't needed, I currently check if there is more then ~420 rows and don't apply if > – FreeSoftwareServers Jan 30 '23 at 21:40

0 Answers0