Best solution VBA/C++

Hello

I am seeking some advice on the best solution for a small program I
have to build. I need to design a program which can be launched in
Excel, would acquire some data from Excel, then run some cash flow
calculations (so mostly working on multiple small arrays with a lot of
conditions and simple calculations) and would run some monte carlo
simulations.

So basically I need a program which would be fast enough so that I can
run my calculations 1,000s of times in a reasonable time, that would
have access to a proper random number generator (I guess I have to go
for a commercial product for that, any suggestion?), and which could
easily communicate with Excel.

For the moment I think I can run that in VBA. C++ is quite unflexible
and would probably require a little more time to code my stuff. In top
I would have to manage the interface between VBA and C++. On the other
side, I don't know how much faster the program would run under C++
compared to VBA. Can C++ really save some time on functions like if,
loops, while, and simple array calculations? (there will be no access
to excel between the begining and the end of the program).

If anyone has an opinion or a suggestion, I am happy to take it!
Best regards
Charles

0
spam1746 (26)
8/1/2006 5:05:08 PM
excel 39879 articles. 2 followers. Follow

3 Replies
340 Views

Similar Articles

[PageSpeed] 15

Charles  -

If you can build the cash flow model in Excel, you could perform the Monte 
Carlo simulation using industrial-strength software like Crystal Ball 
(www.crystalball.com) or @RISK (www.palisade.com).

Or, you could use less expensive simulation software like my RiskSim 
(www.treeplan.com) or Sam Savage's XLSIM package (www.analycorp.com).

Or, you can reinvent the wheel and write VBA or C++ code, where compiled C++ 
will undoubtedly be faster than interpreted VBA.

-  Mike
www.mikemiddleton.com

"Charles" <spam@cmichel.net> wrote in message 
news:1154451907.958384.24500@s13g2000cwa.googlegroups.com...
> Hello
> I am seeking some advice on the best solution for a small program I
> have to build. I need to design a program which can be launched in
> Excel, would acquire some data from Excel, then run some cash flow
> calculations (so mostly working on multiple small arrays with a lot of
> conditions and simple calculations) and would run some monte carlo
> simulations.
> So basically I need a program which would be fast enough so that I can
> run my calculations 1,000s of times in a reasonable time, that would
> have access to a proper random number generator (I guess I have to go
> for a commercial product for that, any suggestion?), and which could
> easily communicate with Excel.
> For the moment I think I can run that in VBA. C++ is quite unflexible
> and would probably require a little more time to code my stuff. In top
> I would have to manage the interface between VBA and C++. On the other
> side, I don't know how much faster the program would run under C++
> compared to VBA. Can C++ really save some time on functions like if,
> loops, while, and simple array calculations? (there will be no access
> to excel between the begining and the end of the program).
> If anyone has an opinion or a suggestion, I am happy to take it!
> Best regards
> Charles


0
mike5208 (300)
8/2/2006 5:31:37 AM
Thanks for your answer

For the first solution, I think that Crystal Ball just uses the results
from the calculations of the spreadsheet? My problem is that we are
talking about a significantly complex cash flow model, which is to be
honest already a bit slow to process in Excel. (Not slow enough to be a
problem in Excel, but slow enough to be worried if it had to be ran
100,000 times).That's why I can hardly see a way to do it without VBA
and/or C++.

My question is rather: will I win a significant amount of runtime by
using C++ than by using properly (declaring all the variables, etc)
VBA?

You look like you know these software quite well. I think I would
mostly use their random number generation capabilities. Do you know how
they compare for that purpose?

Charles


Mike Middleton wrote:
> Charles  -
>
> If you can build the cash flow model in Excel, you could perform the Monte
> Carlo simulation using industrial-strength software like Crystal Ball
> (www.crystalball.com) or @RISK (www.palisade.com).
>
> Or, you could use less expensive simulation software like my RiskSim
> (www.treeplan.com) or Sam Savage's XLSIM package (www.analycorp.com).
>
> Or, you can reinvent the wheel and write VBA or C++ code, where compiled C++
> will undoubtedly be faster than interpreted VBA.
> 
> -  Mike
> www.mikemiddleton.com
>

0
spam1746 (26)
8/2/2006 12:35:59 PM
Charles  -

I do not have direct experience comparing VBA and C++. For some anecdotal 
evidence regarding VBA vs. C++, see

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse_thread/thread/676170589b71e84e/9c62051cc639479f?lnk=st&q=&rnum=4&hl=en#9c62051cc639479f

or search Google Groups for "excel ian smith" (without the quotes) to find 
the above message and other relevant information. Ian Smith can provide 
numerous relevant VBA functions for Monte Carlo simulation, e.g.,

http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/4c888c242ca61621/3c4626d90261b42f?lnk=st&q=&rnum=7&hl=en#3c4626d90261b42f

Another important resource regarding Excel calculation speed is Charles 
Williams' web site

http://www.decisionmodels.com/

-  Mike

"Charles" <spam@cmichel.net> wrote in message 
news:1154522159.250269.190920@p79g2000cwp.googlegroups.com...
> Thanks for your answer
>
> For the first solution, I think that Crystal Ball just uses the results
> from the calculations of the spreadsheet? My problem is that we are
> talking about a significantly complex cash flow model, which is to be
> honest already a bit slow to process in Excel. (Not slow enough to be a
> problem in Excel, but slow enough to be worried if it had to be ran
> 100,000 times).That's why I can hardly see a way to do it without VBA
> and/or C++.
>
> My question is rather: will I win a significant amount of runtime by
> using C++ than by using properly (declaring all the variables, etc)
> VBA?
>
> You look like you know these software quite well. I think I would
> mostly use their random number generation capabilities. Do you know how
> they compare for that purpose?
>
> Charles
>
>
> Mike Middleton wrote:
>> Charles  -
>>
>> If you can build the cash flow model in Excel, you could perform the 
>> Monte
>> Carlo simulation using industrial-strength software like Crystal Ball
>> (www.crystalball.com) or @RISK (www.palisade.com).
>>
>> Or, you could use less expensive simulation software like my RiskSim
>> (www.treeplan.com) or Sam Savage's XLSIM package (www.analycorp.com).
>>
>> Or, you can reinvent the wheel and write VBA or C++ code, where compiled 
>> C++
>> will undoubtedly be faster than interpreted VBA.
>>
>> -  Mike
>> www.mikemiddleton.com
>>
> 


0
mike5208 (300)
8/2/2006 4:56:18 PM
Reply:

Similar Artilces:

Oris Artelier Chronograph Mens Watch 676-7547-4051LS, Best Wristwatch World
Oris Artelier Chronograph Mens Watch 676-7547-4051LS, Best Wristwatch World Click Here To Website : http://www.watchebay.net/Oris-Artelier-Chronograph-Mens-Watch-676-7547-4051LS.html Wristwatch World: http://www.watchebay.net/ Oris Artelier Chronograph Mens Watch 676-7547-4051LS Information : Brand : Oris Watches ( http://www.watchebay.net/Oris-Watches.html ) Gender : Mens Code : oris-artelier-chrono-676-7547-4051LS Also Called : Case Material : Stainless Steel Case Thickness : 44.5 mm Dial Color : Silver Bezel : Stainless Steel Move...

Excel 2003 - VBA
Hi Guys: A quick question on the "Workbook Open" event: When I load up Excel and open the workbook, this event triggers. If I then close the workbook, without closing Excel, then open the workbook, it does not occur. Close down the worksheet and Excel, then open it, it triggers. Is this normal operation? Could it have to do with the possibility the I had set Application.EnableEvents = False, before exiting the workbook. Craig Also put in sub auto_open() -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Craig Brandt" <brandtcraig...

Best way to store image, voice, text file?
Hello there, Is it possible to define a sql server field to store image, or voice, or text file? If the size of the file is very big, thus exceeding the max row size 8064 bytes limit, is it better to store it in a directory, and just save the location of the file in the field? Thanks, Ben On Thu, 1 Jul 2010 07:19:54 -0700, Ben <Ben@discussions.microsoft.com> wrote: >Hello there, > >Is it possible to define a sql server field to store image, or voice, or >text file? > >If the size of the file is very big, thus exceeding the max row size 8064...

Lock and Unlock cells using VBA
Hello All Anyone know how to lock cells using VBA, also unlock others currently locked. Page protection will be in use at the time this needs to be done. Also, is there anywhere on the net with a complete list of VBA functions, including a short statement of what each one does, with or without examples. If not, any good books that cover the above area. I'm really (strange as it might seem), enjoying playing with VBA and seeing exactly what it can do. Regards Peter The easy questions... Excel's Help is a very good source. take a look at Peter Nonely's workbook that describes...

A problem occur after changing OFFICE from C: to D:
Considering my C:\ is nearly full, I unstall my office from C:\ and then install my Office to D:\ After this, OFFICE runs well except EXCEL has a warning messege when I open it. The problem is: When I open EXCEL, it jump up an alert messege: [Unable to find C:\Program Files\Microsoft Office\Office10\Library\Analysis\ATPVBACS.XLA] 1. Can anyone tell me how to solve this problem? 2. If I don't solve this problem, what will be the result? Thanks! Martin 2007-07-04 > The problem is: > > When I open EXCEL, it jump up an alert messege: > [Unable to find C:\Program Fil...

Writting Function using VBA
Hi I am trying to write a function to return an address but instead I get #VALUE!. Public Function fnd(a, b) fnd = Range(a).Find(b).Address End Function Please help. Thanks. ..Find won't work in UDF's called from the worksheet until xl2002. Depending on the range (a), you could use application.match() through each column. If the range is small, you could just loop through the values in that range, too. nc wrote: > > Hi > > I am trying to write a function to return an address but > instead I get #VALUE!. > > Public Function fnd(a, b) > >...

Populate List Box
Need some ideas. I have a list box from which our clients will be able to select which form they want to preview or print. However, each client should only have selections available that apply specifically to their company. For example: Company A will require forms 1, 2, 3, 6 and 7 Company B will require forms 1, 6 and 7 Company C will require forms 4, 5 and 6 I have some code right now to handle a simple two form variation: If Forms("Main").Controls("Form1Req") = "Yes" and Forms("Main").Controls ("Form2Req") = "No" Then Me.L...

Best solution to retrieve email on the road using 56k dial up
Got a situation here. My boos who often travels home to the East Coast only has 56k dial up available. We host our own SMTP Exchange server here so normally via DSL he has a few options. OWA, Citrix, RWW. however he complains this takes to long to download and read his emails. He wants to be able to download and go offline. We do not use VPN so downloading to his Outlook is not an option. We all have POP3 mailboxes at our ISP for backup in case our Exchange server becomes unavailable. I'm thinking if there is a way I can turn off his email from coming here to the Exchange server so ...

Use Stored Procedure developed in C# in a SQL Query
I created a Stored Procedure by selecting a SQL Server Project in Visual Studio 2008 using C#. It receives a SqlString as a parameter and return how many times a specific character was found. I deployed the procedure to a SQL Server database and can see it listed under the database. One caveat is that it has a small lock icon on it. I have a select query where I want to use the stored procedure. I am thinking this is doable in a select query but can't seem to get it to work. What I want to do is to have a select statement listing some fields and one of the fields being th...

MFC String-table in C# ?
Hi, maybe a bit OT for this newsgroup, but I know that people here use C#, too. So, does anyone know how the MFC string-table technique map to C#/WinForm? I initially thought about using an integer->string map in C#, but is there some built-in mechanism to manage that? Thanks, Giovanni "Giovanni Dicanio" <giovanniDOTdicanio@REMOVEMEgmail.com> ha scritto nel messaggio news:ugqw1g7LJHA.1736@TK2MSFTNGP03.phx.gbl... > So, does anyone know how the MFC string-table technique map to C#/WinForm? ....after some web search, it seems that C# has a ResourceManager class ...

Best way to use CAsyncSocket.
What is the best way for implementing a CAsyncSocket derived class for being a TCP client? I've been using a class for a few years, but I want a better class. I'm thinking about writing a class that would incorporate a CAsyncSocket derived class. That CAsyncSocket derived class would run in a separate thread? Is using a separate thread the best way? If in a separate thread, should the class send data out of the thread every time it gets data, using ::PostMessage to the mainfrm? Is there a better way? So if I'm doing PostMessage I would first "new" ...

VBA to default printer in Crystal Report
is it possible to set default the printer for crystal report to be the same as the printer setup in GP (Files>Print Setup)? eg: is GP is using Printer1, then when calling a crystal report using VBA, the printer will be set to Printer1. If GP is using Printer2, then crystal report will be defaulted to Printer2. thks in advance. Unfortunately that isn't going to work for a couple reason: 1. The biggest reason is there isn't any way to determine what the printer is in Dynamics. Not even with Dexterity. It just wasn't something that was exposed. So I can't think of ...

Conditional Formatting in VBA in 2007
I have a series of non-contiguous rows that I want to apply conditional formatting to. Right now, my code snip looks like this: With Range(sBegRange, sEndRange) .FormatConditions.Add Type:=xlExpression, _ Formula1:="=" & sCFCell & ">40" .FormatConditions(i).Font.Color = 3 .FormatConditions(i).StopIfTrue = False End With Where sCFCell is a string value for the cell I want evaluated for the formula, and i is an integer that increments for each time I create a new rule (which I'm doing for each row I format). It...

non vba way to print non continuous ranges #2
Thanks Myrna, I didn't know about the hide columns facility. It makes what I want to do a lot easier : -- DavidObei ----------------------------------------------------------------------- DavidObeid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=223 View this thread: http://www.excelforum.com/showthread.php?threadid=27196 ...

Passing a C++ 6.0 MFC File stream into a Visual Basic 6.0 dll func
Hello All, I am stuck trying to find a way to open a file in a C++ MFC application (using a stream), write data into it, then call a Visual Basic dll function and pass to it the file stream (or a file stream pointer or a file handle somehow) so that the VB dll functions can write their own data into the same open file. The problem is that Visual Basic 6.0 has no such thing as a file stream. There might be a way to do this using COM, but I do not know much about COM. Are there any guru's reading this that know a way I can pass a stream for an open file back and forth between MFC C...

Can't auto-add override in C#
In the VS.NET 2003 IDE, I can usually type "override" in the code editor and then select from a list of virtual properties and methods in the base classes. However, this occasionally stops working. If I try to add the override via the Class View, sometimes my class's base classes are not shown (under the "Bases and Interfaces" node), and when they are shown, sometimes I can't get the "override" flyout menu pick. Also, when the IDE is in this state, many of my classes do not appear in the Class View. Does anyone know what's going on here? Any help is...

Money cannot creare a temporary file in 'C:\Documents and Settings\[USER]\Local Settings\Temp'
Hi, I just started to get this kind of error message when I make bakups with my Money 2003 Deluxe: "Money cannot creare a temporary file in 'C:\Documents and Settings\[USER]\Local Settings\Temp'". I checked the folder mentioned and my WinXP user account has the privilges on that folder (as it is supposed to have). How I can solve it? Thank in advance. Di Fresco Marco > I just started to get this kind of error message when I make bakups with my > Money 2003 Deluxe: > "Money cannot creare a temporary file in 'C:\Documents and > Settings\[USER]\Local...

programming IE best source of info
like it says on the label SimonSX wrote: > like it says on the label > > Perhaps an *IE* group? This one is for *OUTLOOK* "like on the label". -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk ...

Error message when retriving a string from my xml file in C#.net a
I created the following code in my C# program but it's giving me error message at run time of : XML.XPATH.XPATHEXCEPTION : Namespace Manager or XSLTContext needed. This query has a prefix, variable or user defined function. Can someone see what I'm doing wrong? Thanks, Alpha private XPathDocument unityMessages = new XPathDocument("UnityMessages.xml"); unityMsgNavigator = unityMessages.CreateNavigator(); string query = @"/trans-unit[@id=""m1""]/target[@xml:lang=""fr""]"; XPathExpression queryM1 = unityMsgNavigator.Co...

in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell?
in vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell? Hi Daniel, See http://www.mvps.org/dmcritchie/excel/event.htm change event excel does not keep track of the old value, nor can you get if from the event code. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Daniel" <softwareengineer98037@yahoo.com> wrote i...

C:\WINDOWS\system32\ieframe.dll\1
Hello, if you have problems with VB6 - C:\WINDOWS\system32\ieframe.dll\1 1. open regedit 2. search for: C:\WINDOWS\system32\ieframe.dll\1 3. change to: C:\WINDOWS\system32\ieframe.dll 4. save regedit finished :) ---------------------------------------------------------------------------= ---------- Hallo, wenn ihr Probleme mit VB6 habt - C:\WINDOWS\system32\ieframe.dll\1 1. =F6ffnet regedit 2. sucht nach: C:\WINDOWS\system32\ieframe.dll\1 3. =E4ndern zu : C:\WINDOWS\system32\ieframe.dll\ 4. speichert regedit fertig :) "netbase4web" <netbase4web@web.de&...

What is the best way to set up an auto response
What is the best way to set up an autoresponse without using the Out o Office reply? Does this need to be set on the client or Microsof Exchange server? Thanks ----------------------------------------------- ~~ Message posted from http://www.OutlookForum.com ~~ View and post usenet messages directly from http://www.OutlookForum.com thanks a lot for responding....du ----------------------------------------------- ~~ Message posted from http://www.OutlookForum.com ~~ View and post usenet messages directly from http://www.OutlookForum.com Hey, this is a peer-to-peer forum...you get what...

How to create an access database programatically in C#
I have created access databases in VB6, but now I am working in C#. I am trying to create a Microsoft Access Database programatically in C#, but can't figure out how to do it. Can anyone give me any pointers? You'll probably have more luck asking in a C# newsgroup. Most of us here work from within Access, not from outside it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Brian Kitt" <BrianKitt@discussions.microsoft.com> wrote in message news:34A1AD65-1C1D-4000-83F1-17FD907027EB@microsoft.com... >I have created access d...

Recovery of VBA code
Hi, Somehow I lost my VBA code from Outlook (2003). Perhaps by adding a digital signature ...? I have no idea what happened. I restored a backup of an earlier Outlook.pst but that didn't help. No modules inside. Can anyone tell me if it is possible to recover VBA code from a backup? What file, how to restore? -- Mvg, Frans www.fhvzelm.com The file you need to restore is VbaProject.otm; see http://www.slipstick.com/config/backup.asp for Outlook file locations. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administ...

Creating charts with VBA code
Is it possible to create a Chart using code? If so, how do I do that. Thanks This should get you started: http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/> Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html _______ "Ayo" <Ayo@discussions.microsoft.com> wrote in message news:6BF13198-DE14-44CA-8E82-A1A98E2F9B35@microsoft.com... > Is it possible to create a Chart using code? If s...