Writing, Technology, Web Programming, and Ideas


...Quisquilian nugaments for the modern age

Unexpected Behavior in Excel Regarding Names Collection

(This is an Excel-specific tech note)

Simple description:

Delete method of Name object fails, even though prior use of the named range succeeds.

The snippet to return a row number succeeds:
myRowNumber = CurrSheet.Range("LASTROW").Row
The snippet to delete the name “LASTROW” from the names collection fails (generating Runtime Error 1004):
CurrSheet.Names("LASTROW").Delete

I only discovered this in Excel 2000, so I don’t know if anything’s changed in newer versions. I hope to get time to find out soon.

I have a project that involves creating a new workbook from a template, and then each successive pass through the code causes the second worksheet to be copied to the end. In other words, my template file has one “cover” sheet as Sheet1, and a first “data” sheet as sheet2. Each new sheet added to the workbook is obtained by copying Sheet2 to the end, and replacing the data in it with data specific to the new sheet.

To accomplish this in code, the template file has NAMED RANGES assigned to Sheet2. They are Sheet-Level names only. Thus each succeeding copy from Sheet2 to the end results in a new sheet with its own Sheet-Level names, each of which is the same as the names in all other sheets (except Sheet1, the “cover” sheet).

After much trial and error, I discovered in the debugger that the Names collection for the current sheet (Sheet2) had only the Print Area in it, which makes no sense, given that I could dereference the Row property of the named range LASTROW just fine. Looking at the names collection for the Workbook, however showed that the names exist as expected.

Adding to the perplexing nature of this strange behavior is the fact that it has never failed before.

The clue that solved it was that the only time it ever failed was also the only time it was ever executed against the original Sheet2, from which all successive sheets were copied.

In other words, the copied sheets never exhibited this behavior of the Names collection.

The Bottom Line:

I believe each Workbook has only one Names collection. This explains why the Name Manager in newer versions of Excel lets you select whether a named range is Sheet-level or Workbook-level. (It also explains why some descriptions of Sheet-level names claim that an exclamation point is used internally to the Names collection to denote sheet-level names. I have not verified this with Excel 2000, but it makes sense.)

When you attempt to access the names collection for a given Worksheet, AND when that Worksheet has been created from a template file, the Named Ranges are manageable only as Workbook-Level names. If you copy the Worksheet with those names to the end of the sheets in the current Workbook, the name objects will be manageable as sheet-level objects.

The Workaround (for my case)

        If CurrSheet.Index = 2 Then
            ThisWbk.Names("LASTROW").Delete
        Else
            CurrSheet.Names("LASTROW").Delete
        End If


I’m sure a more robust method exists for dealing with names directly, possibly involving the Item, but I haven’t had time to explore it further.

UPDATE:

I never tried “activating” the sheet where the runtime error was thrown. But reading the following item at StackOverflow makes it seem like that might be an alternate way of solving this. I hope to get time to make the experiment. Until then, I’ll just add the link and you can see what I mean:

StackOverflow Questions: How do I avoid run-time error when a worksheet is protected in MS-Excel?

wp_query pagination: previous and next return nothing

(This is a WordPress-specific tech note.)

After creating a separate loop on a fullpage template for custom posts, I found it was not properly paginating after the maximum posts per page had been reached.

A quick search on Google or Bing should have returned this immediately. Instead it took a great deal of hunting through the WordPress Codex, and several helpful blogs, so I wrote it up here to make sure it’s findable.

$wp-query: If you try to search for “$wp_query vs WP_query” or “$wp_query pagination” you get many useless results, and only by digging through them would you discover that the php variable $wp_query is a global object. (Something that significant ought to turn up sooner in search results.)

So if you decide to use a different variable as the result of a call to the function WP_query() (or as is more likely, you copy code from an example and it uses a different variable), the pagination functions do not work. They evidently rely on $wp_query as the active object to use in pagination.

So whether you use get_previous_posts_link() or previous_posts_link() or the similar ones for “next,” you must have an instance of $wp_query containing the result of your call to WP_query().

To see what I mean, consider the following snippet involving custom posts:

	<?php /** Query Arguments */ ?>
	<?php $paged = ( get_query_var( 'paged' ) ) ? get_query_var( 'paged' ) : 1; ?>
	<?php $args = array('posts_per_page' => 8, 'post_type' => 'my_custom_post_type', 'paged' => $paged ); ?>

	<?php /** Hold $wp_query */ ?>
	<?php $temp_wp_query = $wp_query; ?>
	<?php $loop = null; ?>

	<?php /** Custom $wp_query */ ?>
	<?php $loop = new WP_Query( $args ); ?>
	<ul>
		<?php while ( $loop->have_posts() ) : $loop->the_post(); ?>
			<li><h3><?php the_title(); ?></h3>
	
			<div class="entry-content">
			<?php the_content(); ?>
			</div></li>
		<?php endwhile; ?>
		<?php /** Loop Pagination */ ?>
		<div id="loop-nav-next-prev">
		<div class="loop-nav-previous">
			<?php previous_posts_link( '← Newer Posts' ); ?>
		</div>
		<div class="loop-nav-next">
			<?php next_posts_link( 'Older Posts →' ); ?>
		</div>
		</div>
		
		<?php /** Restore Post Data */ ?>
		<?php wp_reset_postdata(); ?>
	
		<?php /** Restore value of $wp_query */ ?>
		<?php $wp_query = null; ?>
		<?php $wp_query = $temp_wp_query; ?>
	</ul>

Apologies for all the inline php… that’s the code I was working with. Anyway, there’s only one thing preventing this code from working correctly:

The WP_query() result is assigned to the variable $loop.

So looping thru posts works up to the number of posts_per_page as you would expect, but the calls to previous_posts_link() and next_posts_link() return nothing.

If you instead use $wp_query as the variable assigned by WP_query(), you’ll get the usual expected results for pagination.

Site Changeover – Back To Basics

Reopening under the same, old management, this site has been renamed. (Nameserver updates to follow.)

(You should be able to navigate to the updated site using the new name, quixologies.com in a few days.)

Having recently capped off my involvement with the world of music, I decided to focus on the site’s other core areas of interest. This site will still be covering items related to design, philosophy, life, and marketing of your creative efforts. However I will leave the business of music and musical gear reviews to those who are much closer to the stage than I now am. I spent a lot of years in music, but I also have spent a lot of years in the world of ideas, business, philosophy, and of course, anything to do with computers.

This changeover is a full, back-to-square-one event. The design I am currently using is one I made in a hurry, just to alter the look, and to introduce the new site name, “quixologies“. The older articles will stay for now, but eventually I will roll them off of the site altogether.

I have removed a few items, below, that no longer apply to this website, and anything minor having dead links.

The time to announce upcoming projects has not arrived, but here are some ideas about what will happen over the next few months: First, I will complete the layout, which I will retain for several more months, perhaps until Spring. I’m waiting on more complete implementation of HTML5 and CSS3 before bothering with a redesign, primarily because the current one is quite flexible and looks decent at nearly all screen widths. The nav, header, and background will all be changing, and new pages will be added to support the projects I have in the works. So the site will look quite different, but the layout will be similar.

I will be adding site comments to certain articles, and working up a mobile version that isn’t so restrictive as most mobile versions seem to be these days.

Since the focus of coverage is actually narrowing a bit, it will be easier to update the site with new articles, and several are already planned.

Look for updates regarding hardware, since computers running here at the newly-christened Quixolarium are due for updates.

In anticipation of my biggest project to date (which I am well into), I have acquired a new keyboard, with the classic, IBM Model M layout and clicky feel. Article to follow!

2013 looks to be a volatile year, but one where we can all prosper if we stay focused and ready to handle rapid change.

K-Bob’s Quixolarium — No longer boogyin’, but still tilting at epistomological windmills

Great Moments In Sport – Men’s 10,000 Meter

I don’t do sports here, as a rule guideline, but I just watched one of the greatest Olympic moments, ever.


Britain’s Mohamed Farah and USA’s Galen Rupp, who trained together in Portland, Oregon, take Gold and Silver, respectively.

This is a great story, and it was a very emotional moment for both young men. Britain had never taken a Gold in this event, so the home crowd was uproarious in it’s support in the packed stadium. (As an audio guy, I love uproar!)

Congratulations to Mr. Farah and Team Great Britain, and to Mr. Rupp and Team USA, and to the man who coached both of them: Olympic great, Alberto Salazar. This was one to remember, for more reasons than I could possibly list.

To support Team USA, go to the donations page on the “official” Team USA website, TeamUSA.org (I just checked: it is the official website, according to Google’s coverage of the 2012 Olympics).

August Is Hot: How To Keep Beer Cold

I found this on the web, while searching for something better than the cheapo foam insulators I had laying around.

Make your own can coolerMmmmmm, edible!

The author provides better comparisons than the delicious, make-your-own variety shown here, and even tells you which one you can buy that does the best job. You should check it out, over at myscienceproject.

The good news is that even cheapo foam coolers with adverts on them do a decent job of keeping the can’s contents cool. However, Thermos sells a high-end version that does the best job, according to the research done at myscienceproject.

I’m going to order several today, and I will update this article later after I test them in a week or so.

Advertisement Loudness and the CALM Act

A press release by ATSC describes upcoming seminars on loudness management and the Commercial Advertisement Loudness Management (CALM) Act. Setting aside the fact that I keep seeing it reported as the “Commercial Audio Loudness Management Act,” the Act will begin to be enforced in a few months, and it’s good to see broadcasters planning for implementation. The law is the law.

One might wonder why no technology-based solution was put forth to end the problem. It can’t be that hard to monitor an average loudness and lower the volume on material that is vastly-different from the average. I know early attempts were not satisfactory, but with the ability to perform a delay-based analysis of the audio/video stream, the problem ought to have vanished by now. Instead, it has become the broadcasters problem. Don’t get me wrong, I’m sure they can deal with it. But it never should have required a law.

Line Arrays: Not Just For High-End Tours?

I recently noticed a press release in the ProSoundNetwork twitter feed for these Renkus-Heinz loudspeakers. You’ll notice that in this picture, at least three setup types involving the Renkus-Heinz speakers seem to indicate you could truck them in for a gig in almost any situation, and gain the benefits of line array systems for your average, working-stiff band.

Renkus-Heinz LoudspeakersCF101LA & CFX101LA MODULAR POINT SOURCE ARRAY
Photo: Renkus-Heinz

So I called the US marketing office for Renkus-Heinz and asked the burning question: “Is anyone using these speakers in mobile setups for working bands?” The short answer I got is, “yes, and they are also being installed in ‘small’ venues” (where “small” is marketing speak for “your typical dive that sells beer and puts hand-stamps on the kids who come to listen to poorly-paid bands”).

All of which brings up a favorite topic of discussion of mine, namely line arrays. Over the next few weeks, I plan to write about the line array, and its usefulness in certain situations, and hopefully some pointers on relative costs for the cash-strapped band. I’m sure that, going into it, your typical PA speakers from the usual sources will be a lot cheaper and easier to bang around, en route to the pool party gig or the local VFW Hall event. But I’m hoping bands with plans to move up in the world can benefit from gear made for sound professionals, without first scoring the requisite “Sponsored Tour.”

Digital technology has brought costs down so that working bands can deliver better quality sound. But a gap still exists between what bands can usually afford and the gear used by audio pros to support tours, perform installations, and reinforce sound at festivals. I plan to explore the nature of that gap, and how your band can take advantage of the latest gear without going broke.

Well, without becoming even more broke than usual. Stay tuned!

Buy A Brain Tumor Support T-Shirt

quixologies CafePress Shop

Support Brain Tumor Research

Every grey ribbon shirt you buy contributes to the American Brain Tumor Association, to help fund research, and support the families affected by this disease

 

BNN: the Boogie News Network

News to help you boogie forward

=============

 
© Quixologies, February 5, 2014