ExcelCOM UDF

[randallc]

Hi,

i'll attach the spreadsheet the script uses

Cna someone do that as planned so I can look into this?

can you show me how an Excel macro does this (ie example macro script?)?

As before, this would help

i ended up just using VBA to do my forms

Can you please show me the vba code to folllow?; I suspect it may be accessible via scriptcontrol object and it may not be vba re-formatting for com which is the problem.

@cameronsdad; are you still looking at the re-write udf?

Best, randall

[seandisanti]

Hi,

Cna someone do that as planned so I can look into this?

As before, this would help

Can you please show me the vba code to folllow?; I suspect it may be accessible via scriptcontrol object and it may not be vba re-formatting for com which is the problem.

@cameronsdad; are you still looking at the re-write udf?

Best, randall

I thought i had done it, but i'm not at work right now. if you create a new spreadsheet, then go to view>toolbars>control toolbox, you'll be able to add a checkbox, which will be named checkbox1 by default.

with vba you can use IsNull() to check if the value returned is Null... here's an example vba to find out the return value of a checkbox with the triplestate property set to true...

Private Sub CheckBox1_Click()
If IsNull(CheckBox1.Value) Then MsgBox "it's null", vbOKOnly, "result"
If CheckBox1.Value = True Then MsgBox "true", vbOKOnly, "result"
If CheckBox1.Value = False Then MsgBox "false", vbOKOnly, "result"
End Sub

***edit*** forgot to speak on the re-write...

i am still working on a new excel udf, i'm almost done adding in most of the functionality that you already have, except i'm handling things a little differently. rather than having each of the functions call a main function that does the work (i'm sorry if that's over simplification, but it's really about i was able to make of yours because of the limited time i've been able to invest studying it). I've been delayed because of new duties at work which is where i do most of my stuff (off time is spent with the son, the wife, or the girlfriend). i'm about done with automating all of my new tasks though, so i should have alot more free time here this week or next to really get it finished to where i'd be comfortable with a beta release. I've added alot of error checking, and i'm trying to make it as simple as possible to understand in addition to using, that way i'm sure there will be alot more community additions to it on top of what maintenance i do. I hope you'll like it.

Edited June 6, 2006 by cameronsdad

[randallc]

OK,

thanks;

good luck,

I'll look at the vba

Best, randall

[blitzkrg]

(off time is spent with the son, the wife, or the girlfriend)

you have both?

you dog.. :-)

[seandisanti]

you have both?

you dog.. :-)

now it's the wife and the fiance... atleast till the first divorce is final...

[SmOke_N]

now it's the wife and the fiance... atleast till the first divorce is final...

[seandisanti]

but the fiance comes with extra girlfriends, so that's a big perc.

[randallc]

Hi,

Checkboxes;

does this do it?

I am not sure how to set the "null" to test?

;_XLCheckBox.au3 0_2
$FilePath=@ScriptDir&"\Book5.xls"
$s_CheckBox="CheckBox1"
$s_AnswerValue=_XLCheckBoxValue($FilePath,2, $s_CheckBox)
;=====================================================================================================
MsgBox(0,"","$s_AnswerValue="&$s_AnswerValue)
Func _XLCheckBoxValue($sPath,$i_Sheet, $s_CheckBoxName)
    $code=         "Function CheckBoxValue(Path)"
    $code &= @LF & 'Set xlBook = GetObject(Path)'
    $code &= @LF & 'Dim mysheet:  set mysheet = xlBook.sheets('&$i_Sheet&')'
    $code &= @LF & 'If IsNull(mysheet.'&$s_CheckBoxName&'.value) Then CheckBoxValue="Null"'
    $code &= @LF & 'If mysheet.'&$s_CheckBoxName&'.value = True Then CheckBoxValue="True"'
    $code &= @LF & 'If mysheet.'&$s_CheckBoxName&'.value = False Then CheckBoxValue="False"'
    $code &= @LF & 'End function   '
    ConsoleWrite($code)
    $vbs = ObjCreate("ScriptControl")
    $vbs.language="vbscript"
    $vbs.addcode($code)
    return $vbs.run("CheckBoxValue",$sPath)
    $vbs=""
EndFunc

Best, Randall

[seandisanti]

Hi,

Checkboxes;

does this do it?

I am not sure how to set the "null" to test?

;_XLCheckBox.au3 0_2
$FilePath=@ScriptDir&"\Book5.xls"
$s_CheckBox="CheckBox1"
$s_AnswerValue=_XLCheckBoxValue($FilePath,2, $s_CheckBox)
;=====================================================================================================
MsgBox(0,"","$s_AnswerValue="&$s_AnswerValue)
Func _XLCheckBoxValue($sPath,$i_Sheet, $s_CheckBoxName)
    $code=         "Function CheckBoxValue(Path)"
    $code &= @LF & 'Set xlBook = GetObject(Path)'
    $code &= @LF & 'Dim mysheet:  set mysheet = xlBook.sheets('&$i_Sheet&')'
    $code &= @LF & 'If IsNull(mysheet.'&$s_CheckBoxName&'.value) Then CheckBoxValue="Null"'
    $code &= @LF & 'If mysheet.'&$s_CheckBoxName&'.value = True Then CheckBoxValue="True"'
    $code &= @LF & 'If mysheet.'&$s_CheckBoxName&'.value = False Then CheckBoxValue="False"'
    $code &= @LF & 'End function   '
    ConsoleWrite($code)
    $vbs = ObjCreate("ScriptControl")
    $vbs.language="vbscript"
    $vbs.addcode($code)
    return $vbs.run("CheckBoxValue",$sPath)
    $vbs=""
EndFunc

Best, Randall

in the spreadsheet, first right click on the checkbox and go to properties. set the triplestate property to true. that will give you a 3rd state, the greyed out check. then take the sheet off of design mode (the first icon in the control toolbox usually) then you can click on the box to check, uncheck, or grey the box. with the code i included above, it will report the value at each click...

[randallc]

OK -got it;

so my function works here; can you confirm too?

Thanks, Randall

Edited June 7, 2006 by randallc

[blitzkrg]

OK -got it;

so my function works here; can you confirm too?

Thanks, Randall

I figured out my problems and think it's working..

I'll give it a full test tomorrow

Thanks!

Edited June 7, 2006 by blitzkrg

[tdpaxton]

Hey, I'm not sure if this would be the right location to post this, but I am looking for some documentation on the Microsoft.Excel object used in the Excel UDF. I could have swore I found it before. Any help would be great, thanks!

[seandisanti]

Hey, I'm not sure if this would be the right location to post this, but I am looking for some documentation on the Microsoft.Excel object used in the Excel UDF. I could have swore I found it before. Any help would be great, thanks!

i can't explain the current excelcom.udf, BUT for just an object reference, go into vba editor in excel (alt + F11) and press F2 that will bring up the object browser, and you can use it to view all methods and properties of the objects in excel, office, and vba libraries.

[blitzkrg]

Just wanted to follow up.

RandallC your checkbox code is working well..

Cameronsdad thanks for getting it started!

[blitzkrg]

Thought of a feature that is missing.. font size.

we have a set font type feature, but not the size.

I made the following additions to the UDF (based off the existing code of course)

_XLSetCellFontSize(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, $i_FontSize, $s_i_Visible)

Case StringInStr($s_MEExcelCom, "SetCellFontSize")
      If $s_i_ExcelValue <> "" Then
     ;.Application.Cells ($i_Row, $s_i_Column).Font.Size = $s_i_ExcelValue
      .activesheet.range ($Range).Font.Size = $s_i_ExcelValue
      EndIf

Func _XLSetCellFontSize(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, $i_FontSize, $s_i_Visible)
$var = _ExcelCOM($s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, "SetCellFontSize", "NoSave", $i_FontSize, $s_i_Visible, "NOTExit", "NotLastRow", "NOTToColumn")
EndFunc

[seandisanti]

Thought of a feature that is missing.. font size.

we have a set font type feature, but not the size.

I made the following additions to the UDF (based off the existing code of course)

_XLSetCellFontSize(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, $i_FontSize, $s_i_Visible)

Case StringInStr($s_MEExcelCom, "SetCellFontSize")
      If $s_i_ExcelValue <> "" Then
    ;.Application.Cells ($i_Row, $s_i_Column).Font.Size = $s_i_ExcelValue
      .activesheet.range ($Range).Font.Size = $s_i_ExcelValue
      EndIf

Func _XLSetCellFontSize(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, $i_FontSize, $s_i_Visible)
$var = _ExcelCOM($s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, "SetCellFontSize", "NoSave", $i_FontSize, $s_i_Visible, "NOTExit", "NotLastRow", "NOTToColumn")
EndFunc

my head hurts just reading that... maybe i just have undiagnosed ADD or something, but it seems like this can be done ALOT more simply... example: this will change the font in cell a1 of the active workbook to be size 72...

$ex = ObjGet("","excel.application")
$wb = $ex.activeworkbook
$ws = $wb.activesheet
$ws.range("a1").font.size = 72

[blitzkrg]

my head hurts just reading that... maybe i just have undiagnosed ADD or something, but it seems like this can be done ALOT more simply... example: this will change the font in cell a1 of the active workbook to be size 72...

I was just adding to the existing code, not trying to re-engineer it..

I'll leave that to someone who's more capable than i

[seandisanti]

I was just adding to the existing code, not trying to re-engineer it..

I'll leave that to someone who's more capable than i

i'm sorry, i really wasn't trying to be critical at all, and meant no offense, to you or any of the others that have been keeping this project going. i was just trying to suggest what i thought might be a little simpler to write, and alot easier to read. the one i put up is going to be in my excel udf if i ever get some time at work to finish it. they're talking about promoting me again this week, and then i'm giong to have to automate a whole new job before i can get active with it again

[randallc]

Hi,

_XLSetCellFontSize(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, $i_FontSize, $s_i_Visible)

your checkbox code is working well..

I'll try to get around to including these, thanks.

Func _XLFontSetSize($range,$size = 10)

one i put up is going to be in my excel udf

@Cameronsdad

I presume, though, that you will allow naming the file, sheet, etc when you call them in your new ones too?, with similar visibility option etc?

Best Randall

[seandisanti]

Hi,

I'll try to get around to including these, thanks.

@Cameronsdad

I presume, though, that you will allow naming the file, sheet, etc when you call them in your new ones too?, with similar visibility option etc?

Best Randall

all of that will be possible, just differently implemented. i'm actually trying to do mine kind of like ie.au3...

each function takes the objects to interact with, and the specifics...