Скачать с ютуб Excel VBA Arrays For Beginners в хорошем качестве

Excel VBA Arrays For Beginners

Here’s one I avoided for a long time. Why? I found it hard to conceptualise what a VBA ‘array’ is. I came to understand it like this: it’s a range of cells that lives in VBA’s memory. Yes, we can’t ‘see’ an array – we can only extract parts of it; but, in Excel’s mind, it’s a table of data – cells containing values or, in our case, text strings. A table in Excel’s memory! 💻FREE: YOUR EXCEL CHEATSHEET MINI-COURSE https://tinyurl.com/jxezd355 00:00 Introduction 01:20 Key Concepts & Techniques 03:00 VBA Coding Start 03:40 Excel VBA Split Function 06:05 Lbound And Ubound Functions 07:35 Using Array Index Numbers 09:40 Integrate For-Next Loop 12:20 Integrate Offset Method If you can get your head around this, the implications are intriguing. We work with tables or ranges of data in Excel all the time – and it can be onerous: formulae such as VLOOKUP can take time, and even macros can feel slow when they write to or read from the spreadsheet. So, what if there wasn’t a spreadsheet at all? What if the data ‘lived’ in VBA instead? It’s mind-bending … but could it work? 💼DOWNLOAD FILES https://tinyurl.com/ne4jyfxx Get ready for some slick VBA macros, some difficult coding to get it all working, and at least one ‘punch-the-air’ moment. Welcome to Excel VBA Arrays For Beginners! When might a VBA array be useful for in the real world? My customer wanted me to ‘fragment’ a single cell of text entries, separated by semi-colons, to separate cells. So “ABC;DEF;GHI” would become “ABC”, “DEF”, “GHI”, with those values entered into different cells in the same column. It’s possible with formulae, of course, and a formula-based solution is included in the download file. But, what if you didn’t have the spreadsheet real estate to implement the formulae? Perhaps you just want a faster, leaner solution? Let’s look at how to do it with arrays in Excel VBA. VBA’s Split function gets the party started. It does a lot of work: the function separates the text string (eg. the original text entry) into chunks using a ‘delimiter’. A particular character is needed to separate or ‘delimit’ the text – in our case a semi-colon. Yes, some kind of delimiter is essential for this application of arrays in Excel VBA. So, in the background, Excel has created an ‘array’ comprising our 3 smaller text strings. Visualising this, however, is difficult – which is why it took me so long to work out arrays. As I demonstrate in the video, the Msgbox technique for externalizing variable values simply doesn’t work with arrays. Arrays require a special variable type – or at least a modification of a string variable – in the format VARIABLENAME(). Those brackets seem to make everything work – but also make the code rather opaque / inaccessible. What’s actually going on? 💻FREE: YOUR EXCEL CHEATSHEET MINI-COURSE https://tinyurl.com/jxezd355 Things get easier when you understand the values in the array (ie “ABD”, “DEF”, “GHI”) are allocated an index number. Which must mean 1,2,3, right? Well, not quite. Because arrays always start at 0. Why? I don’t know and if you do – please enlighten me in the YouTube comments! So, the values in the array are allocated index numbers of 0,1 and 2. The Lbound (lower bound) and Ubound (upper bound) functions in VBA demonstrate this and do work with Msgbox, as I show in the video. We can use the index number to externalize the values in the array and finally pull back the curtain on what is going on. I use the construct Msgbox Chris_Array(0) in the video to extract the values. Progress! But, the task is not yet complete. How do we now input the array’s values into separate cells in the spreadsheet? You can use a For-Next loop in combination with an array to get this done. A loop can ‘count through’ the array’s index (0,1,2) and write the corresponding values to a cell using eg. Range(“A1”) = Chris_Array(Array_Counter), where Chris_Array is the variable storing the array, and a Array_Counter is the integer variable controlling the loop. It’s probably easier to understand watching the video demonstration – I promise it will be worth your while! A final VBA technique brings everything together. I’ve said many times on the channel that the Offset method can work with a For-Next loop like fish and chips: magic happens when these two are paired together. As the counting variable increments up, the macro works down the spreadsheet to list the values of the array in adjacent rows. Job done! See how I set it up in the video and remember the download files from the start AND end are available and should help. Did you get it all working and did you have a ‘punch-the-air’ moment? Can you see application for VBA arrays in your work? I would love to hear your thoughts in the YouTube comments – I will get back to you there as usual. 💻FREE: YOUR EXCEL CHEATSHEET MINI-COURSE https://tinyurl.com/jxezd355