Excel VBA Arrays - A Complete Guide
ฝัง
- เผยแพร่เมื่อ 24 ก.ย. 2024
- The event will cover arrays in VBA from the very basic to advanced.
It includes:
* What are arrays and why do you need them?
* A very simple example of using an array?
* Static arrays versus dynamic
* How to best use them in Excel
This webinar will feature live VBA coding by me as I show how anyone can use the power of arrays in their VBA code.
Useful Links
==========
Excel VBA Handbook(www.TheExcelVBA...)
ExcelMacroMastery Website(excelmacromast...)
VBA Arrays(excelmacromast...)
VBA Collectoins(excelmacromast...)
VBA Tutorial(excelmacromast...)
Your channel is "Excel"ent!!
Thank-you Paul. I'm glad you recorded this. I was busy and could not join the webcast. Great job.
Hello,
I am from india and I am working as a VBA Developer in top MNC in India. I would like to say thanks to you for your videos. Your videos is so helpful to inhance the skills in VBA.
Thanks one again.!!!
You are all welcome.
Thanks for the guide, Paul. Very well explained. Cheers from Brazil!
I definitely need to understand arrays once and for all. Hopefully I'll have reached it at the end of this class🤞.
Many thanks. You made it so simple and easy to understand...
Thanks Paul, this has made it really easy to understand arrays. Excellent.
Well explained Paul.
Thanks Powell, excellent explanation.
Greetings from Yemen.
Thanks Paul, I learned so much from this - not just on VBA arrays (for which your explanations and demos are probably the clearest I've seen) but also great example of using break points and locals window. Thanks also for the great questions from those who were able to participate.
Hi Glenn,
Glad you found it useful. I may do a webinar on debugging tools in the future e.g. breakpoints, watch windows, local windows, stepping through the code etc.
Paul
Excellent tutorial.Thanks
Very well explained and helpful! Thank you for sharing this.
I have been using VBA to help my work for a long time. This is by far the best course. Thank you Paul.
I have a question - I have a sub that loops through a few hundreds of product, and in each loop a product object is created. When initiating each object, the program reads from a spreadsheet (20,000+ rows) to set properties. The coding was easy but it takes 10+ min to run. I wonder whether there is a way, when workbook opens, I read in the 20K+ row datasheet into a public array, and then in each loop the product object can be initiated from the array instead of reading the spreadsheet again. I tried to add codes in workbook.open method but didn't work. Can this be done? Thank you so much!
Great video Paul 👍🏾
Thanks Mi
Hi Paul. This video and your webpage is my go to for arrays.
I see you're using the For Loop but can you use the For Each Loop? If so, when would you use the For Each Loop instead of the For Loop.
I've learned from you that the For Each Loop is faster.
great Class.
I can't thank you enough for this video.
You are great, Paul, thanks a lot for you help
Thanks Kris
Thank you for your kind explain.But the screen is too small to see your wonderful vba code.
If you want on a tablet or laptop you should be able to see the code fine.
thank you so much!!
How to re arrange columns in arrays ? Thanks
Thank you very much
Thanks so much for this, Paul.
Do you have a Dictionaries tutorial as well?
Glad you liked it. The dictionary tutorials are coming in the next few weeks.
Thanks Paul.
Thanks a lot for recording! I couldn't join the webcast.
How to use the combination of array ,class and dictionary in real time excel workbook like advancefilter, sir can u show an example of this pls…
First of all thanku so much sir for this great learning , actually I want to know , Is it possible to get value in array from Excel Data model?
Column a, product codes. Row 1 different cities. And the stock available related to each product code and its city. Those values could be negative amd positive. In those cases where the value is negative, you have to look for one positive unit, indicate the city where you want to take this unit from and update both units: one more in the needed city and one less in the giver city. How can u do that?
Ow shit, published 3 years ago! I guess you won’t see this comment. :-(
Hi Paul,
I must have missed something when retyping the code because it returned only the last 199 value when I had increased the arrays to 500 rows
Sub dynamic2()
Dim mark() As Long
Dim lastrow As Long
lastrow = shNumbers.Range("A" & shNumbers.Rows.Count).End(xlUp).row
ReDim mark(1 To lastrow)
Dim i As Long
For i = 1 To lastrow
mark(i) = shNumbers.Range("A" & i)
Next i
For i = 1 To lastrow
Debug.Print mark(i)
Next i
End SubSub dynamic2()
Dim mark() As Long
Dim lastrow As Long
lastrow = shNumbers.Range("A" & shNumbers.Rows.Count).End(xlUp).row
ReDim mark(1 To lastrow)
Dim i As Long
For i = 1 To lastrow
mark(i) = shNumbers.Range("A" & i)
Next i
For i = 1 To lastrow
Debug.Print mark(i)
Next i
End Sub
Very nice video. I get a 424 error when using the basic macro at 15:00 in my Personal.xlsb, BUT it works when I run the macro inside a macro enabled Excel file.
My code name is shNumbers and my worksheet name is Sheet1. mark1 = shNumbers.Range("A1") is what errors (in Personal.xslb only). I run some macros using my Personal.xlsb. How do I write this part of the code (using code name) to work for my Personal.xlsb? I have searched and can't find the answer. Thank you
Hello,
will you be doing more of webinars?
Yes. I usually do them every 4 to 6 weeks. If you join my email list you will get notified of upcoming videos.
Please provide a detailed lecture on excel vba course for beginners, otherwise it's confusing for most learners
You are correct. Thats why you can take advantage of their courses on their website courses.excelmacromastery.com
Hi Im trying to add a command line that goes like ActiveSheet.PivotTables("PivotTable1").PivotFields("[Date].[Month].[Month]"). _
VisibleItemsList = Array("[Date].[Month].&format("01/11/2018","yyyy-MM-dd'T'HH:mm:ss.fffffff'Z'")") but it is returning an error.
Hi Paul,
Many thanks for your videos.
when you code in the video above, I see that once you start typing shn your vba autosuggest shNumbers. I am using excel 2016 and I don't have this feature.
Can I ask please if is my excel version the problem or I have to activate something.
Many thanks.
Hi Alexei. If you press Ctrl + Space it will bring up the Intellisense.
Paul
Many thank Paul, really like your videos. Hope in the future there will be others. Thank you very much
@@Excelmacromastery hi
i cant seem to rename my sheets that way. I cant alter the sheet name in the properties window i can only change the 'name' in brackets. nevertheless i still have to refer to the sheets using "Worksheets("my sheet name").Range
Hi Paul, is a connection.close statement needed for the WriteFoodTypes sub like there was in the ReadDBData sub? If not, why? If so, what might go wrong when it is not added?
Hi Paula,
Yes - you should always close the connection when you are finished with it.
I am new to VBA so I tried the code in the video calling my worksheet "nums", The line "mark = nums.Range("A1")" gave me a 424 error with the comment " object required" Please help
Should be
mark = nums.Range("A1").Value.
nums should be the code name(th-cam.com/video/MU3e6iqc0Rk/w-d-xo.html) of the worksheet. If it's the name of the worksheet use:
mark = ThisWorkbook.Worksheets("nums").Range("A1").Value.
@@Excelmacromastery Thanks
which vba books would you recommend?
If you want to learn VBA I would recommend my course: theexcelvbahandbook.com/. Books are good as a reference guide but they are not really a good way to learn how to create VBA applications.
Do you know how to create a "link" in excel such that, when the user presses it, it unfolds a certain number of columns (which are initially merged) and reversely. I want it to be just a typed phrase, not a button (I know how to do it using toggle button). Thanks a lot!
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
'Do you know how to create a "link" in excel such that, when the user presses it,
'it unfolds a certain number of columns (which are initially merged) and reversely.
'I want it to be just a typed phrase, not a button (I know how to do it using toggle button)
'Unfolds is not a excel term ?????? Assume u mean hide columns
' The Target Cell A6 is the key click or select cell A6
If Target.Range.Address = "$A$6" Then
If ActiveSheet.Range("A6").Text = "Hide" Then
'hide columns C to F
ActiveSheet.Columns("C:F").Hidden = True
ActiveSheet.Range("A6").Value = "Unhide"
Else
ActiveSheet.Columns("C:F").Hidden = False
ActiveSheet.Range("A6").Value = "Hide"
End If
End If
'format cell A6 as required
we can not see there coading bcz out of zoom
Hey Paul, you should do all your website into videos, you are really good,
thanks!