2c:null 2d:["$","title",null,{"children":"Dynamic Lexicon with Excel and VBA | Christophe Yamahata | Observable"}] 32:T422,### Solution You can download the resources directly from the ᥫ **Files Pane**: * Lexicon_famous_scientists.xlsm (macro-enabled Excel file) * demo_Excel_dictionary.zip (Zip archive containing the Excel file and sample MP3 audio files) Once downloaded, you must unblock the XLSM file before using it. To do this, right-click on the file, select Properties, and check the box labelled Unblock at the bottom. Additionally, make sure to enable macros when prompted, as they are required for the tool’s functionality. After unzipping the *.zip* file, be sure to keep the "Audio" folder together with the XLSM file. This is important because, when using the Azure AI Speech option, the tool will look for the MP3 audio files inside that folder, rather than trying to access Microsoft Azure. 33:T4ef,
### How it Works With a touch of **VBA** (Visual Basic for Applications) the spreadsheet is transformed into a dynamic Kanban-style tool, enhancing learning efficiency. #### Main functionalities 1. **Glossary**: The data is organized in a structured spreadsheet that you can be adapted straightforwadly. 2. **Access Terms Easily**: Use the "Random Term" sheet to explore terms either **randomly** or via a **search bar**. #### Text-to-Speech Integration On Window, the built-in, Microsoft David Desktop (SAPI) voice feels outdated and is boring to listen considering today’s AI advancements. So, I have included Microsoft Azure’s "Ryan" AI voice (en-GB-RyanNeural), which offers much more natural speech synthesis. While both options are available for experimentation, note that Microsoft Azure requires account creation to access its AI-powered speech functionality. To try this option, you will need to enter your own Azure API Key and Azure Region in the two dedicated cells. 34:T5ca,

Useful links

Video tutorial on YouTube (4 min.): Audio-enable lexicon in Excel

→ In this 4-minute video, I go through the initial steps of building the tool using Excel and VBA macros.

YouTube Short (38''): Create a Dynamic Audible Lexicon in Excel with VBA

→ A quick overview of the dynamic audible lexicon in Excel.

35:[{"id":"01e5644e5a720568d86838c0e0ae42973e73c3518b10871665d695abfb754ec5e04f8cb062f701109667ed8ee3d486d3d9059898f4e51dd7ddcbac24668b2f95","href":"https://static.observableusercontent.com/files/01e5644e5a720568d86838c0e0ae42973e73c3518b10871665d695abfb754ec5e04f8cb062f701109667ed8ee3d486d3d9059898f4e51dd7ddcbac24668b2f95","name":"YouTube_icon.svg","type":"image/svg+xml","lastModified":1734778153078.358,"size":974},{"id":"7151ba80091e6133ec996d89c2311d7e934168e326146f20e9ec6f0e80b6be960e2738e843c9dd0205d33149fa3cdd0e1c745d6a8b373de6d0f9b0d300d4cc0e","href":"https://static.observableusercontent.com/files/7151ba80091e6133ec996d89c2311d7e934168e326146f20e9ec6f0e80b6be960e2738e843c9dd0205d33149fa3cdd0e1c745d6a8b373de6d0f9b0d300d4cc0e","name":"YouTube_shorts_icon.svg","type":"image/svg+xml","lastModified":1734778250448.872,"size":583},{"id":"e1d057957527d426930f0003e7400bff4bbbe39f1cb33abbd1a0421d211ea30ea1e2ad38ccfd21efe49e8813177fe3cd10918c48f590f81e47218687e096f228","href":"https://static.observableusercontent.com/files/e1d057957527d426930f0003e7400bff4bbbe39f1cb33abbd1a0421d211ea30ea1e2ad38ccfd21efe49e8813177fe3cd10918c48f590f81e47218687e096f228","name":"Excel_2021_Windows_10.png","type":"image/png","lastModified":1734214733746.934,"size":24212},{"id":"f81703623a5620d39ce38df722685f96b7c11ff2d6d55235b7c2163a43b6ca3bd7ae86b3788ffde77a5cd944b84ade70089ace5f985bfc0a3f3e8413289f95cb","href":"https://static.observableusercontent.com/files/f81703623a5620d39ce38df722685f96b7c11ff2d6d55235b7c2163a43b6ca3bd7ae86b3788ffde77a5cd944b84ade70089ace5f985bfc0a3f3e8413289f95cb","name":"Lexicon_famous_scientists.xlsm","type":"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet","lastModified":1733520439421.214,"size":121602},{"id":"c4bfe9114fc83c205e776936f04e291ea0ce58fb4afe729874b67650a805bcd895c98e33459c1e7c668e3cc1127d6bc8a12d51755f26a9d4e92db37b31278ea5","href":"https://static.observableusercontent.com/files/c4bfe9114fc83c205e776936f04e291ea0ce58fb4afe729874b67650a805bcd895c98e33459c1e7c668e3cc1127d6bc8a12d51755f26a9d4e92db37b31278ea5","name":"Lexicon_famous_scientists.PNG","type":"image/png","lastModified":1733521437285.066,"size":138043},{"id":"fa2878b50c85b6558581c7eebb7e447940dc994726617230fcce7c31321e66751cf7d87e12f0eba3d1c5423f4e802ac7e28746fe7d5d5ef5b05e49a03b46d956","href":"https://static.observableusercontent.com/files/fa2878b50c85b6558581c7eebb7e447940dc994726617230fcce7c31321e66751cf7d87e12f0eba3d1c5423f4e802ac7e28746fe7d5d5ef5b05e49a03b46d956","name":"demo_Excel_dictionary.zip","type":"application/zip","lastModified":1733526367247.838,"size":1603173},{"id":"68c17306b42ece1b6e92b671047321f6a0918fe0c809460cb357db3bf3820e3f8453372c705a9840976f3f14402cd54d1b43a612c9297191f514bdcffc794f7d","href":"https://static.observableusercontent.com/files/68c17306b42ece1b6e92b671047321f6a0918fe0c809460cb357db3bf3820e3f8453372c705a9840976f3f14402cd54d1b43a612c9297191f514bdcffc794f7d","name":"demo_audible_lexicon_Excel.mp4","type":"video/mp4","lastModified":1733526958294.518,"size":4207768}] 30:{"id":"27fd6b34c1e12346","slug":"dynamic-lexicon-with-excel-and-vba","title":"Dynamic Lexicon with Excel and VBA","thumbnail":"a0b15744a2bca899e313a76b7e639dfb55aa6c9b0e24ea1333aa3ebe26689313","access_level":"public","create_time":"$D2024-12-06T21:14:17.730Z","update_time":"$D2024-12-21T11:07:23.587Z","view_time":null,"first_public_time":"$D2024-12-06T22:35:48.925Z","fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"dd67891d326bcfb1","login":"christophe-yamahata","name":"Christophe Yamahata","avatar_url":"https://avatars-next.observableusercontent.com/8256ac716aa8395babba412b2835913d186874fa4190df57a4a28f2e60129876","tier":"starter_2024"},"version":377,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"06f083eba60f5a76","login":"christophe-yamahata","name":"Christophe Yamahata","avatar_url":"https://avatars-next.observableusercontent.com/8256ac716aa8395babba412b2835913d186874fa4190df57a4a28f2e60129876","tier":null}],"members":[{"type":"individual","id":"06f083eba60f5a76","login":"christophe-yamahata","name":"Christophe Yamahata","avatar_url":"https://avatars-next.observableusercontent.com/8256ac716aa8395babba412b2835913d186874fa4190df57a4a28f2e60129876","tier":null,"role":"editor"},{"type":"team","id":"dd67891d326bcfb1","login":"christophe-yamahata","name":"Christophe Yamahata","avatar_url":"https://avatars-next.observableusercontent.com/8256ac716aa8395babba412b2835913d186874fa4190df57a4a28f2e60129876","tier":"starter_2024","role":"editor"}],"comments":[],"custom_thumbnail":true,"trashed":false,"subscribed":null,"forks":1,"collection":{"id":"89503c6463a74372","slug":"tools","title":"Tools","owner":{"id":"dd67891d326bcfb1","login":"christophe-yamahata","name":"Christophe Yamahata"}},"type":"notebook","body":{"title":"Dynamic Lexicon with Excel and VBA","cells":[{"id":0,"value":"# Dynamic Lexicon with Excel and VBA\n\n*This notebook introduces an XLSM file (macro-enabled Excel) with advanced features designed as an **audio-enabled lexicon**. This versatile tool can also be adapted for other purposes.*\n\n

","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":112,"value":"### Background\nI created this file during my journey to master standard Project Management terminology, which involves learning over 700 terms. To tackle this, I quickly realized that Excel could be an invaluable tool for enhancing the learning experience.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":127,"value":"video = html`\n

This video gives an overview of how it all works in practice within the downloadable XLSM file.\n

\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":46,"value":"$32","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":251,"value":"### Remark\nThe PMI lexicon is proprietary and cannot be shared. Instead, I have prepared a sample file featuring a short list of famous scientists, such as Johannes Kepler, to showcase the tool’s capabilities.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":48,"value":"lexicon_famous_scientists = {\n const container = html`\n

Preview of the XLSM Excel file.\n

👆🏻 Click to resize the illustration\n

`;\n\n container.onclick = () => {\n const currentWidth = container.style.width;\n container.style.width = currentWidth === \"600px\" ? \"1100px\" : \"600px\";\n };\n\n return container; \n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":5,"value":"$33","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":258,"value":"
\n### Software and Environment\nPrepared with Excel 2021 (English) on Microsoft Windows 10 Pro, and successfully tested on Windows 11.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":288,"value":"excel_2021_windows_10 = FileAttachment(\"Excel_2021_Windows_10.png\").image()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":267,"value":"$34","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined"},{"id":35,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":228,"value":"viewof display_inspect = Inputs.toggle({label: \"⇣ Check to display the appendix\", values: [\"true\", \"none\"], value: \"none\" })","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":225,"value":"\n
\n${display_inspect == \"none\"? html`

⇣ Appendix is hidden ...

` : html`

Appendix

`}","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined"},{"id":23,"value":"excel_file = await (FileAttachment(\"Lexicon_famous_scientists.xlsm\").url())","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":27,"value":"zip_excel_and_audio = await (FileAttachment(\"demo_Excel_dictionary.zip\").url())","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":124,"value":"demo_video = await (FileAttachment(\"demo_audible_lexicon_Excel.mp4\")).url()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"}],"files":"$W35","stdlib":"1","titling":"auto"}} 31:"$30:body"