Macro For Reflections
Download
Report
Transcript Macro For Reflections
Macros For Reflections
Christopher Guertin
VAMC – West Palm Beach, FL
[email protected]
1
Objectives
Define what a Macro is
Tell the differences between VB and VBA
Record a Macro
Edit a Recorded Macro
Create a Macro
Explain when and why we would use a Macro
in Reflections
2
What is a Macro
Macro (noun) Webster Dictionary
a single computer instruction that stands for a
sequence of operations
Sometimes referred to as: Macroinstruction
3
VB vs VBA
VB –
Visual Basic
Standalone
Can be used complied into
an executable and run
outside the host
Uses same syntax as VBA
VBAVisual Basic For Applications
4
Requires an Application
Reflections
Excel
Word
Access
Is complied and run within
inside the host only
Uses same Syntax as VB
Why use a Macro
Saves Times
Consistent Data
Easy to make both minor and major changes
Exportable / Importable
Multiple + Easier to use interfaces
Allows for User Input
Software Already Available
5
What a Macro Can Do
Automating almost anything
Mouse, Keyboard, Options on Menu
Creating Dialog Boxes or User Forms
Passing Data Between Applications
Error Checking and Handling
6
Benefits of Macros for Reflections
Examples:
7
Change Screen Fonts, Color, Size
Assign Functions to Keys
Allow User Input for Fileman Reports
Allow Fileman Reports to be Dynamic
Clean up “Dirty Data”
Complete Repetitive Tasks
Works well with Fileman
Transfer Data to/from MS Office Products
No Programmer Access Required
Security
DO NOT put your username or password
into a macro to use as an AutoLogin
Code Entered is generally not considered
Encrypted
8
Sample Reflections Screen
9
Naming a Macro
Must start with letter
May Contain Numbers, Letters
May use an Underscore
80 Character Max
No Special Characters or Spaces
10
Example: INPT_WORKLOAD_2011
How to Record
Macro Start Recording
Stop, Pause, Annotate
Can Edit, Create button, Map to key
11
How to Edit Macro
12
Macro Macros Edit
Creating Macro
Macro Macros
Type in name under Macro Name:
13
Assigning Macro to Key
14
Setup Keyboard Map
Assigning Macro to Mouse Button
15
Setup Mouse Map
Assigning to Button on Toolbar
Right Click Tool Bar
Setup Toolbars Customize New
Button
After Recording – Check – Create Button
16
Customizing Buttons
17
Stopping a Macro
Macro Stop Macro
Ctrl+Break
Create a Button
18
Saving Macro
19
File Save As
Saving Tool Bars
20
Setup Toolbar… Settings
VBA – Adding to MS Office Products
21
Customize Quick Access Toolbar
22
Customize Quick Access Toolbar More
Commands Customize
Adding / Removing Object Libraries
23
Tools References
User Input
Wait for Input into Reflections
Message Box to Help
Input Box
User Form
24
Message Box
25
Input Box
26
User Form
27
User Form
Could be a course all by itself
Many Options
Label
Text Box
Combo Box
List Box
28
User Form - WYSIWYG
29
User Form – How the User Sees It
30
Date Converter
31
Converts Date to Format Useable by VISTA
Format Date / Time
Format()
Format(expression[, format[, firstdayofweek[,
firstweekofyear]]])
strDate = Format(strDate, "dddd, mmm d yyyy")
Wednesday, Aug 3 2011
MyStr = Format(strTime, "hh:mm:ss AMPM")
32
05:14:03 AM
Expressions
Operators
Arithmetic
Comparison
&, +
Logical
33
>, < , =
Concatenation
+, -, *, /
And, Not, Or
Variables
Variables
Naming
Must start with letter
Can be up to 255 characters
Cannot contain a Space but can use underscore
Cannot contain Operators or Special Characters
34
Variable Life
Should be declared
Procedure
Private
All Procedures in the Module
Private strDRUG as String
Public
35
Only for that Procedure
Automatically declared
Dim strDRUG as String
All Procedures in All Modules of Project
Public strDRUG as String
VB Codes
36
Ascii Codes
37
Message Box
38
Statements
If - Then
If – Then – Else
If – Then – ElseIf – Else
GoTo
Select Case
39
Loops
For – Next
For Each – Next
Do While – Loop
Do – Loop While
While – Wend
Do Until – Loop
Do – Loop Until
40
Comparison Operators
41
Logical Operators
42
Functions
Format
Transmit
WaitForString
Ucase
Lcase
Able to create your own
43
Allows for only one set of code(refer to it)
Good Habits
‘ Apostrophe
for comment(will not run)
Comment
Top: User, Date, Explain Function
After Code
Declare Variables at Top
TALLman Lettering
Use Error Handler
Try to make more universal
44
Good Habits
Create in Test Account if possible
Be careful and use checks when user will be
creating orders
Validate Data
45
Switch Column Size
46
Create Buttons to Change Column Size
Screen Capture to Text File
Logging On/Off
47
Changing the Color of the Screen
Differentiate Screens
Allows users to easily change to preferred
color scheme
48
How to Run Macro
Macro Macros
Select from list and click Run
49
Run a Macro
50
Click on Button Created
Code Behind the User Form
51
From a Macro you can Load a User FormAuto-Populate Fields Then Send User Input
back to Original Macro
Macro has Started
52
The Code will start running and user input
will be requested as needed
User Input
53
Many Input boxes can be used or one user form
Default Values may be set
User Input
54
Many Input boxes can be used or one user form
Default Values may be set
The user input is then used
55
The user input is captured and then added to
the Fileman routine
The routine is run
The routine appears to be done but more
actions are preformed in the background
May have a final stop to allow it to be aborted
56
What is happening Behind the Scenes
57
The Results from the Macro
58
From .TXT to Excel
Shawn Toy Created an Amazing Add-In for
Excel that will allow text that wraps to be
flattened out
http://vaww.infoshare.va.gov/sites/vapharmacyi
nformatics/WIKI/FileMan/FileMan_TextCaptur
e_Tools.aspx
59
How to use Add-In
60
Add-In FileMan Tools File Import Interm
Delimited Data
61
Easy as 1, 2, 3
The Final Results
The header will confirm when it was run and
what user input was entered
The data can be copied over to a new sheet
or the top part can be deleted to make the
headers active
62
Help File
63
Can be useful to learn Syntax and Concepts
Help File
Methods
64
Properties
Events
More Help
Pharmacy ADPAC Email Group
Visual Basic Books / References
VBA Books
Google
SharePoint (Future)
65
The Future
SharePoint Site
http://vaww.infoshare.va.gov/sites/vapharmacyinformatics/WIKI/VBA/Home.aspx
Share Design Ideas
Integrate Fileman Routines
Seamless Data Transfer to MS Office Products
VBA / Macro Education Series
Reflections
MS Office
66
Questions
67