vba-corner.livejournal.comCollection of VBA Code Snippets and Useful Excel Knowledge — LiveJournal

? ? LiveJournal Find more Communities RSS Reader Shop Help Log in Log in Join free Join English (en) English (en) Русский (ru) Українська (uk) Français (fr) Português (pt) español (es) Deutsch (de) Italiano (it) Беларуская (be) vba_corner —no suspend reasonReadability More vba_corner Archive Photos Video Readability Log in No account? Create an account Remember me Forgot password Log in Log in QR code No account? Create an account By logging in to LiveJournal using a third-party service you accept LiveJournal’s User agreement Recent Entries Archive Friends Profile Collection of VBA Code Snippets and Useful Excel Knowledge January 2016 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Links Microsoft Developer Network Office-Lösung Tags additem android api array array formulas array of arrays arrays beforerightclick binary bom borders button byref byval calendar cells certificate check checkbox close collection colors column_letters combobox conditional formatting control csv cursor_coordinates date datetimepicker dialogue dim document dom events excel excel_coordinates execute macros existence filter find folder formula formular1c1 formulas get getasynckeystate html hyperlinks inputbox internet_explorer isarray jagged join label lastcolumn lastrow lbound leftclick list listbox loop macro menu monthview mousemove ms_office multi-dimensional object office open optionbutton performance radiobutton random randomize range ranges read redim references registry removeitem resize rightclick rnd search security selectionchange shapes shell shortcuts slash split substring text file time timer togglebutton tooltip ubound userform utf-8 vba web webpage wildcards windows windows_scripting word 13 January 2016 @ 03:54 pm Signing your own macros with SelfCert.exe If you want to run your own macros without lowering the default macro security settings, you’ll need to sign them with a digital certificate. Signing your own macros with SelfCert.exe is a great description how to create your own certificate and sign your macros with it. And everything you’ll need for this already comes with Microsoft Office. Tags: certificate , macro , ms_office , office , security , vba Leave a comment 11 January 2013 @ 08:33 pm Create csv Files with Quoted Values and UTF-8 Encoding for Android Apps So you have an Excel sheet with data on your Windows computer, and an app on your Android phone that can import csv files. You want to export your Excel data to a csv file that you can send to the Android device in order to import it in your app. ( Read more... Collapse ) Tags: android , bom , csv , excel , utf-8 , vba Leave a comment 18 October 2010 @ 04:06 pm Excel Menu Key Apparently, I’ve never before tried to enter a slash in an Excel sheet until now. Because the result surprised me - if you type /, it doesn’t enter the character in the cell, but it activates the keyboard access to the Excel menu. Which is really annoying if you actually want to enter / in a cell. Fortunately, this behavior can be turned off and this is how: Excel 2007 1. From the Office Button menu, press the button Excel Options . 2. Choose Advanced , and then scroll down until you see the Lotus compatibility section. 3. In the field Microsoft Office Excel menu key , replace the slash with a character that you do not use frequently or delete it, and then click OK. Excel 2003 1. From the Tools menu, select Options 2. Select the tab Transition 3. In the field Microsoft Office Excel menu key , replace the slash with a character that you do not use frequently or delete it, and then click OK. And to make this a post about VBA , you can switch off this property in VBA as follows: Application.TransitionMenuKey = "" Tags: excel , menu , slash , vba 4 comments | Leave a comment 24 August 2009 @ 12:00 pm Reading in a Complete Text File Sometimes you just want to read in a complete text file from your PC without muddling through all the records (i.e. text lines) one by one. When you open a text file in binary mode, such things like EOL won’t get special attention and you can read in the text as one big chunk. ( Code for function that reads in a text file Collapse ) Tags: binary , close , get , open , read , text file , vba 1 comment | Leave a comment 20 August 2009 @ 01:22 pm Array Formulas / Shortcuts in Excel Long time no post... And this post has nothing to do with VBA either, but I’d like to point out two incredibly useful Excel tutorials by slowfox 1. Have you ever heard of Array Formulas in Excel? No? Or you did, but never really understood how they work? Then have a look at this tutorial . Although the described tasks would also be solvable with ordinary formulas, they’re a great example to understand the concept of array formulas. And as soon as your task is something else than counting or adding up or gets more complex, array formulas are a powerful tool that might save you from coding macros. 2. Working with a program is always quicker and more efficient if you don’t have to use keyboard and mouse in parallel. You can hardly work with Excel using only the mouse, but you can try to do everything with your keyboard to get faster. Here’s a nice overview of Excel shortcuts that will free you from your mouse maybe not entirely, but a big lot. Tags: array formulas , excel , formulas , shortcuts 2 comments | Leave a comment 16 February 2008 @ 10:49 am Working with Internet Explorer Using VBA Ok, Excel probably isn’t the first thing that comes to mind when needing to deal with web pages. But sometimes it’s necessary to access them from your Excel Application (or Word or any other MS Office Application). And it’s easier than you probably think. ( Read More... Collapse ) Tags: document , dom , excel , html , internet_explorer , ms_office , web , webpage , word 63 comments | Leave a comment 13 February 2008 @ 07:13 pm Checking if an Object Exists in a Collection Excel uses collections of objects a lot, and sooner or later you’ll be in the need of checking if a certain object exists in a collection. For example, to check if a workbook was already opened, check if it’s in the collection of open workbooks ( Application.Workbooks ), to check if a certain worksheet exists, check if it’s in the collection of worksheets (e.g. ActiveWorkbook.Worksheets ) and so on... ( So how to check this? Collapse ) And this approach works for all collections of objects that Excel uses, not only workbooks and worksheets. Tags: check , collection , excel , existence , ms_office , object , vba , word 1 comment | Leave a comment 10 November 2007 @ 06:41 pm Individual Buttons in Worksheets and Documents You want individual buttons? Using the formatting options for CommandButtons will get you only so far. You can change the background color, the font, and you can even put a picture on the button, but the button will always be rectangular. So if you want round buttons (or any other form) on your sheet, use shapes. You can draw them in whichever way you like. You can make them look as complex and colorful as you like by drawing several shapes and grouping them together. ( Assign Code to Run When Pressing the Button Collapse ) ( Create a Tooltip for the Shape Collapse ) ( There Can Be Only One... Or Not? Collapse ) Tags: button , excel , execute macros , hyperlinks , ms_office , shapes , tooltip , vba , word 1 comment | Leave a comment 23 October 2007 @ 06:00 pm Search Cells and Highlight Found Entries Imagine you have a long list in your excel sheet and want to find certain entries. Especially if the list gets bigger, it’d be nice to just tell Excel what you’re looking for, and have Excel automatically find it. It’s faster and more accurately than searching yourself. ( Excel Search - not the ultimate solution Collapse ) The key word is Conditional Formatting . You can define a conditional format for the list that shows only when the cell value matches the search value. This method has the advantage that you don’t have to...