Archive

Posts Tagged ‘add-ins’

Have your VBA Add-in autogenerate menus for easier access to your macros

  1. P_20160720_203804
  2. Storing your macros in add-ins (.xlam) has many advantages over personal.xlsm and similar document locations.
  3. One disadvantage however is that an Add-in macro is not accessible through the Excel macros dialogue.
  4. The community recommends assigning shortcuts for easy access. I did that and went a couple of steps further using VBE extensibility to
    1. depending on scope
      1. public procedures on your end users’ computer to whom you distribute your Add-in
      2. and also non-private on the developer  machine
    2. list your modules alphabetically
    3. list your macros alphabetically under your modules
    4. for each, find & assign a free (free within Excel only –  short of assigned windows-wide shortcuts  since in my current work environment, I am unable run tools that allow you to list these shortcuts) shortcut combination,
    5. automatically generate an Add-Ins menu of all that , to serve as a cheat sheet.
  5. Code TBA

How to ease editing work in MS-Word by automating search/replace operations

  1. If you frequently have to edit documents according to a large number of editorial rules and regulations
  2. and if you can partially automate these edit operations  (or at least highlight suspicious passages for human review) with Word’s search/replace,
  3. I can recommend an add-in that can automate even the repeated search/replace operations (like the 57 in the video below)
  4. and even help you manage your search/replace strings and regular expressions in a spreadsheet which it can load from:
  5. Greg Maxey’s VBA Find & Replace Word Add-in. See it in action (click for full size):
  6. vbareplace
  7. Two Three Caveats: :
    1. At this point, I cannot get the add-in to work only in Word 2010. Even if I lower Macro security and allow programmatic access to the VBA project, when trying to launch the add-in from the ribbon, Word 2013 complains: “The macro cannot be found or has been disabled due to your macro security settings”:image.
    2. The automation is only as good as your underlying search/replace operations. (Hint: “Some people, when confronted with a problem, think ‘I know, I’ll use regular expressions.’ Now they have two problems.”)
    3. I think I will refrain from search/replace during “Tracking changes” – as in the video – , and rather use “Compare documents” after the replace operations – too many quirks otherwise…

An outline of the MS-Word Cloze Quiz, MS-PowerPoint Multiple Choice Quiz and Internet Lookup tools for

  1. To facilitate lesson delivery and student interaction in our language lab and computerized classrooms, I am programming two MS-Office templates with interactive lookup and quiz functions and create new/convert traditional language teaching materials in French, German and Spanish with them.
  2. These templates support the learner by strengthening learner autonomy and providing immediate corrective feedback and – in conjunction with the grouping facilities of the centre’s infrastructure – allow for custom-tailored instruction based on the immediately available outcome of formative assessments.
  3. MS-Word-Template
  4. The student can be given additional hints when tabbing into a form entry field.
  5. The student can easily look up words and terms in internet-based reference works and collections.
    1. Double clicking on a word opens a browser window with the corresponding entry in a dictionary of the corresponding foreign language.
    2. Selecting a word or phrase and clicking on a menu item in the lookup menu a browser window with the corresponding entry of various reference works and databases (Figure 3: Look up Internet Reference Works, Figure 4: Look up Internet Illustrations).
  6. The student will receive instant feedback when tabbing out of an entry field.
  7. During quiz-taking, the screen will be formatted (font face, size, spacing, colors) so that the teacher keeps easily informed, whether through a computer lab management system / screen monitoring tool or by a more “pedestrian” approach for student monitoring.
  8. After collection and before correction by the teacher and reviewing by the student, the screen will be re-formatted to facilitate reading outside of the classroom setting.
  9. The template can be used for a wide variety of typical foreign language teaching exercises:
    1. Listening comprehension exercises (Figure 3: Quiz Template with Chanson Lyrics)
    2. Grammar drills
    3. Model imitation drills
    4. Information-gap dialogues (Figure 7: Information Gap Dialogues (Language Lab Example).
  10. Instructors have requested fuzzy matching for fill-in-the-blank exercises, which I am planning to implement by integrating an existing COM add-in that can make available the Levenshtein Distance Metric to MS-Word.
  11. Upgrade of Templates from Sanako Lab300 to better integrate with the new Sanako Study1200 software features.
  12. Teaching Content Creation:
    1. Making subtitles for foreign language movie digitally available;
    2. Programming regular expressions ins VS.Net that match function words (example: demonstrative pronouns in Spanish) to run over these templates in order to batch create fill-in-the-blank exercises;
    3. Importing them into the templates and creating grammar and listening comprehension exercises
  13. Support: Implementing an Error Logging Application
  14. MS-PowerPoint-Template
  15. The teacher can easily lookup words and terms in internet-based reference works and collections
    1. Currently implementing additional lookup options (NLP and Corpus Linguistics (ACORN, BNC, Sketchup), Dictionaries (Visurwords, Wiktionary, Google Define).
    2. A number of templated custom-animated exercises can be used for
      1. presenting students with guiding questions before watching/listening to a target language segment
      2. Revealing correct answers after the segment.
  16. A number of templated interactive exercises can be used for revealing correct answers (word lists) depending on students’ responses.
  17. The templates support typical activities in the digital language lab (interactive presentations with multimedia, listening comprehensions, speaking and dialoguing activities for language learning, view usage examples in my Templates Eurocall Presentation video of September 2007). Teachers can use them as exercise-generating engines: they allow copy/paste of their own exercises into these templates. These templates have the advantage of being able to hook into the rich infrastructure that MS-Office provides for language teaching; they work with all Western Languages (but have been thoroughly tested only with ESL, French, German, Italian and Spanish so far). These templates that are better geared than VLE and other CALL resources for daily use in a computerized classroom environment: easy to author, take and monitor and either multimedia-heavy or focused on human interaction. These templates support the instructor by relieving of routine tasks in favor of well-informed, well-focused non-routine intervention, and extend the centre’s screen-sharing and VOIP infrastructure to provide the instructor with an unprecedented control of the learning of an entire class.
  18. APP delivery format:
    1. MS-PowerPoint: 1 template-file (.pot) and 1 addin (.ppa)
    2. MS-Word: 1 template (.dot – incorporates Lookup application) and 1 COM application (fuzzy matching)
  19. Apart from the templates themselves, there is
    1. Explanation: documentation of the tool
      1. see Figure 4: Template Documentation,
      2. Figure 8: PowerPoint Exercise Templates,
    2. sample materials which illustrate the practical exploitation of a tool the development of a skill or the response to an issue
      1. see Figure 3: Quiz Template with Chanson Lyrics
      2. Figure 9: PowerPoint-Exercises for German History Documentary,
    3. Sample Exercise material: hands-on activities for materials development, skills training or discussion
      1. Figure 9: PowerPoint-Exercises for German History Documentary)
  20. Since January 2008, the templates have been adapted for use in the Aston university Study1200 lab and a series of 3 teacher training workshops has been delivered in May/June of 2008. During that time, the implementation of the distance metrics within the templates has been started also which is currently still in the debugging state (see following slide below.)

clip_image002

Figure 1: Look up Internet Reference Works

clip_image004

Figure 2: Look up Internet Illustrations

clip_image006

Figure 3: Quiz Template with Chanson Lyrics

clip_image008

Figure 4: Template Documentation

clip_image009

Figure 5: Quiz Result Summary

clip_image011

Figure 6: Spanish Movie Subtitling Exercise Creation

clip_image013

Figure 7: Information Gap Dialogues (Language Lab Example)

clip_image015

Figure 8: PowerPoint Exercise Templates

clip_image017