PageSourceSearch

https://jkp-ads.com/articles/excel-store-meta-cell-info.aspx?lang=en

html jkp-ads.com collected 2026-10-02 10:12:03 UTC 41,841 bytes, 597 lines download raw bytes

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 &#9660;</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 &#9660;</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 &#9660;</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 &#9660;</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 &#9660;</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 &#9660;</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 &#9660;</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()">&#9776;</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> &gt; <a href="/articles/excelarticles.aspx?lang=en" title="Excel Articles">Article index</a> &gt; 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&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">
343'Example formula in named range</span><br />
344&nbsp;&nbsp;&nbsp;&nbsp;<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&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Dim</span> items <span class="kwrd">As</span>
346			<span class="kwrd">Variant</span><br />
347&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Dim</span> ct <span class="kwrd">As</span>
348			<span class="kwrd">Long</span><br />
349&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'Since the LET function returns the range, 
350			we can retrieve the spill cell by evaluating the formula:</span><br />
351&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Set</span> WatchedCell = Range(rngName)<br />
352&nbsp;&nbsp;&nbsp;&nbsp;items = Split(msmetaNameFormula, ",")<br />
353&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'Now extract all properties we need, counting 
354			from the right</span><br />
355&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'Since the sheet name can contain a comma 
356			we extract only the other properties.<br />
357&nbsp;&nbsp;&nbsp;&nbsp;'The following starts from the two-but-last comma, which 
358			is the word "lastCols" in the formula:</span><br />
359&nbsp;&nbsp;&nbsp;&nbsp;<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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'So we can use 
365			CallByName to set the values.</span><br />
366&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'Because of this 
367			we need all properties to be string properties</span><br />
368&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'It is left to 
369			the user of the class to do conversions where needed</span><br />
370&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CallByName Me,
371			<span class="kwrd">CStr</span>(items(ct)), VbLet, <span class="kwrd">
372			CStr</span>(items(ct + 1))<br />
373&nbsp;&nbsp;&nbsp;&nbsp;<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&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'This routine builds a new RefersTo formula 
378			and updates the name. That is relatively straightforward,<br />
379&nbsp;&nbsp;&nbsp;&nbsp;'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&nbsp;&nbsp;&nbsp;&nbsp;'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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;<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&nbsp;&nbsp;&nbsp;&nbsp;<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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;msmetaNameFormula = newValue<br />
503&nbsp;&nbsp;&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Dim</span> nm <span class="kwrd">As</span> 
511			Name<br />
512&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Dim</span> ws <span class="kwrd">As</span> 
513			Worksheet<br />
514&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Dim</span> cMeta <span class="kwrd">As</span> 
515			clsGetMetaFromCell<br />
516&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Dim</span> metas <span class="kwrd">As</span> 
517			Collection<br />
518&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Set</span> metas = <span class="kwrd">
519			New</span> Collection<br />
520&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">For</span> <span class="kwrd">Each</span> 
521			ws <span class="kwrd">In</span> Worksheets<br />
522&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">For</span>
523			<span class="kwrd">Each</span> nm <span class="kwrd">In</span> ws.Names<br />
524&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">If</span> 
525			nm.Name <span class="kwrd">Like</span> "*_SpillManager_*"
526			<span class="kwrd">Then</span><br />
527&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Set</span> 
528			cMeta = <span class="kwrd">New</span> clsGetMetaFromCell<br />
529&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cMeta.MetaName 
530			= nm.Name<br />
531&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'The 
532			next action also triggers the ExtractProperties routine inside clsGetMetaFromCell</span><br />
533&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cMeta.metaNameFormula 
534			= nm.RefersTo<br />
535&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'For 
536			demonstration purposes only:</span><br />
537&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">With</span> 
538			cMeta<br />
539&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox 
540			"Cell: " &amp; .WatchedCell.Address(False, False, ,
541			<span class="kwrd">True</span>) &amp; vbNewLine &amp; _<br />
542&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
543			"direction: " &amp; .direction &amp; vbNewLine &amp; _<br />
544&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
545			"footer: " &amp; .hasFooter &amp; vbNewLine &amp; _<br />
546&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
547			"header: " &amp; .hasHeader &amp; vbNewLine &amp; _<br />
548&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
549			"last cols: " &amp; .lastCols &amp; vbNewLine &amp; _<br />
550&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
551			"last rows: " &amp; .lastRows &amp; vbNewLine &amp; _<br />
552&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
553			"liveUpdates" &amp; .liveUpdates<br />
554&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">End</span>
555			<span class="kwrd">With</span><br />
556&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'Store 
557			the list of properties so we can do something with them</span><br />
558&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;metas.Add 
559			cMeta<br />
560&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">End</span>
561			<span class="kwrd">
561If</span><br />
562&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Next</span><br />
563&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">Next</span><br />
564&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'Now loop through them to act upon them</span><br />
565&nbsp;&nbsp;&nbsp;&nbsp;<span class="kwrd">For</span> <span class="kwrd">Each</span> 
566			cMeta <span class="kwrd">In</span> metas<br />
567&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'If we made a 
568			change to any property like</span><br />
569&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cMeta.direction = "some other direction"<br />
570&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class="rem">'then we need 
571			to update the name</span><br />
572&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cMeta.UpdateProperties2Name<br />
573&nbsp;&nbsp;&nbsp;&nbsp;<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&nbsp;<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.