SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Writing VBA macro

Reply
N/A
Posts: 0

Writing VBA macro

Somewhere I thought I saw something about writing a vba function using ODS markup.

I think I need to write an event sub (specifically, selectionChange) to each worksheet of an excel workbook. I'm using excelxp. Is there a way to do this? If so, are there any examples?
SAS Super FREQ
Posts: 8,721

Re: Writing VBA macro

Hi:
Perhaps you're thinking of this paper:
http://www.lexjansen.com/pharmasug/2003/sasinstitute/sas129.pdf
That outlines how to set an Excel macro to open when your ODS created file is opened.
cynthia
N/A
Posts: 0

Re: Writing VBA macro

Thank you, Cynthia;

I think this was the one I was thinking of. (good news)
I was looking for a way to write some code for each sheet and it doesn't look like it can be done (bad news)

I want to monitor when a cell has been clicked, which you'd think was a workbook event, but apparently MS thinks otherwise - this is a worksheet event. The difference is that apparently the event has to be in the module for each worksheet. That would be ok, if I knew in advance how many there were, but I don't, hence the need to write it on the fly.

Back to the drawing board.
SAS Super FREQ
Posts: 8,721

Re: Writing VBA macro

Hi...
I guess I think of the workBOOK as being a LIBRARY and the workSHEET as being a dataset in the LIBRARY. So if I wanted to know where a cell was clicked I think it would belong in an event at the lower level, (rather than the higher level) -- since the cells that are being clicked are entirely contained in a WORKSHEET and the WORKSHEET(s) is/are entirely contained in a WORKBOOK.

The only things I can set for an ENTIRE WORKBOOK are (generally) pull-down menu items. I don't understand the hierarchy of things you can do in Excel -- would VBScript or OLE-DB or .COM help you in any way?? Or are they all lumped under the general VBA macro category?

cynthia
N/A
Posts: 0

Re: Writing VBA macro

Color me impressed - 12:30 in the morning - don't you SAS people go to bed??!! You're related to the waitress I met last week who had a heart attack while in HS for drinking massive cups of coffee and caffeine pills.

I agree with you - I'm trying to figure this stuff by the seat of my pants as well, and I guess I'm, actually stunned is the word, that I can't monitor a mouse click globally, which is why my first inclination is to assume it's me, and that I don't understand something.

Afa the vbscript or ole-db or .com stuff - I don't know any thing about them, so you're guess is as good as mine.
SAS Super FREQ
Posts: 8,721

Re: Writing VBA macro

Hi,
Don't be impressed. The times get posted in Eastern time. I'm in Mountain time zone. It was 10:30 pm. I always do one last check of mail, etc before I head off to sleep.
cynthia
N/A
Posts: 0

Re: Writing VBA macro

The context of events tends to catch me out too. I suspect that there may be many ways to skin this cat, but I haven't found any.

I'm going to send a plea to my SAS consultant friend who is very much more knowledgeable in the realm of MS integration and ask him whether he has dealt with and solved this problem.

I expect he'll come up for himself if he has an idea.

Kind regards

David
Contributor
Posts: 42

Re: Writing VBA macro

David,

I got your email. I don't really code VBA but I will give it a shot here.

A Workbook consists of worksheet objects which consists of cell objects. A mouse click event such as the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "You just changed " & Target.Address
End Sub

...occurs at the worksheet level. Why? Because a cell is part of the worksheet. You could probably wire an event handler that would bubble it up but I am a C# guy and not a VBA person. In C# we could write a delegate to achieve this result.

The ExcelXP tagset is going to simply generate XML. The easiest way to determine what should be there is to write the code in Excel, save the workbook as an XML workbook, then determine what was reflected in the XML for the VBA code. I would then gen the worksheet in XML, read it back in, and insert the needed macro code in its place.

The above is an educated stab at the issue but I hope it helped a bit.

Hi Cynthia!

Alan Churchill
Contributor
Posts: 42

Re: Writing VBA macro

Ok, I did a little more research on this issue. First of all, I would not use ODS to tackle this problem. I believe this is the wrong direction.

You can use Visual Studios Tools for Office to code a C# event handler for Excel.

http://msdn2.microsoft.com/en-us/library/d2tx7z6d(VS.80).aspx

Read in the SAS data using my free (not a string attached) Data Management utility. The code for handling a click event then becomes pretty easy. My utility can be found here:

http://utilities.savian.net
N/A
Posts: 0

Re: Writing VBA macro

Thank you Alan.

I didn't think you would use VBA, you've (gently) rebuked me often enough for using old technologies. But I did suspect you would have a trick using a newer method.

Kind regards

David
SAS Super FREQ
Posts: 8,721

Re: Writing VBA macro

I should have known it was THAT small a world.

Thanks, David for passing this on to Alan & ...

HI! Alan [aka SAS2Microsoft guru ] ;-).

cynthia
N/A
Posts: 0

Re: Writing VBA macro

It certainly is Cynthia. I posted a question for a friend elsewhere and showed her this resource since I will be elsewhere for a few weeks.

She asked me how often people were posting and I commented that with a group that included a person on GMT, another on Mountain time and one on Eastern Australian Summer Time, there was often someone to answer a question.

With conferences, it was almost inevitable we'd know each other.

Kind regards

David
N/A
Posts: 0

Re: Writing VBA macro

Such a lively and informed conversation, I'm almost embarassed to add anything here, especially since I got an answer to my own question.

Originally, I thought I would just have to copy the event "SelectionChange" to the "ThisWorkbook" module and thing being what they were, the event would bubble up through the hierarchy of sheets to the workbook and the handler would handle.

And I was close. But, since this wasn't horseshoes, I didn't quite get the cigar.

At the sheet level, the handler is called SheetSelectionChange()...
Once I put in the Sub WorkBook_SheetSelectionChange(worksheet, range)
things worked as expected - even for MS.

The actual code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox ("Worksheet:" & Sh.Name & " address: " & Target.Address)
End Sub

To paraphrase Robert Krulwitz' Lobster Song: Read, Digest, Rectify

I appreciate the discussion - I haven't read it all yet, but will.

Thank you,
Richard Wright
Contributor
Posts: 42

Re: Writing VBA macro

Richard,

Microsoft has the best development models in the world, bar none. I would point you to .NET 3.5 which is light years ahead of where anyone else is at this stage.

However, you are working with technology that is very old (VBA) and things have matured quite a bit since VBA was developed. VBA is now deprecated but it should still work for any foreseeable future.

Alan
N/A
Posts: 0

Re: Writing VBA macro

I've got to work with what I've got, and it ain't much. I've worked for the state of Texas for some 20 years and grown somewhat accustomed to doing without - a lot of things. Not all that big on VBA or Excel, but they wanted something (to correct conceptual mistakes), so I'm giving it to them and learning all the while. The upside of working in a depleted environment is that sometimes you're forced to develop techniques that escape other folks.

I could go on for a bit, but no sense in wasting bandwidth over this.

But I do appreciate you're input, and I've taken at least a cursory look at the sites suggested, and will probably look at them in more depth when time allows.
Ask a Question
Discussion stats
  • 14 replies
  • 586 views
  • 0 likes
  • 3 in conversation