1<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 2<html xmlns="http://www.w3.org/1999/xhtml" lang="en"> 3 4<!-- #BeginTemplate "../jkp-ads_aspx.dwt" --> 5 6<head> 7<meta content="width=device-width, initial-scale=1" name="viewport" /> 8<meta content="text/html; charset=utf-8" http-equiv="Content-Type" /> 9<!-- #BeginEditable "doctitle" --> 10<title>Storing meta information about Excel cells</title> 11 12<!-- #EndEditable --> 13<!-- #BeginEditable "headsection" --> 14<meta name="description" content="Learn how to robustly store and manage Excel cell settings using structured, maintainable techniques for workbooks." /> 15<meta name="keywords" content="Excel cell settings, Excel VBA, Excel best practices, maintainable spreadsheets, spreadsheet design" /> 16<meta content="en-us" http-equiv="Content-Language" /> 17<!-- #EndEditable -->
17<script type="application/ld+json"> 18{ 19"@context": "https://schema.org", 20"@type": "Article", 21"mainEntityOfPage": { 22"@type": "WebPage", 23"@id": "https://jkp-ads.com/articles/excel-store-meta-cell-info.aspx" 24}, 25"headline": "Store cell Info", 26"description": "A way to store meta information about a cell using a named range", 27"keywords": "Excel cell settings, Excel VBA, Excel best practices, maintainable spreadsheets, spreadsheet design", 28"author": { 29"@type": "Person", 30"name": "Jan Karel Pieterse", 31"url": "https://jkp-ads.com" 32}, 33"publisher": { 34"@type": "Organization", 35"name": "JKP Application Development Services", 36"logo": { 37"@type": "ImageObject", 38"url": "https://jkp-ads.com/images/jkp-adslogo.png" 39} 40} 41} 42</script>
42 43
44<script type="application/ld+json"> 45{ 46 "@context": "https://schema.org", 47 "@type": "Organization", 48 "name": "JKP Application Development Services", 49 "url": "https://jkp-ads.com/", 50 "logo": "https://jkp-ads.com/images/jkp-adslogo.gif", 51 "founder": { 52 "@type": "Person", 53 "name": "Jan Karel Pieterse", 54 "url": "https://jkp-ads.com/about.aspx" 55 }, 56 "contactPoint": { 57 "@type": "ContactPoint", 58 "email": "[email protected]", 59 "contactType": "customer support", 60 "availableLanguage": ["en", "nl"] 61 }, 62 "sameAs": [ 63 "https://www.linkedin.com/in/jankarelpieterse"] 64} 65</script>
65 66<!-- #BeginEditable "scripting" --> 67<!-- Scripting area here --> 68 69<!-- #EndEditable --> 70<!-- code from topnavscript.asp file--> 71<!-- Include the JavaScript file -->
72<script src="/includes/topnav_script.js" type="text/javascript"></script>
72 73<meta content="General" name="rating" /> 74<meta content="no" http-equiv="imagetoolbar" /> 75<meta content="Copyright ©, Jan Karel Pieterse All Rights Reserved" name="copyright" /> 76<!--meta tags to stop Bing Chat from training its AI on my content.--> 77<meta content="nocache" name="robots"> 78<meta content="noarchive" name="robots"> 79<link href="../jkp-ads.css" rel="stylesheet" type="text/css" /> 80<link rel="canonical" href="https://jkp-ads.com/articles/excel-store-meta-cell-info.aspx" /> 81</head> 82 83<body id="top"> 84 85<!-- #EndEditable --> 86<div id="container"> 87 <div id="banner"> 88 <div id="logoleft"> 89 <a href="../index.aspx"> 90 <img alt="Home" src="../images/jkp-adslogo.gif" /></a></div> 91 <div id="logomiddle"> 92 <div class="search-container" role="search"> 93 <form action="https://www.google.com/search" method="get"> 94 <input name="ie" type="hidden" value="UTF-8" /> 95 <input name="oe" type="hidden" value="UTF-8" /> 96 <div class="smtxt"> 97 <label for="googlesearch">Site search</label></div> 98 <input id="googlesearch" maxlength="255" name="q" placeholder="Search.." size="12" type="text" value="" /> 99 <button class="dropbtn" name="btnG" type="submit" value="?"> 100 ?</button> 101 <input name="domains" type="hidden" value="https://jkp-ads.com" /> 102 <input hidden="true" name="sitesearch" type="radio" value="" /> 103 <input checked="checked" hidden="true" name="sitesearch" type="radio" value="https://jkp-ads.com" /> 104 </form> 105 </div> 106 </div> 107 </div> 108 <nav aria-labelledby="myTopnav"><div id="myTopnav" class="topnav" role="navigation"> 109<a href="/index.aspx?lang=en">Home</a> 110 <div class="dropdown"> 111 <button class="dropbtn" onclick="menuClick('jkpDropdown1')"> 112 <a href="#" title="Products: Examples of products by JKP-ADS">Products ▼</a> 113 </button> 114 <div id="jkpDropdown1" class="dropdown-content"> 115 <a href="/products.aspx?lang=en" title="Products: Examples of products by JKP-ADS">Products</a> 116 <a href="/reftreeanalyser.aspx?lang=en" title="RefTreeAnalyser: Excel formula auditing to the max">â RefTreeAnalyser</a> 117 <a href="/productsremediation.aspx?lang=en" title="Remediation utility: a tool to salvage Excel files which are about to get corrupt">Remediation utility</a> 118 <a href="/books.aspx?lang=en" title="Excel Books">Books</a> 119 </div> 120 </div> 121 <div class="dropdown"> 122 <button class="dropbtn" onclick="menuClick('jkpDropdown2')"> 123 <a href="#" title="Services by JKP-ADS: What can we do for you.">Services ▼</a> 124 </button> 125 <div id="jkpDropdown2" class="dropdown-content"> 126 <a href="/services.aspx?lang=en" title="Services by JKP-ADS: What can we do for you.">Services</a> 127 <a href="/trainings.aspx?lang=en" title="Excel and VBA trainings and courses: what we have to offer.">Excel Training</a> 128 <a href="/events.aspx?lang=en" title="Excel Events: Events organised by us">Events</a> 129 <a href="/recommendations.aspx?lang=en" title="Recommendations by customers of JKP-ADS">Recommendations</a> 130 </div> 131 </div> 132 <div class="dropdown"> 133 <button class="dropbtn" onclick="menuClick('jkpDropdown3')"> 134 <a href="#" title="Free downloads by JKP-ADS">Downloads ▼</a> 135 </button> 136 <div id="jkpDropdown3" class="dropdown-content"> 137 <a href="/download.aspx?lang=en" title="Free downloads by JKP-ADS">Downloads</a> 138 <a href="/excel-flexfind.aspx?lang=en" title="Flexfind Add-in for Excel (free)">Flexfind</a> 139 <a href="/excel-name-manager.aspx?lang=en" title="Name Manager Add-in For Excel (free)">Name Manager</a> 140 <a href="/reftreeanalyser.aspx?lang=en" title="RefTreeAnalyser: Excel formula auditing to the max">â RefTreeAnalyser</a> 141 <a href="/excel-rule-manager.aspx?lang=en" title="Rule Manager Add-in For Excel (free)">Rule Manager</a> 142 <a href="/excel-spill-manager-addin.aspx?lang=en" title="Spill Manager Add-in For Excel (free)">Spill Manager</a> 143 <a href="/excel-workbook-info-addin.aspx?lang=en" title="Workbook Info Add-in For Excel (free)">Workbook Info</a> 144 <a href="/accessdownloads.aspx?lang=en" title="Free Downloads For Access">Downloads For Access</a> 145 </div> 146 </div> 147 <div class="dropdown"> 148 <button class="dropbtn" onclick="menuClick('jkpDropdown4')"> 149 <a class="active" href="#" title="Excel Articles">Article index ▼</a> 150 </button> 151 <div id="jkpDropdown4" class="dropdown-content"> 152 <a href="/articles/excelarticles.aspx?lang=en" title="Excel Articles">Article index</a> 153 <a href="/articles/excel-tips.aspx?lang=en" title="Numerous tips and tricks about Excel and VBA">Tips & tricks</a> 154 <a href="/articles/addinsandsetupfactory.aspx?lang=en" title="add-in Installation using Setup Factory">Add-in Installation</a> 155 <a href="/articles/excel-add-ins-fail-to-load.aspx?lang=en" title="Excel Add-ins fail to load because they are blocked">Add-ins do not load</a> 156 <a href="/articles/apideclarations.aspx?lang=en" title="Declaring API functions in 64 bit Office">API declarations</a> 157 <a href="/articles/buildexceladdin.aspx?lang=en" title="Building an add-in for E
157xcel">Build Excel Add-in</a> 158 <a href="/articles/catchpaste.aspx?lang=en" title="Catch Paste: prevent paste on cells with validation">Catch Paste</a> 159 <a href="/articles/chartanequation00.aspx?lang=en" title="Chart an Equation: enter a mathematical equation and get it plotted">Chart an Equation</a> 160 <a href="/articles/excelchallenge.aspx?lang=en" title="Excel Challenges">Challenges</a> 161 <a href="/articles/circularreferences.aspx?lang=en" title="Working with circular references in Excel">Circular References</a> 162 <a href="/articles/excelcoauthoring.aspx?lang=en" title="Co-authoring in Office 365. A great feature with an unexpected side-effect">Co-Authoring</a> 163 <a href="/articles/controlhandler00.aspx?lang=en" title="Use one event handler for multiple controls on a worksheet">Control Events</a> 164 <a href="/articles/controls.aspx?lang=en" title="Using controls on worksheets">Controls</a> 165 <a href="/articles/corruptfiles.aspx?lang=en" title="Corrupt Files: trying to salvage your precious work">Corrupt Files</a> 166 <a href="/articles/distributemacro00.aspx?lang=en" title="Create an add-in from your macro">Create add-ins</a> 167 <a href="/articles/customfind.aspx?lang=en" title="Custom Find using CallByName">Custom Find</a> 168 <a href="/articles/exceldatatypes.aspx?lang=en" title="Using Data Types in Excel">Data Types</a> 169 <a href="/articles/exceldataentrywithvalidationhelp.aspx?lang=en" title="Creating a user-friendly Data Validation in Excel Displaying help out of the way">Data Entry Help</a> 170 <a href="/articles/noevents00.aspx?lang=en" title="Control When Events Fire And When Not">Disable Events</a> 171 <a href="/articles/vbedocking.aspx?lang=en" title="Restore the docking of your VBE windows">Docking VBE Windows</a> 172 <a href="/articles/exceluserformdragdrop.aspx?lang=en" title="VBA: Enabling Drag and Drop between two Listboxes on a Userform">Drag And Drop</a> 173 <a href="/articles/excel2007fileformat.aspx?lang=en" title="The Excel xlsx and xlsm File Format">Excel FileFormat</a> 174 <a href="/articles/exceltablerecipe.aspx?lang=en" title="Batchprocessing: Calculating many scenario's with one model">Excel data table recipe</a> 175 <a href="/articles/excel2007tables.aspx?lang=en" title="Working with Excel Data Tables">Excel Tables</a> 176 <a href="/articles/excel2007tablesvba.aspx?lang=en" title="Working with Excel Data Tables (VBA)">Excel Tables (VBA)</a> 177 <a href="/articles/embeddedexcel00.aspx?lang=en" title="Creating webpages with Excel Web App mashups">Excel Web App Mashups</a> 178 <a href="/articles/fixlinks2udf.aspx?lang=en" title="Fixing Links to UDFs in Excel add-ins">Fix Links to UDFs</a> 179 <a href="/articles/formulawrapper.aspx?lang=en" title="Formula Wrapper: prevent # errors in your sheets">Formula Wrapper</a> 180 <a href="/articles/importtext.aspx?lang=en" title="Importing textfiles into a worksheet">Import textfiles</a> 181 <a href="/articles/excelsimpleinventory.aspx?lang=en" title="A simple Inventory system Built in Excel">Inventory System</a> 182 <a href="/articles/keepuserformontop.aspx?lang=en" title="Keeping Userforms on top of SDI windows in Excel 2013 and up">Keep Userform On Top</a> 183 <a href="/articles/excel-lambda-function-basics.aspx?lang=en" title="The Excel Lambda function; the basics">Lambda function basics</a> 184 <a href="/articles/excel-lambda-function-examples.aspx?lang=en" title="The Excel Lambda function; Examples">Lambda function examples</a> 185 <a href="/articles/excel-lambda-function-convert-vba.aspx?lang=en" title="The Excel Lambda function; converting VBA UDFs to Lambda functions">Lambda From VBA</a> 186 <a href="/articles/autosizelistbox.aspx?lang=en" title="Listbox AutoSize: Autosize the column widths of your listbox">Listbox AutoSize</a> 187 <a href="/articles/vba-listbox-control.aspx?lang=en" title="Listbox Control: An All-VBA Formatted List Box Control">Listbox Control</a> 188 <a href="/articles/leastsquares.aspx?lang=en" title="Least Squares: Fitting curves to your data using least squares">Least Squares</a> 189 <a href="/articles/excel-modern-userform.aspx?lang=en" title="Creating modern-looking userforms in VBA">Modern Userform</a> 190 <a href="/articles/excel-name-manager-history.aspx?lang=en" title="The history of the Name Manager add-in">Name Manager history</a> 191 <a href="/articles/objectlister.aspx?lang=en" title="Object Lister: Listing an Object's Properties and Methods">Object Lister</a> 192 <a href="/articles/excel-office-script-basics.aspx?lang=en" title="Office Script, the basics">Office Script basics</a> 193 <a href="/articles/excel-office-script-examples.aspx?lang=en" title="Office Script, some examples">Office Script examples</a> 194 <a href="/articles/excel-office-script-call-from-power-automate.aspx?lang=en" title="Office Script called from Power automate">Office Script Power automate</a> 195 <a href="/articles/performanceclass.aspx?lang=en" title="A class module to measure your VBA performance">Performance Class</a> 196 <a href="/articles/slicers.aspx?lang=en" title="Slicers for Pivot tables and regular tables">Pivottable Slicers</a> 197 <a href="/articles/preventopenevent.aspx?lang=en" title="Preventing auto_open and Workbook_Open events from running">Prevent Open Event</a> 198 <a href="/articles/excelnames.aspx?lang=en" title="All there is to know about range names in Excel">Range Names</a> 199 <a href="/articles/registerudf00.aspx?lang=en" title="Registering UDFs with Excel to show help in the function wizard">Register UDFs</a> 200 <a href="/articles/excel-addin-remove-problem.aspx?lang=en" title="Getting rid of the missing add-in error message">Remove Add-in</a> 201 <a href="/articles/vba-rich-text-box-control.aspx?lang=en" title="Rich Text box Control: An All-VBA Rich Text Box Control">Rich Text Control</a> 202 <a href="/articles/round2digits.aspx?lang=en" title="Rounding numbers to a number of significant digits">Round2Digits</a>
203 <a href="/articles/selectarange.aspx?lang=en" title="Select a range using VBA">Select a range (VBA)</a> 204 <a href="/articles/excel-select-text-in-textbox-on-userform.aspx?lang=en" title="Selecting text in a multi-line userform text box">Selecting Textbox text</a> 205 <a href="/articles/excel-addin-installer.aspx?lang=en" title="Code to make your add-in install itself">Self installing Add-in</a> 206 <a href="/articles/showpicture00.aspx?lang=en" title="Show a dynamic picture in Excel">Show Picture</a> 207 <a href="/articles/spreadsheettemplate.aspx?lang=en" title="A template to start your spreadsheet project with">Spreadsheet Template</a> 208 <a class="active" href="/articles/excel-store-meta-cell-info.aspx?lang=en" title="A way to store meta information about a cell using a named range">Store cell Info</a> 209 <a href="/articles/troubleshooting-excel.aspx?lang=en" title="Troubleshooting Excel Problems">Troubleshooting</a> 210 <a href="/articles/styles.aspx?lang=en" title="Styles in Excel">Styles in Excel</a>
211 <a href="/articles/formulatranspose.aspx?lang=en" title="Transpose formulas in a Table">Transpose Table</a> 212 <a href="/articles/treeview.aspx?lang=en" title="An MSForms (all VBA) treeview">Treeview control</a> 213 <a href="/articles/undowithvba00.aspx?lang=en" title="How to program Undo in Excel VBA">Undo With Excel VBA</a> 214 <a href="/articles/updateanaddin.aspx?lang=en" title="Updating An add-in through Internet">Update An add-in</a> 215 <a href="/articles/webquery.aspx?lang=en" title="A Webquery with parameters">WebQuery</a> 216 <a href="/articles/excelwheeloffortune.aspx?lang=en" title="A wheel of fortune built entirely in Excel">Wheel of Fortune</a> 217 <a href="/articles/workbookopenbug.aspx?lang=en" title="Workbook Open Bug">Workbook Open Bug</a> 218 <a href="/articles/xmlandexcel00.aspx?lang=en" title="XML and Excel">XML and Excel</a> 219 </div> 220 </div> 221 <div class="dropdown"> 222 <button class="dropbtn" onclick="menuClick('jkpDropdown5')"> 223 <a href="#" title="Excel related Links I like">Links ▼</a> 224 </button> 225 <div id="jkpDropdown5" class="dropdown-content"> 226 <a href="/links.aspx?lang=en" title="Excel related Links I like">Links</a> 227 <a href="/privacy.aspx?lang=en" title="Our Company Privacy Policy">Privacy Policy</a> 228 <a href="/sitemap.aspx?lang=en" title="jkp-ads.com Site Map">Site Map</a> 229 </div> 230 </div> 231 <div class="dropdown"> 232 <button class="dropbtn" onclick="menuClick('jkpDropdown6')"> 233 <a href="#" title="Our contact details">Contact ▼</a> 234 </button> 235 <div id="jkpDropdown6" class="dropdown-content"> 236 <a href="/contact.aspx?lang=en" title="Our contact details">Contact</a> 237 <a href="/about.aspx?lang=en" title="About Jan Karel Pieterse">About Me</a> 238 </div> 239 </div> 240 <a href="/newsletter.aspx?lang=en" title="Subscribe to our newletter (and view our Newsletter archive)">Newsletter</a> 241 <div class="dropdown"> 242 <button class="dropbtn" onclick="menuClick('jkpDropdown7')"> 243 <a href="#" title="Excel pages by Ron de Bruin">Ron de Bruin ▼</a> 244 </button> 245 <div id="jkpDropdown7" class="dropdown-content"> 246 <a href="/rdb/?lang=en" title="Excel pages by Ron de Bruin">Ron de Bruin</a> 247 <a href="/rdb/about.htm?lang=en" title="About by Ron de Bruin">About</a> 248 <a href="/rdb/tips.htm?lang=en" title="Windows Excel tips by Ron de Bruin">Windows tips</a> 249 <a href="/rdb/mac.htm?lang=en" title="Mac Tips by Ron de Bruin">Mac Tips</a> 250 </div> 251 </div> 252<a class="icon" href="javascript:void(0);" onclick="hamburgerClick()">☰</a><div class="dropdown"> <button class="dropbtn" onclick="menuClick('jkpDropdown8')" > <a href="#" ><img alt="Taal/Language" title="Kies je taal/Choose your language" src="../images/envlag.png" style="float: left; margin:0px 5px 0px 0px" height="18" width="36"> â¼</a> </button> <div id="jkpDropdown8" class="dropdown-content"> <a class="active" href="/articles/excel-store-meta-cell-info.aspx?lang=en" title="You are here: English"><img src="../images/envlag.png" style="float:left;margin:0 5px 0 0" height="18" width="36">English</a> <a href="#" title="Deze pagina is niet beschikbaar in het Nederlands"><img src="../images/nlvlagna.png" style="float:left;margin:0 5px 0 0" height="18" width="36">Nederlands</a> <a href="#" title="Diese Seite ist nicht auf Deutsch verfügbar"><img src="../images/devlagna.png" style="float:left;margin:0 5px 0 0" height="18" width="36">Deutsch</a> <a href="#" title="Cette page nâest pas disponible en français"><img src="../images/frvlagna.png" style="float:left;margin:0 5px 0 0" height="18" width="36">Français</a> <a href="#" title="Esta página no está disponible en español"><img src="../images/esvlagna.png" style="float:left;margin:0 5px 0 0" height="18" width="36">Español</a> </div></div> 253</div></nav><br style="clear: both" /> 254 <nav aria-label="Breadcrumb" class="breadcrumb"><div id="crumbs"><a href="/index.aspx?lang=en" title="Home">Home</a> > <a href="/articles/excelarticles.aspx?lang=en" title="Excel Articles">Article index</a> > Store cell Info</div></nav><br /> 255 <div id="content" role="main"> 256 257 <!-- #BeginEditable "content" --> 258 <h1>Storing meta information about Excel cells</h1> 259 <h2>Content</h2> 260 <ul> 261 <li><a href="#background">
261Background</a></li> 262 <li><a href="#method">Method</a></li> 263 <li><a href="#implementation">Implementation</a></li> 264 </ul> 265 <h2 id="background">Background</h2> 266 <p>While I was designing my <a href="../excel-spill-manager-addin.aspx"> 267 Spill Manager for Excel</a> add-in I needed a way for the add-in to quickly 268 find spill ranges and to remember which spill ranges the add-in should manage 269 (which spills it needs to watch for size changes). I needed to be able to:</p> 270 <ol> 271 <li>Locate a spill's top-left cell. This has to be reliable, if the 272 user inserts or removes columns or rows or cell blocks, the spill's 273 top-left cell still must be simple to find</li> 274 <li>Store spill setttings: 275 <ol> 276 <li>whether to copy formatting across columns or down rows</li> 277 <li>whether to clean the formatting outside of the spill on a shrink</li> 278 <li>whether there is a header row/column to account for</li> 279 <li>whether there is a totals row/column</li> 280 <li>whether the spill range needs to be updated on each change automatically</li> 281 <li>the last size of the spill range, to be able to detect a change 282 in dimensions and act accordingly</li> 283 </ol> 284 </li> 285 <li>Save and retrieve the information easily.</li> 286 </ol> 287 <h2 id="method">Method</h2> 288 <p>At first I thought I'd use a set of range names per spill area, a range 289 name for each of those settings. That posed a couple of issues:</p> 290 <ul> 291 <li>The add-in would potentially create many range names</li> 292 <li>Maintaining separate range names for each spill area is hard.</li> 293 </ul> 294 <p>Then I had an idea: I'll use the LET function for this purpose. After 295 all, a range name is nothing more than a named formula. 296 <a href="https://jkp-ads.com/articles/excelnames.aspx#A_Step_Further:_A_Formula_In_A_Defined_Name"> 297 Any valid formula you can write in a cell, can also be put in a name</a>. 298 Spill manager now keeps one range name (local to the worksheet in question) 299 for each spill, using a formula like this one:</p> 300 <p>Name: Sheet1!_SpillManager_Watch_19dd36c539f_5798b737</p> 301 <p>RefersTo formula:</p> 302 <p class="vbacode">=LET(rng,'Spill Manager Training'!$B$3,direction,"row",cleanupOnShrink,1,hasHeader,1,hasFooter,1,liveUpdates,1,lastRows,5,lastCols,6,rng) 303 </p> 304 <p>Note how the final argument of the LET function repeats the first one, 305 so the range name returns the spill range's top-left cell. This means I 306 can use the range name in a formula exactly the same as if the name referred 307 to the range directly. It also means VBA code like this "just works":</p> 308 <p class="vbacode">Set rng = Range("Sheet1!_SpillManager_Watch_19dd36c539f_5798b737")</p> 309 <p>And rng will now contain a direct reference to the spill anchor.</p> 310 <h2 id="implementation">Implementation</h2> 311 <p>All we need now is some code that parses the LET formula to extract the 312 properties it stores. For that I devised a class module. The class uses 313 property names which match those in the LET formula above exactly. This 314 means we can use the CallByName VBA function to dynamically set the properties 315 in a simple loop. Here's that class. I called it "clsGetMetaFromCell". The 316 ExtractProperties routine does the heavy lifting in retrieving the settings 317 from the formula.</p> 318 <div class="vbacode"> 319 <span class="kwrd">Option</span> <span class="kwrd">Explicit</span><br /> 320 <br /> 321 <span class="kwrd">Private</span> mscleanupOnShrink 322 <span class="kwrd">As</span> <span class="kwrd">String</span> 323 <span class="rem">'1</span><br /> 324 <span class="kwrd">Private</span> msdirection <span class="kwrd">As</span> 325 <span class="kwrd">String</span> <span class="rem">'"row"</span><br /> 326 <span class="kwrd">Private</span> mshasFooter <span class="kwrd">As</span> 327 <span class="kwrd">String</span> <span class="rem">'1</span><br /> 328 <span class="kwrd">Private</span> mshasHeader <span class="kwrd">As</span> 329 <span class="kwrd">String</span> <span class="rem">'1</span><br /> 330 <span class="kwrd">Private</span> mslastCols <span class="kwrd">As</span> 331 <span class="kwrd">String</span> <span class="rem">'6</span><br /> 332 <span class="kwrd">Private</span> mslastRows <span class="kwrd">As</span> 333 <span class="kwrd">String</span> <span class="rem">'5</span><br /> 334 <span class="kwrd">Private</span> msliveUpdates <span class="kwrd">As</span> 335 <span class="kwrd">String</span> <span class="rem">'1</span><br /> 336 <span class="kwrd">Private</span> moWatchedCell <span class="kwrd">As</span> 337 Range<br /> 338 <span class="kwrd">Private</span> msmetaNameFormula 339 <span class="kwrd">As</span> <span class="kwrd">String</span><br /> 340 <br /> 341 <span class="kwrd">Private</span> <span class="kwrd">Sub</span> ExtractProperties(rngName 342 <span class="kwrd">As</span> <span class="kwrd">String</span>)<br /> 343 <span class="rem">
343'Example formula in named range</span><br /> 344 <span class="rem">'=LET(rng,'Spill Manager Training'!$B$3,direction,"row",cleanupOnShrink,1,hasHeader,1,hasFooter,1,liveUpdates,1,lastRows,5,lastCols,6,rng)</span><br /> 345 <span class="kwrd">Dim</span> items <span class="kwrd">As</span> 346 <span class="kwrd">Variant</span><br /> 347 <span class="kwrd">Dim</span> ct <span class="kwrd">As</span> 348 <span class="kwrd">Long</span><br /> 349 <span class="rem">'Since the LET function returns the range, 350 we can retrieve the spill cell by evaluating the formula:</span><br /> 351 <span class="kwrd">Set</span> WatchedCell = Range(rngName)<br /> 352 items = Split(msmetaNameFormula, ",")<br /> 353 <span class="rem">'Now extract all properties we need, counting 354 from the right</span><br /> 355 <span class="rem">'Since the sheet name can contain a comma 356 we extract only the other properties.<br /> 357 'The following starts from the two-but-last comma, which 358 is the word "lastCols" in the formula:</span><br /> 359 <span class="kwrd">For</span> ct = <span class="kwrd">UBound</span>(items) 360 - 2 <span class="kwrd">To</span> 3 <span class="kwrd">Step</span> -2<br /> 361 <span class="rem">'We deliberately 362 use the same property names in this class as we have in the LET named 363 range</span><br /> 364 <span class="rem">'So we can use 365 CallByName to set the values.</span><br /> 366 <span class="rem">'Because of this 367 we need all properties to be string properties</span><br /> 368 <span class="rem">'It is left to 369 the user of the class to do conversions where needed</span><br /> 370 CallByName Me, 371 <span class="kwrd">CStr</span>(items(ct)), VbLet, <span class="kwrd"> 372 CStr</span>(items(ct + 1))<br /> 373 <span class="kwrd">Next</span><br /> 374 <span class="kwrd">End</span> <span class="kwrd">Sub</span><br /> 375 <br /> 376 <span class="kwrd">Public</span> <span class="kwrd">Sub</span> UpdateProperties2Name()<br /> 377 <span class="rem">'This routine builds a new RefersTo formula 378 and updates the name. That is relatively straightforward,<br /> 379 'so I left that for you to do :-). I don't think we can 380 get around hard-coding the property names here though.<br /> 381 'One needs to call this routine from outside of the class 382 after updating properties.</span><br /> 383 <span class="kwrd">End</span> <span class="kwrd">Sub</span><br /> 384 <br /> 385 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 386 <span class="kwrd">Get</span> cleanupOnShrink() <span class="kwrd">As</span> 387 <span class="kwrd">String</span><br /> 388 cleanupOnShrink = mscleanupOnShrink<br /> 389 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 390 <br /> 391 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 392 <span class="kwrd">Let</span> cleanupOnShrink(<span class="kwrd">ByVal</span> 393 newValue <span class="kwrd">As</span> <span class="kwrd">String</span>)<br /> 394 mscleanupOnShrink = newValue<br /> 395 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 396 <br /> 397 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 398 <span class="kwrd">Get</span> direction() <span class="kwrd">As</span> 399 <span class="kwrd">String</span><br /> 400 direction = msdirection<br /> 401 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 402 <br /> 403 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 404 <span class="kwrd">Let</span> direction(<span class="kwrd">ByVal</span> 405 newValue <span class="kwrd">As</span> <span class="kwrd">String</span>)<br /> 406 msdirection = newValue<br /> 407 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 408 <br /> 409 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 410 <span class="kwrd">
410Get</span> hasFooter() <span class="kwrd">As</span> 411 <span class="kwrd">String</span><br /> 412 hasFooter = mshasFooter<br /> 413 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 414 <br /> 415 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 416 <span class="kwrd">Let</span> hasFooter(<span class="kwrd">ByVal</span> 417 newValue <span class="kwrd">As</span> <span class="kwrd">String</span>)<br /> 418 mshasFooter = newValue<br /> 419 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 420 <br /> 421 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 422 <span class="kwrd">Get</span> hasHeader() <span class="kwrd">As</span> 423 <span class="kwrd">String</span><br /> 424 hasHeader = mshasHeader<br /> 425 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 426 <br /> 427 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 428 <span class="kwrd">Let</span> hasHeader(<span class="kwrd">ByVal</span> 429 newValue <span class="kwrd">As</span> <span class="kwrd">String</span>)<br /> 430 mshasHeader = newValue<br /> 431 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 432 <br /> 433 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 434 <span class="kwrd">Get</span> lastCols() <span class="kwrd">As</span> 435 <span class="kwrd">String</span><br /> 436 lastCols = mslastCols<br /> 437 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 438 <br /> 439 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 440 <span class="kwrd">Let</span> lastCols(<span class="kwrd">ByVal</span> 441 newValue <span class="kwrd">As</span> <span class="kwrd">String</span>)<br /> 442 mslastCols = newValue<br /> 443 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 444 <br /> 445 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 446 <span class="kwrd">Get</span> lastRows() <span class="kwrd">As</span> 447 <span class="kwrd">String</span><br /> 448 lastRows = mslastRows<br /> 449 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 450 <br /> 451 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 452 <span class="kwrd">Let</span> lastRows(<span class="kwrd">ByVal</span> 453 newValue <span class="kwrd">As</span> <span class="kwrd">String</span>)<br /> 454 mslastRows = newValue<br /> 455 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 456 <br /> 457 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 458 <span class="kwrd">Get</span> liveUpdates() <span class="kwrd">As</span> 459 <span class="kwrd">String</span><br /> 460 liveUpdates = msliveUpdates<br /> 461 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 462 <br /> 463 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 464 <span class="kwrd">Let</span> liveUpdates(<span class="kwrd">ByVal</span> 465 newValue <span class="kwrd">As</span> <span class="kwrd">String</span>)<br /> 466 msliveUpdates = newValue<br /> 467 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 468 <br /> 469 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 470 <span class="kwrd">Get</span> WatchedCell() <span class="kwrd">As</span> 471 Range<br /> 472 <span class="kwrd">Set</span> WatchedCell = moWatchedCell<br /> 473 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 474 <br /> 475 <span class="kwrd">Private</span> <span class="kwrd">Property</span> 476 <span class="kwrd">Set</span> WatchedCell(<span class="kwrd">ByVal</span> 477 oNewValue <span class="kwrd">As</span> Range)<br /> 478 <span class="kwrd">Set</span> moWatchedCell = oNewValue<br /> 479 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 480 <br /> 481 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 482 <span class="kwrd">
482Get</span> MetaName() <span class="kwrd">As</span> 483 <span class="kwrd">String</span><br /> 484 MetaName = msMetaName<br /> 485 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 486 <br /> 487 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 488 <span class="kwrd">Let</span> MetaName(<span class="kwrd">ByVal</span> 489 newValue <span class="kwrd">As</span> <span class="kwrd">String</span>)<br /> 490 msMetaName = newValue<br /> 491 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 492 <br /> 493 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 494 <span class="kwrd">Get</span> metaNameFormula() <span class="kwrd">As</span> 495 <span class="kwrd">String</span><br /> 496 metaNameFormula = msmetaNameFormula<br /> 497 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 498 <br /> 499 <span class="kwrd">Public</span> <span class="kwrd">Property</span> 500 <span class="kwrd">Let</span> metaNameFormula(<span class="kwrd">ByVal</span> 501 newValue <span class="kwrd">As</span> <span class="kwrd">String</span>)<br /> 502 msmetaNameFormula = newValue<br /> 503 ExtractProperties newValue<br /> 504 <span class="kwrd">End</span> <span class="kwrd">Property</span><br /> 505 </div> 506 <p>A Class doesn't do much without implementing it, so here is an example 507 routine that reads all names pertaining to my add-in:</p> 508 <div class="vbacode"> 509 <span class="kwrd">Sub</span> ProcessAllWatchedSpillCells()<br /> 510 <span class="kwrd">Dim</span> nm <span class="kwrd">As</span> 511 Name<br /> 512 <span class="kwrd">Dim</span> ws <span class="kwrd">As</span> 513 Worksheet<br /> 514 <span class="kwrd">Dim</span> cMeta <span class="kwrd">As</span> 515 clsGetMetaFromCell<br /> 516 <span class="kwrd">Dim</span> metas <span class="kwrd">As</span> 517 Collection<br /> 518 <span class="kwrd">Set</span> metas = <span class="kwrd"> 519 New</span> Collection<br /> 520 <span class="kwrd">For</span> <span class="kwrd">Each</span> 521 ws <span class="kwrd">In</span> Worksheets<br /> 522 <span class="kwrd">For</span> 523 <span class="kwrd">Each</span> nm <span class="kwrd">In</span> ws.Names<br /> 524 <span class="kwrd">If</span> 525 nm.Name <span class="kwrd">Like</span> "*_SpillManager_*" 526 <span class="kwrd">Then</span><br /> 527 <span class="kwrd">Set</span> 528 cMeta = <span class="kwrd">New</span> clsGetMetaFromCell<br /> 529 cMeta.MetaName 530 = nm.Name<br /> 531 <span class="rem">'The 532 next action also triggers the ExtractProperties routine inside clsGetMetaFromCell</span><br /> 533 cMeta.metaNameFormula 534 = nm.RefersTo<br /> 535 <span class="rem">'For 536 demonstration purposes only:</span><br /> 537 <span class="kwrd">With</span> 538 cMeta<br /> 539 MsgBox 540 "Cell: " & .WatchedCell.Address(False, False, , 541 <span class="kwrd">True</span>) & vbNewLine & _<br /> 542 543 "direction: " & .direction & vbNewLine & _<br /> 544 545 "footer: " & .hasFooter & vbNewLine & _<br /> 546 547 "header: " & .hasHeader & vbNewLine & _<br /> 548 549 "last cols: " & .lastCols & vbNewLine & _<br /> 550 551 "last rows: " & .lastRows & vbNewLine & _<br /> 552 553 "liveUpdates" & .liveUpdates<br /> 554 <span class="kwrd">End</span> 555 <span class="kwrd">With</span><br /> 556 <span class="rem">'Store 557 the list of properties so we can do something with them</span><br /> 558 metas.Add 559 cMeta<br /> 560 <span class="kwrd">End</span> 561 <span class="kwrd">
561If</span><br /> 562 <span class="kwrd">Next</span><br /> 563 <span class="kwrd">Next</span><br /> 564 <span class="rem">'Now loop through them to act upon them</span><br /> 565 <span class="kwrd">For</span> <span class="kwrd">Each</span> 566 cMeta <span class="kwrd">In</span> metas<br /> 567 <span class="rem">'If we made a 568 change to any property like</span><br /> 569 cMeta.direction = "some other direction"<br /> 570 <span class="rem">'then we need 571 to update the name</span><br /> 572 cMeta.UpdateProperties2Name<br /> 573 <span class="kwrd">Next</span><br /> 574 <span class="kwrd">End</span> <span class="kwrd">Sub</span><br /> 575 </div> 576 <p>See, I left something for you to do. I bet ChatGPT and its comrades are 577 perfectly capable of filling that gap.</p> 578 <div id="comments"> 579 <hr /> 580 <h2>Comments</h2> 581 <div commentsection="/includes/getcomments.aspx?page=articles/excel-store-meta-cell-info.aspx"> 582 Loading comments...</div> 583 <div commentform="/includes/comments.aspx"> 584 </div> 585 </div> 586 587 588 <!-- #EndEditable --></div> 589 <div id="footer" role="contentinfo"> 590 <div role="contentinfo">Jan Karel Pieterse <a href="mailto:[email protected]" title="Email Jan Karel Pieterse">[email protected]</a><br />Copyright 2026, All rights reserved.</div><div><p><a href="/products.aspx?lang=en" title="Products: Examples of products by JKP-ADS">Products</a> | <a href="/services.aspx?lang=en" title="Services by JKP-ADS: What can we do for you.">Services</a> | <a href="/download.aspx?lang=en" title="Free downloads by JKP-ADS">Downloads</a> | <a href="/articles/excelarticles.aspx?lang=en" title="Excel Articles">Article index</a> | <a href="/links.aspx?lang=en" title="Excel related Links I like">Links</a> | <a href="/contact.aspx?lang=en" title="Our contact details">Contact</a> | <a href="/newsletter.aspx?lang=en" title="Subscribe to our newletter (and view our Newsletter archive)">Newsletter</a> | <a href="/rdb/?lang=en" title="Excel pages by Ron de Bruin">Ron de Bruin</a></p></div></div> 591</div> 592 593</body> 594 595<!-- #EndTemplate --> 596 597</html>
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.