create-dynamically.vb
create-dynamically.vb is a simple
VBA script that dynamically creates two Active-X (OLE) buttons using the
user defined function addButton.
Its first parameter specifies the
range that the button should occupy.
The second parameter specifies the caption of the button.
The third parameter is the (one line) VBA code to be executed when the button is clicked.
Additionally, clicking the button randomly changes the button's color.
option explicit
sub main() ' {
rows(2).rowHeight = 22
rows(4).rowHeight = 22
addButton cells(2,2), "one", "action_1"
addButton cells(4,2), "two", "action_2"
end sub ' }
sub addButton( _
rng as range , _
caption as string, _
action as string)
dim btn as oleObject
set btn = rng.parent.oleObjects.add( _
classType := "Forms.CommandButton.1" , _
link := false , _
displayAsIcon := false , _
left := rng.left , _
top := rng.top , _
width := rng.width , _
height := rng.height )
btn.object.caption = caption
dim line as long
with thisWorkbook.vbProject.vbComponents(rng.parent.codeName).codeModule ' {
line = .countOfLines : line = line + 1
.insertLines line, "sub " & btn.name & "_click()" : line = line + 1
.insertLines line, " " & action : line = line + 1
.insertLines line, " sheets(""" & rng.parent.name & """).oleObjects(""" & btn.name & """).object.backColor = rgb(rnd(1)*256, rnd(1)*256, rnd(1)*256)" : line = line+1
.insertLines line, "end sub"
end with ' }
end sub ' }
sub action_1() ' {
msgBox "action 1"
end sub ' }
sub action_2() ' {
msgBox "action 2"
end sub ' }
create-dynamically.wsf
create-dynamically.wsf is a simple script that creates the Excel workbook and inserts
create-dynamically.vb. It requires the
VBScript MS-Office App Creator and must be invoked on the command line like so:
script .\create-dynamically.wsf
<job>
<script language="VBScript" src="..\..\..\..\VBS-MS-Office-App-Creator\create-MS-Office-app.vbs" />
<script language="VBScript">
option explicit
dim app
dim xls
set xls = createOfficeApp("excel", currentDir() & "create-dynamically.xlsm")
if xls is nothing then ' {
wscript.echo("Could not create excel worksheet.")
wscript.quit(-1)
end if ' }
set app = xls.application
call insertModule(app, currentDir() & "create-dynamically.vb", "funcs", 1)
call app.run("main")
xls.save
wscript.echo("The end")
'
' Bring created Excel Workbook to the front:
'
createObject("WScript.Shell").appActivate(app.caption)
</script> </job>
