Column Calculations in Excel

In Excel X, it was possible to do things like “currentColumn + 1” because that version of Excel used the R1C1 notation (i.e. columns had a numeric notation, not alpha)

Now that I’ve switched to Excel 2008 and they’ve switch to the abbreivated “B2” (column B, row 2) notation, any script that I had that did column moves with math now break (since “B+1” isn’t valid). My work around for now is hard-coding column values, but that’s not always going to be possible.

So how does one handle such things now?

Wow, this is the heaviest math I’ve every done with AppleScript.

Since the (excel) alphabet has 26 letters, you can represent them with a base 26 (do you understand? I don’t know how to explain it correctly in English). Converting the decimals to base 26 wasn’t easy, but it worked! Yeah!

Script:

columnLetterBase26(26) --> "z"
columnLetterBase26(27) --> "aa"
columnLetterBase26(28) --> "ab"
columnLetterBase26(289) --> "kc"

on columnLetterBase26(aDecimal)
	try
		set aDecimal to aDecimal as integer
	on error
		error "The parameter given to convert decimals to base 26 wasn't a number." from aDecimal to integer
	end try
	
	if aDecimal < 1 then error "The parameter given is smaller than 1" from aDecimal to integer
	
	local Base26
	set Base26 to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
	
	-- search for largest exponent
	set exponent to 0
	repeat while (26 * (26 ^ exponent)) < aDecimal
		set exponent to exponent + 1
	end repeat
	
	-- fill string
	set repr26 to {}
	repeat while exponent ≥ 0
		
		-- find biggest suitable
		set baseNumber to 26
		repeat while (baseNumber * (26 ^ exponent)) > aDecimal
			set baseNumber to baseNumber - 1
		end repeat
		
		-- add letter
		set end of repr26 to item baseNumber of Base26
		
		-- addapt values
		set aDecimal to aDecimal - (baseNumber * (26 ^ exponent))
		set exponent to exponent - 1
		
	end repeat
	exponent
	
	
	return (repr26 as string)
end columnLetterBase26

Hope it helps,
ief2

Functional, but OMG that’s a complicated way to do it. Impressive nonetheless. :smiley:

(And while I understand the basic premise of baseX numbering, the math sometimes makes my head hurt. But I always solved the D’ni numeric puzzles…which are base25)

I was rather hoping Excel had some handy way to shortcut it. :stuck_out_tongue:

THANKS!

Of course you know my next question…what about in reverse? If I query a column location and Excel gives back “BB”, how would I convert the BB into a number to perform a calculation with, then use the script you just gave to turn in back into an Excel reference?

For example:

–Get current Excel column, such as (BB) or (Q)
–I want to move over 1 column (BB+1 = BC) or (Q+1 = R)

You can “translate” an A1 style range into R1C1 style range this way


tell application "Microsoft Excel"
	get address range "D12" reference style R1C1 --> "R12C4"
end tell

It’s actually more simple than decimal to base 26

Script:

on base26toInteger(stringRepr)
	local Base26
	set Base26 to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
	
	set stringRepr to stringRepr as string
	set strCount to count every character of stringRepr
	
	-- repeat with every character
	set decimalRepr to 0
	repeat with i from strCount to 1 by -1
		-- get last char
		set myChar to character -i of stringRepr
		
		-- get index of char
		set n to 1
		repeat while (item n of Base26) is not myChar
			set n to n + 1
			if n > 26 then error "Unknown character encountered" from myChar
		end repeat
		
		-- get exponent
		set exponent to i - 1
		
		-- get decimal repr and add to total
		set charToDec to n * (26 ^ exponent)
		set decimalRepr to decimalRepr + charToDec
	end repeat
	
	return decimalRepr as integer
end base26toInteger

base26toInteger("ab")

Hope it helps,
ief2

EDIT: I see StefanK was faster and has a better method
EDIT 2: My method has limitations: Column “FXSHRXW” is the maximum you can convert. FXSHRXX will give an error (a to large number)

Hi,

you can do this much faster without a repeat loop


set Base26 to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
set myChar to "X"
set n to offset of myChar in Base26
if n = 0 then error "Unknown character encountered" from myChar
n -- > 24

Is there a way to convert back if I already have a range in the R1C1 style?

if it’s a single cell, the same way


tell application "Microsoft Excel"
	range (get address of cell 12 of column 4) --> cell $D$12
end tell

PS: Do you know the Excel 2004 AppleScript Guide. Almost all functions are the same in Office 2008. It’s very helpful

Been working in Excel X up until today. Excel X in some ways was easier, because it was recordable. :stuck_out_tongue:

Does Amazon sell this reference, or do I have to buy it from elsewhere? Links would be helpful.

Here’s a link to the Excel package from microsoft.
http://www.microsoft.com/mac/developers/default.mspx?CTT=PageView&clr=99-21-0&target=4acff5ca-5863-4bb6-9a3b-09d2bc0d1dc71033&srcid=e1dbbe49-e45b-4606-bc00-dc5d3bd2d4601033&ep=7

I’m not sure what your issue is, applescript for Excel 2004 accepts R1C1 notation and (AFAIK) so does 2008.

tell application "Microsoft Excel"
	set myCell to range "d4"
	
	set formula r1c1 of myCell to "=R1C[3]"
	display dialog formula of myCell as text -- =G$1
	
	set myOtherCell to get offset myCell column offset 1
	display dialog (get address of myOtherCell) -- $E$4
	
	return first column index of myOtherCell -- 5
end tell

You also might want to look at the Convert Formula command.

The Applescript set up is very similar to VBA. to move one cell to the right the get offset command should be used as in above

Back to basics I guess…:confused:

Here’s one of many example what I have now, and I get an “object does no exist error” in Excel 2008 (worked fine in Excel X). I’d prefer to keep the R1C1 notation if possible, as I have quite a few code segments that look like this:

(This reliably finds an empty cell in a particular spreadsheet without worrying about Excel’s concept of a “used” cell being any format changes.)

			set currentRow to 1
			set currentColumn to 1
			repeat until formula of active cell is ""
				set currentRow to (currentRow + 1)
				set currentCell to ("R" & currentRow & "C" & currentColumn) as text
				select range currentCell
			end repeat

I also tried “select cell currentCell” as well, same error. I had assumed the new Excel just didn’t want the R1C1 notation. Comments above seem to indicate it will take the old notation, so I can only assume it’s some kind of syntax issue.

I’ve got quite a few Excel scripts around here, and wholesale notation changes would be annoying…the syntax changes are giving me a headache as it is. Starting to feel like it would be easier to start from scratch. :frowning: