U: UserBlock Link to MS Excel.
MJMcCann-Consulting

10 lines each way to Excel Spreadseet for VBA programmes to be embedded in McSimAPN
MJMcCann-Consulting Logo

Purpose
The purpose of the user block is to provide a means of incorporating extra logical functionality, which the user can define, into a simulation. For example:
Computer control systems (multivariable, Programmable logic controllers --PLCs, SCADA, imitate human decisions, rule based), data loggers, controllers for the running of a model.
The Microsoft Excel software has to be available and the programme to do the actions has to be written by the user in VBA (Visual Basic for Applications) in an Excel spreadsheet. An example prototype demonstration file is available with the Demos.

UserBlock.
The block itself (block 20) as shown on the diagram provides the link to an Excel Spreadsheet, which has to be available and activated to function.
The block operates periodically at which point it sends a set of (up to) 10 numbers plus some control values to the Excel spreadsheet. The arrival of the numbers in the spreadsheet triggers a calculation subroutine (which you will have to write). When the calculation is done 10 numbers are returned to McSimAPN together with updated (if so programmed) versions of the control parameters.
The numbers sent into Excel are the output values from the (up to) 10 input links that can be specified on the form (Rt-click on the USER block).
The numbers returned to the simulation are made available via 10 psuedo-constants, Blocks 21-30, labelled initially as U1 to U10. These appear on the diagram on the lower left hand side. You can drag and drop them anywhere else on the diagram

A sample '.XLS' sheet is provided with the framework for the definition and operation of the VBA code.

Operation
Assume there is a suitable Excel Spreadsheet (such as UserBlock_APN_01.XLS) available. The User block is, by default, OFF so that it doesn't try to active a spreadsheet that isn't there.
Make up a set of connections for the User block. Connnections can be defined in the form that comes up with a Rt-click on the User Block or on the [Excel Link] button at the bottom of the display..
This has been done in the McSimAPN model called: "UserBlock_APN_01.txt"

The link is made by opening the Excel Spreadsheet thus:
..L-Click on the [Excel Link] button. A small window appears.
..L-click on its [Load MS Excel File] button and select a file. If you start with UserBlock_APN_01.XLS you can adapt it later.
You may have to wait while MS Excel loads itself and then accept the [Enable Macros] option twice to get it to start.

..R-click in the panel in the middle of the Window and select showing the spreadsheet so you can work on it.
..Close the little window.
McSimAPN is ready to work with the Spreadsheet. So when you have checked the control variables (as below) you can switch the user block on (L-click on it).

Control Variables
Now check the values for the control variables in the User Block. The A and B values are a pair of links that put values in to the spreadsheet. If you only need two links you can use these and ignore the other ten.
The D values define the first time that the spreadsheet will be called.
The C values give a control signal to the spreadsheet. The initial value is always a zero unless you manually change it. It can trigger whatever reset or first-pass operation you define.
After doing that, the spreadsheet returns a different value which gets put into the User Block's 'cc'.

Next Event Time (activation)
The spreadsheet can make changes to the period and the next event time (by changing Vn and pp). However, McSimAPN will advance the next event time in the normal way before calling the spreadsheet so changing them is optional.
The spreadsheet always has access to the current value of Time, but can't affect it in McSimAPN.
The spreadsheet can do any programmable calculations, putting the results in the second row of the spreadsheet and they will get transfered to the blocks 21 to 30 whence they can be connected into the rest of the model.

Programming in VBA
This is no place to teach VBA programming, but the example file has control buttons that allow some development work to be done without using McSimAPN. It also has some demonstration of using the Spreadsheet as a data logger, with means for clearing the records.
The example McSimAPN file is a vehicle for developing the programming. Then, when it's working, it can be brought in to a dynamic model to serve as a computer control system or whatever you want.

Save a Copy of the Spreadsheet
Remember to save a copy of your working programme with an appropriate name. You will need to recover it later.
When you activate a spreadsheet from McSimAPN, make sure it isn't already running in Excel (save it and shut it down first).

MJMcCann-Consulting

Help Index:
Index/Search

Background
Simulation Concepts
Continuous Systems
Discrete Systems
McSimAPN Structure
McSimAPN Operation

Using McSimAPN
Start McSimAPN
Save Model,data
Create Blocks
Run-Hold-Reset
Link Excel+VBA

PetriNet Block Types
A activity/action
B belt conveyor
C container/constant
D diverter(random)

Analogue Block Types
E exponents
F flux/flow
G function Generator
H hysteresis
I integrator
J inductor
K logic element
L logarithms
M memory
N note/label
O oscilloscope/graph
p not assigned
Q quantizer/rounding
R relay on/off
S sin/asin/atan
T timer/clock
U user link Excel
V visual voltmeter
W sWitch selector/MUX
X multiply
y not assigned
Z random (fuZZ)
& signed summation
% division/difference
@ access/move values

Invitation. McCann can help if you have a design or operational problem that needs some technical support that is outside your team's experience, some quantitative assessment of what is really the cause of the difficulties, some design alternatives or just a fresh look by an intelligent interrogator.
If you have a problem with the behaviour of a market sector, plant, process or item of equipment and would like to get a quantitative handle on it to improve yield or optimise performance, then contact us. We are always ready to give a little time to discuss a new puzzle, in confidence, of course. We'll only worry about fees when we have some defined work. We can be flexible about how we work with you.
Top
MJMcCann-Consulting,
POB 902,
Chadds Ford PA
19317 USA.
T: 1 302 654-2953
F: 1 302 429 9458
E: mjmccann@iee.org
Request. Please let us know how you found this software and your interests by sending an email to mjmccann@iee.org Thank you Date: 2012.02.26
File: u.htm