Excel settings and VBA declarations

F o r u m

Ballistics for Excel

Home
About BfX
Install/Update
Downloads
Excel/VBA settings
Entering formulas
Drag functions
Robert Meijer

Be sure your Excel 2003, 2007, 2010 and 2013 is up to date

Whether you run Excel 2003, 2007, or 2010, be sure that you have all their updates and service packs intstalled. As one user reported me "I found I needed to update Excel 2007 version to SP2 in order to run WhatDoIShootToday! I probably should have loaded SP2 ages ago but it might be worth mentioning on the website that the spreadsheets require latest Service Packs to be installed."

Enabling add-ins

It does not take much adjustments in Excel to deploy BfX.xll. These are described in the download and installation procedure. However, the example workbooks do depend on other add-ins, most prominently the Solver add-in (about solver). The description of its functions is found here. Solver is packed in SOLVER.XLS for Excel 2003 and in SOLVER.XLAM for Excel 2007. You find them in your Excel installation, see below. Below the screenshots of the Excel Add-Ins menu. It appeared that the CONVERT add-in required the Analysis ToolPack in Excel 2003. BfX appears as BfXXll2003 and BfXXll2007 - two interfaces contained in BfX.xll.


Excel 2003	Excel 2007

To enable workbooks that contain macro's

Some of the downloadable workbooks contain macro's. Opening such workbooks might fail because of the security settings in Excel. Even the use of an add-in can be blocked by the security settings. The following screenshots show were you can set them to enable the use of BfX and the workbooks.

Excel 2003	Excel 2007	Excel 2010/13
Security settings can be found via Tools>Security... The following pops up.	Hit the developer tab on the menu bar and the following pops up.	Hit "Enable content"
Click OK, close and restart Excel 2003. Without these setting you might not be able to include add-ins and workbooks with vba	Uncheck all!

Declaration of Solver in Visual Basic for Applications (VBA)

Some of the VBA modules use Solver. VBA must contain a reference to SOLVER.XLS (Excel 2003) or SOLVER.XLAM (Excel 2007). They can be found in path's similar to

SOLVER.XLS (e.g. C:\Program Files (x86)\Microsoft Office\OFFICE11\Library\SOLVER\SOLVER.XLA)
SOLVER.XLAM (e.g C:\Program Files (x86)\Microsoft Office\Office12\Library\SOLVER\SOLVER.XLAM)

Excel 2003	Excel 2007
Via the Tools>Macro open the Visual Basic editor. Then in the editor:	Via Developer open the VB editor and then:

Click Browse, goto a directory like (there might be slight differences from computer to computer) C:\Program Files (x86)\Microsoft Office\OFFICE11\Library\SOLVER\ SET THE FILE SELECTOR TO All Files Select SOLVER.XLA	Click Browse, goto a directory like (there might be slight differences from computer to computer) C:\Program Files (x86)\Microsoft Office\Office12\Library\SOLVER\ SET THE FILE SELECTOR TO All Files Select SOLVER.XLAM

Declaration of BfX functions in Visual Basic for Applications (VBA)

Use a sellection of the following VBA declaration in your Excel 2003, 2007, 2010 (32 bit) VBA programs. Take care that you substitute the path to your BfX.xll!!!

'unconment: 'Private Declare PtrSafe Function vb_y Lib "FullPath\BfX64.xll" Alias "BfX_VB_Y" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant, Optional v9 As Variant, Optional v10 As Variant, Optional v11 As Variant) As Variant 'Private Declare PtrSafe Function vb_zx Lib "FullPath\BfX64.xll" Alias "BfX_VB_Zx" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant, Optional v9 As Variant, Optional v10 As Variant, Optional v11 As Variant, Optional v12 As Variant, Optional v13 As Variant, Optional v14 As Variant) As Variant 'Private Declare PtrSafe Function vb_ze Lib "FullPath\BfX64.xll" Alias "BfX_VB_Ze" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant, Optional v9 As Variant, Optional v10 As Variant, Optional v11 As Variant, Optional v12 As Variant) As Variant 'Private Declare PtrSafe Function vb_e Lib "FullPath\BfX64.xll" Alias "BfX_VB_E" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant, Optional v9 As Variant, Optional v10 As Variant, Optional v11 As Variant, Optional v12 As Variant) As Variant 'Private Declare PtrSafe Function vb_d Lib "FullPath\BfX64.xll" Alias "BfX_VB_D" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant) As Variant 'Private Declare PtrSafe Function vb_c Lib "FullPath\BfX64.xll" Alias "BfX_VB_C" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant) As Variant 'Private Declare PtrSafe Function vb_xv Lib "FullPath\BfX64.xll" Alias "BfX_VB_Xv" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant) As Variant 'Private Declare PtrSafe Function vb_xt Lib "FullPath\BfX64.xll" Alias "BfX_VB_Xt" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant) As Variant 'Private Declare PtrSafe Function vb_vx Lib "FullPath\BfX64.xll" Alias "BfX_VB_Vx" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant) As Variant 'Private Declare PtrSafe Function vb_vt Lib "FullPath\BfX64.xll" Alias "BfX_VB_Vt" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant) As Variant 'Private Declare PtrSafe Function vb_tx Lib "FullPath\BfX64.xll" Alias "BfX_VB_Tx" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant) As Variant 'Private Declare PtrSafe Function vb_tv Lib "FullPath\BfX64.xll" Alias "BfX_VB_Tv" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant, Optional v8 As Variant) As Variant 'Private Declare PtrSafe Function vb_ran Lib "FullPath\BfX64.xll" Alias "BfX_VB_Ran" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant) As Variant 'Private Declare PtrSafe Function vb_ranb Lib "FullPath\BfX64.xll" Alias "BfX_VB_Ranb" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant, Optional v5 As Variant, Optional v6 As Variant, Optional v7 As Variant) As Variant 'Private Declare PtrSafe Function vb_cd Lib "FullPath\BfX64.xll" Alias "BfX_VB_Cd" (Optional v1 As Variant, Optional v2 As Variant, Optional v3 As Variant, Optional v4 As Variant) As Variant

Use a sellection of the following VBA declaration in your Excel 2010 64 bit VBA programs. Take care that you substitute the path to your BfX64.xll!!!