• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

True or False, Is It Possible To Convert An Excel Module To A Stand Alone App?

I

indi visual

Member
I'm thinking I would have to use a compiler? (am I on the right track with thinking Visual Studio can do this)?


My working code module works on an Excel worksheet activated by user input.


My guess is if this is in fact possible, then I would have to make this a UserForm first?


Lastly, would it be a matter of simply pasting my working code module into Visual Studio, or will the syntax required for Visual Studio be so different that it'll throw errors all over the place?


Any help in the right direction is super appreciated.


I just thought that if I had actual healthy syntax working vb script language for an excel sheet, I could some how make it a stand alone app with the same copied and pasted code.


My goal is to learn how to create my first stand alone app.
 
It sounds like you want to be able to deploy your code for other people to use? Rather than a seperate "app", perhaps what you really want is to create an "add-in". Just like the Solver and Analysis ToolPak add-ins, you can create a piece of code (and workbook, technically), that other people can then load into their Excel applications. Jon Peltier did a short tutorial showing how to go from creation to deployment that you might want to look at. Here's link to step 1:

http://peltiertech.com/WordPress/build-an-excel-add-in-1-basic-routine/
 
Vbscript editors such as vbsedit can help you with converting the excel module into an executable file. There are some changes to the code that need to be made and error checking in vbscript is not as robust as in VBA but it's a n option.
 
You could use VB6 to create an ActiveX DLL. The VBA code would translate almost exactly to VB6 code.
 
VBSEdit !


That's it ! Luke, vasKov17 Thanks a mill!


I'm guessing though that for everytime I used "With Application" in my code that it will throw an error (because of course I will be outside of Excel)?
 
You have to declare an object such as:


Set objXLApp = CreateObject("Excel.Application")


then you can use


with objXLApp

'code

end with
 
So In theory (if I understand everyone right), once I click the .exe file on my desktop, it will basically be like running the same working excel module code within Excel, ....only except for the fact that Excel won't be open at all (it will instead just simply reference the Excel internal gears so to speak)?


...-which would also mean Excel would have to be installed on the computer even though it doesn't necessarily have to open to run this .exe I've made?
 
Just to give you an example (albeit a useless one):


Here is a VBA subroutine:

[pre]<br />
Sub Test()<br />
With Application.ActiveSheet<br />
.Cells(1, 1).Value = 10<br />
.Cells(1, 2).Value = 20<br />
.Cells(1, 3).Value = "=sum(a1:b1)"<br />
End With<br />
End Sub<br />



and here is the same thing in vbscript:


<br />
Set objXLApp = CreateObject("Excel.Application")</p>
<p>objxlapp.Visible=True</p>
<p>objxlapp.Workbooks.Add</p>
<p>With objxlapp<br />
.ActiveSheet.cells(1,1).value=10<br />
.ActiveSheet.cells(1,2).value=20<br />
.ActiveSheet.cells(1,3).value="=sum(a1:b1)"<br />
End With</p>
<p>WScript.Quit<br />
[/pre]


To test the vbscript one, just paste the text into a notepad document and save it with "vbs" extension then double click to run it.
 
I simply pasted this in a text file with the ext .vbs "HelloWorld.vbs"

Is there any way to accomplish this "Hello World" script without Excel opening?


Oh yeah.. objxlapp.Visible=False (and it displayed the MsgBox without Excel opening) :)


Set objXLApp = CreateObject("Excel.Application")


objxlapp.Visible=True


objxlapp.Workbooks.Add


With objxlapp

MsgBox "Hello World"

End With


WScript.Quit
 
I am not sure what you are trying to do, but there is an option of having the script simply open an excel workbook and call a macro stored inside it. This way you can distribute an excel file with all the macros inside and just have a script (or an app) run them.


Keep in mind that excel opens when the CreateObject call is made and the Visible property just makes it visible to the user. If you do a script like that, excel remains open but invisible after the script quits, so you have to either make it visible or have a procedure in the script that shuts down excel and releases the memory.
 
Yes that's it, I would ultimately like to just build these things from scratch without the use of Excel from vb script.


But for now simply running the excel macros without ever opening Excel would be fantastic.


I've tried to use the code you provided above like

[pre]<br />
Set objXLApp = CreateObject("Excel.Application")</p>
<p>objxlapp.Visible=False</p>
<p>objxlapp.Workbooks.Add</p>
<p>With objxlapp<br />
Range("A1").Value = "Milkshake"<br />
Range("A2").Value = "Fries"<br />
Range("A3").Value = Range("A1").Value & " & " & Range("A2").Value<br />
MsgBox Range("A3").Value<br />
End With</p>
<p>WScript.Quit<br />
[/pre]


Can this really be this simple?

Of course, after I saved this milkshake & fries script as .vbs and cliked-ran it -it threw an error.


But to answer your question yes (and admittedly I wasn't exactly sure what I was looking for until everyone pointed me in the right direction).


What I'm looking to do (and the reason I started this new topic was to find out how to run all of my code in my macro enabled workbook -without actually opening Excel at all *having Excel invisible running in the background until the script quits-ends like you said).
 
Oh snap!


I added "objxlapp." to each range value and it worked : )


[pre]<br />
Set objXLApp = CreateObject("Excel.Application")<br />
objxlapp.Visible=False<br />
objxlapp.Workbooks.Add<br />
With objxlapp<br />
objxlapp.Range("A1").Value = "Milkshake"<br />
objxlapp.Range("A2").Value = "Fries"<br />
objxlapp.Range("A3").Value = objxlapp.Range("A1").Value & " & " & objxlapp.Range("A2").Value<br />
MsgBox objxlapp.Range("A3").Value<br />
End With<br />
WScript.Quit<br />
[/pre]
 
If you put objXLApp everywhere inside the With statement, it makes using the With statement pointless. Note the added ".activesheet" on the With line.


Try this:


[pre]<br />
Set objXLApp = CreateObject("Excel.Application")<br />
objxlapp.Visible=False<br />
objxlapp.Workbooks.Add<br />
With objxlapp.activesheet<br />
.Range("A1").Value = "Milkshake"<br />
.Range("A2").Value = "Fries"<br />
.Range("A3").Value = .Range("A1").Value & " & " & .Range("A2").Value<br />
MsgBox .Range("A3").Value<br />
End With<br />
WScript.Quit<br />
[/pre]
 
If you are going to use With, might as well use it throughout


[pre]<br />
Set objXLApp = CreateObject("Excel.Application")<br />
With objxlapp</p>
<p> .Visible=False<br />
.Workbooks.Add</p>
<p> With .Activesheet</p>
<p> .Range("A1").Value = "Milkshake"<br />
.Range("A2").Value = "Fries"<br />
.Range("A3").Value = .Range("A1").Value & " & " & .Range("A2").Value<br />
MsgBox .Range("A3").Value<br />
End With<br />
End With</p>
<p>Set objXLApp = Nothing<br />
WScript.Quit<br />
[/pre]
 
You must log in or register to reply here.
Back
Top