Thursday, 20 August 2009

Excel evaluated functions

Yay, finally, something else obscure to blog about - evaluated functions in excel


What you say? use Real english? - well if you insist. I'm talking about the ability to treat a text string in an excel cell as if it were a formula, and to evaluate it (work out what the answer would be - happy?)


Suppose, for example, you had a spreadsheet set out as follows:





The boxes in yellow are those you have highlighted for user input. The aim of the spreadsheet is to allow the user to select a range of dates to add the values together for.


The value in E4 is the result of a formula which reads

=CONCATENATE("=sum(",E2,":",G2,")")


This creates a nice formula as seen in cell E4, but it's no use to us at present, because excel sees it as text and doesn't treat it as a formula. (Typically, you might put an intermediate result like this into a hidden column or row somewhere so as not to confuse the user)


The typical (but problematic) answer given to this conunderum on the net is as follows:

1) right-click on e4 and select "Name a range"
2) modify the "Refers to" field as shown below and press ok




3) in your target (answer) cell enter =Name (in our case the cell will read =Mysum.) You end up with a spreadsheet that looks something like this....


Which is exactly what you wanted, right? - errm it might look that way, but watch what happens if you change one of the source values - let's say we change a6 to 999



Oops - the data value has changed - but the calculated total has not. Why not? Well that's because when Excel decides whether a cell needs recalculating, it works out whether any of the cells that the formula depends upon have changed.

In this case E5 depends upon E4, E4 depends upon E2 and G2. However E2 and G2 are plain text, they don't depend on anything - they're not cell references (at least as far as excel knows). Because none of the prerequisites have changed, there's no need to recalculate.

There are 2 easy work-rounds for this. You can change either of the values in E2 or G2, or force a complete recalculation using ctrl-shift-F9.

But is there a more elegant solution that doesn't require user input? - use your noggin - i wouldn't have asked the question if there weren't :)

unfortunately, it requires the use of a (very small) macro, which means you need to use a macro-enabled workbook, but you can't have everything.

So the new way to implement is like this:

1) press ALT-F11 to bring up the VBA interface

2) Add a module to your spreadsheet as follows:






In the module we're going to create a very small UDF (user defined function)


Presented here for easy cut and paste:

Public Function MyEval(s)
Application.Volatile True ' forced recalc every time
MyEval = Evaluate(s)
End Function


3) save and close the VBA interface.

4) the results cell (E5 in our example) should now read =Myeval(CONCATENATE("=",E4)).

5) We also need to modify cell E4 to remove the "=" in front of the sum

=CONCATENATE("sum(",E2,":",G2,")")



Once you've set this up, you will find that the result updates automatically every time a data value changes.



Job done



So how does this little piece of magic work?



The key is in the line


Application.Volatile True



This tells excel that the function value cannot be depended upon, and that the value should be recalculated on every worksheet change. Without this line, the macro would function in exactly the same way as the earlier method using cell names. It's the same method Excel uses internally to ensure that functions like now() get update regularly.

Enjoy

Wednesday, 28 January 2009

Skippy's List

If you've never seen it then "Skippy's list" is a laugh a minute.

http://skippyslist.com/list/

213 things Skippy is no longer allowed to do in the U.S. Army.

Technical resources

Having one technical issue in this blog so far, it was with suprise that I was able to use it to assist in a technical query for someone on the activedir.org mailing list

If you don't know this list, it's an amazing resource for those involved in implementing and maintaining AD, run by a published author on AD and populated with the highest density of AD professionals I've ever encountered. If you work with AD, subscribe.

Friday, 6 July 2007

Remote service management

OK, first technical issue of the new blog - and welcome :)

I had an issue today where the application team responsible for the application support on a new implementation I'm managing wanted the rights to stop and start services.

As the application support team are also a subset of the users, and as the team as a whole are Windows developers, I am unwilling to give them full local admin rights - I need to ensure any OS changes go through the internal change control process and I get the distinct feeling that simply mandating this in the support policy wasn't going to cut it. :(

I used subinacl.exe from the resource kit to grant the necessary access to the services.

subinacl /service servicename /grant="domain\usergroup"=QSETOI

This grants the domain user group members the following permissions on the services:

Query Service Config
Query Service Status
Enumerate Dependent Services
StarT Service
StOp Service
Interrogate Service

All this is as documented in KB288129

I supplied the relevent people with the relevent command line tools (PSService, SC.exc, NetSVC) but none of them appeared to work - they were all getting access denied.

Some googling, KBing and head-scratching later I found details of some changes that were made in W2K3 SP1 to the default DACL of the service controller (the rights given to remote users had beed reduced by default, and an API had been added that allowed the security on the service manager to be changed. (See http://msdn2.microsoft.com/en-us/library/ms684323.aspx)

So, to grant the users access to the service controller, it was as simple as using

sc sdset SCMANAGER D:(A;;CCLCRPRC;;;AU)(A;;CCLCRPRC;;;IU)(A;;CCLCRPRC;;;SU)(A;;CCLCRPWPRC;;;SY)(A;;KA;;;BA)S:(AU;FA;KA;;;WD)(AU;OIIOFA;GA;;;WD)

(The important part of this sddl code is A;;CCLCRPRC;;;AU which gives authenticated users the same rights as interactive users - In W2K3 SP1 these rights are changed to A;;CC;;;AU)

Or so I thought.

With this solution in place, users were able to remotely query the services, but any attempt to stop or start them resulted in an access denied message

Checking the security event log, I found the following alert:


Object Open:
Object Server: SC Manager
Object Type: SC_MANAGER OBJECT
Object Name: ServicesActive
Handle ID: -
Operation ID: {0,90876741}
Process ID: 524
Image File Name: C:\WINDOWS\system32\services.exe
Primary User Name: SERVERNAME$
Primary Domain: DOMAINNAME
Primary Logon ID: (0x0,0x3F7)
Client User Name:
Client Domain:
Client Logon ID: (0x0,0xC657EA)

Accesses: DELETE
READ_CONTROL
WRITE_DAC
WRITE_OWNER
Connect to service controller
Create a new service
Enumerate services
Lock service database for exclusive access
Query service database lock state
Set last-known-good state of service database
Privileges: -
Restricted Sid Count: 0
Access Mask: 0xF003F

The info highlighted in red indicates that instead of just requesting the permissions required to enumerate, start and stop services, the command line tools were actually requesting FULL CONTROL access - which hadn't been given.

WARNING: If I had granted full control permissions, the users would have had the right to add a new service or modify one of the existing services. This service could be configured to point to any exe placed on the server. This could include an exe specifically written to add the users into the local administrators group.

So now my problem was with command line tools which were not coded to support this function.
To my surprise, however, when we tried to use the services.msc tool remotely, this worked exactly as designed - only requesting the exact permissions it needed to start and stop the services remotely. This was a better solution than I originally anticipated, and more in line with how the users actually wanted to work.

Hello and welcome

I've been meaning to set up a blog for some time, it seems I'm somewhat behind the wave.

My purpose in this blog is to document the little bits and twiddles of system design and configuration that I stumble across in everyday life, along with a few bits and pieces I find particularly humourous.

Take this as a warning, I have a somewhat corny sense of humour <:)