Originally Posted by
stanl
To avoid having to register a .wsc, use GetObject() rather than CreateObject(). Should work within a macro.
Sorry, I meant to include an example, but Alberto dumped 6-9 inches of rain on Raleigh in about 2-3 hours. All the streets around our house were closed for flooding - bailed water in a 5-gallon pail for 4 hours - as we came within 1/8 inch of losing our downstairs. Anyway, bygones - sun is out now.
cut and paste this code in to a file named tn.wsc
<?xml version="1.0"?>
<component>
<?component error="true" debug="true"?>
<registration
description="Tn"
progid="Tn.WSC"
version="1.00"
classid="{9a3ef45a-5375-4e14-833f-a8793ab9e519}"
>
</registration>
<public>
<method name="GetTn">
<PARAMETER name="var"/>
</method>
</public>
<implements type="Behavior" id="Behavior"/>
<script language="VBScript">
<![CDATA[
function GetTn(var)
GetTn = TypeName(var)
end function
]]>
</script>
</component>
The script wizard takes care of assigning the classid.
Now open a spreadsheet and enter this macro [ for simplicity sake I hard-coded the location of the .wsc, but it consists of "script:" & path & filename.wsc ] and adjust for your situation.
Sub testwsc()
cWsc = "script:c:\test\tn.wsc"
Set Var = ActiveWorkbook.ActiveSheet
Set oWsc = GetObject(cWsc)
MsgBox oWsc.GetTn(Var)
End Sub
When run, it should display the TypeName [WorkSheet]. Not particulary impressive, but since the .wsc is an ascii file, you can add functions on the fly with elementary file i/o. It is also a neat way to work with Jscript arrays, or other activex components.
Stan