I subscribed and purchased one of your courses. You have a lot to give. You are an example of an individual who loves doing good and helping others. Congratulations on your wonderful soul
Thank for you Randy for this excellent Video, I enjoyed learning about arrays,, but I am about to ask a crazy question,,, what font do you use for your vba coding?
Hi and thanks for your support. Its a nice question. I was not sure, so I had to look it up. its Arial (Western) Font Size 16. I hope this helps and thanks so much.
In the 2DArray example, why are we able to omit the '()' parenthesis in the 'Dim TwoDArr as Variant' line? I am thinking it has something to do with the data type, 'Variant'.
Yes that is correct, as a variant, its a much more flexible variable than the others. Strings, Long or other variables need to be specified. Thank you for your Likes, Shares & Comments. It really helps.
Excellent run through. I need to have a play with Arrays in VBA ... familiar with them in C/Python so this should be interesting. One question (I'm probably being dim) - in Sub DynamicBoundsArray(), why is the RowNumb = 34? The array is length 30, first (Excel) row 4 and last 33 (so not a +1 because it is zero to ...) why isn't it 33 as this is the final row processed [RowNumb = 4 to 33]?
HI and thanks, the item in an array is counted as 0 if its not specified so therefore the rows should always be 1 more than the array value. I hope this helps and thanks so much.
Thanks so much. Yes its certainly possible. I checked it out and seemed a bit complicated for one of our Basic VBA tutorials but I will try to get it added into one of the more advanced lessons. Thanks for your continued support over the years.
I am not sure if its faster, however I prefer the For Next simply because of the clearly defined data set. I use for Each when I loop through objects such as Shapes, Worksheets or Workbooks. I hope this helps and thanks so much.
Are you planning to do videos of applications that work with office scripts? There is a real need of using applications for excel for the web and also application that can be run by multiple users at the same time (vba workbooks that are used at the same time by multiple users can give errors), and office scripts is the tool to avoid these inconveniences
Hi and thanks. I would love to for sure, however Office Scripts are nowhere near being ready for that. For example worksheet/workbook change events and selection events do not exist in scripts as of yet, so as soon as the power is up to the VBA level I will be creating videos for scripts aw well. I hope this helps and thanks so much.
Hi and thanks so much. This would be a custom job since its very specific. If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet) You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project. I hope this helps and thanks so much.
Hi sir. I have a problem in excel I have data that was downloaded from a software which is totally web based. In that excel all values are in text format including dates. If I give f2+enter that text will turn to proper date. But the problem is there are a lot of cells like in thousands. I tried converting to CSV. It works sometimes and sometimes it make the date to us format. Like 1/4/24 (1st April) will become 4th jan 24. Is there any VBA code that you can suggest to solve that issue
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Hi Randy, how are you? Your explanation is superb. No one can explain these topics so easily. Randy, recently I've come across a problem like this. Would you kindly make a video on this topic - "How to split Strings that contain no Delimiter using User Defined Function in Excel VBA?" For Example, - You have some client names on your Sheet1 from Range("A2") to Range("A16"): Raw Data: ======== Names First Names | Middle Names | Last Names ------------ -------------------- ------------------------ -------------------- AlexanderWilliamJamesBrown AmandaTaylor AriaNguyen AubreyFoster AveryAnnPhillips Beerus BenjaminThomasHarris CharlotteMitchell ChristopherJackson DanielJamesMiller DavidAndrewJohnWilliams Dende DylanJamesWard ElijahWatson EllaSophiaRoberts ========== Here, some clients have First Names, Middle Names and Last Names (For Example: Alexander William James Brown etc.); some have First Names and Last Names (For Example: Amanda Taylor etc.) and some have only First Names (For Example: Beerus etc.). Now, I want to delimitate each name based on the Capital Letter. Desired Output mentioned below: Desired Output: ============= Names First Names | Middle Names | Last Names ------------ -------------------- ------------------------ -------------------- AlexanderWilliamJamesBrown Alexander | William James | Brown AmandaTaylor Amanda | | Taylor AriaNguyen Aria | | Nguyen AubreyFoster Aubrey | | Foster AveryAnnPhillips Avery | Ann | Phillips Beerus Beerus BenjaminThomasHarris Benjamin | Thomas | Harris CharlotteMitchell Charlotte | | Mitchell ChristopherJackson Christopher | | Jackson DanielJamesMiller Daniel | James | Miller DavidAndrewJohnWilliams David | Andrew John | Williams Dende Dende DylanJamesWard Dylan | James | Ward ElijahWatson Elijah | | Watson EllaSophiaRoberts Ella | Sophia | Roberts
Hi and thanks very much. Yes it should be possible looping through the characters in a string and uusing the Asc code such as Function SplitByCapitalLetters(inputStr As String) As String Dim i As Integer Dim result As String result = Mid(inputStr, 1, 1) ' Start with the first character (always a capital letter) ' Loop through the rest of the characters in the string For i = 2 To Len(inputStr) If Asc(Mid(inputStr, i, 1)) >= 65 And Asc(Mid(inputStr, i, 1))
PLEASE SUBSCRIBE !! SUBSCRIBE TO RANDY'S HARD WORK, THE BEST VBA TEACHER ON RUclips. Thumbs up 👍to his passion & dedication to teach. Thanks Randy for another excellent, valuable tutorial. God bless you!
Look Randy, I've created a Dynamic Spilled Array Formulae that can split any Names that contain any number of Spaces as a Delimiter. Thank you very much for your priceless training. Next, I will try to create splitting Names that contain No Delimiter, splitting strings with Multiple Delimiters, splitting Text and Numbers from AlphaNumeric Characters and last but not the least, splitting the Domain Names from URL. (I've tried my best to align the Data here on RUclips Comment Section. Please don't read this comment on your Mobile Phone as the alignments of text (Raw Data and Output) are not correct on Phone as they are on PC or Laptop.) Raw Data: ---------------- Sr. No. Names First Names Middle Names Last Names 001 Goku 002 Amanda Taylor 003 Alexander William James Brown 004 Florian Henckel Von Donner Smarck 005 Frieza 006 Lily Grace Cooper 007 Vegeta Output: ------------ Sr. No. Names First Names Middle Names Last Names 001 Goku Goku 002 Amanda Taylor Amanda Taylor 003 Alexander William James Brown Alexander William James Brown 004 Florian Henckel Von Donner Smarck Florian Henckel Von Donner Smarck 005 Frieza Friza 006 Lily Grace Cooper Lily Grace Cooper 007 Vegeta Vegeta Code: --------- Option Explicit Function SpilledArrayNamesByRange(InputRange As Range) As Variant 'Here, I set the Return Type of Function as Variant since it delivers an Array of Values. Dim SplitNamesBasedOnSpace() As String
Dim MaxNameRow As Long MaxNameRow = InputRange.Rows.Count - 1
Dim MaxNameColumn As Long MaxNameColumn = 2
ReDim SplitNamesBasedOnSpace(0 To MaxNameRow, 0 To MaxNameColumn)
Dim Cell As Range
Dim CurrentRow As Long CurrentRow = 0
For Each Cell In InputRange Dim NamePart() As String NamePart = Strings.Split(Expression:=Strings.Trim(String:=Cell.Value), Delimiter:=" ")
If UBound(NamePart) = -1 Then 'Incase of Blank Cell, The UBound Array Function returns -1 SplitNamesBasedOnSpace(CurrentRow, 0) = Empty SplitNamesBasedOnSpace(CurrentRow, 1) = Empty SplitNamesBasedOnSpace(CurrentRow, 2) = Empty ElseIf UBound(NamePart) = 0 Then SplitNamesBasedOnSpace(CurrentRow, 0) = NamePart(0) SplitNamesBasedOnSpace(CurrentRow, 1) = Empty SplitNamesBasedOnSpace(CurrentRow, 2) = Empty ElseIf UBound(NamePart) = 1 Then SplitNamesBasedOnSpace(CurrentRow, 0) = NamePart(0) SplitNamesBasedOnSpace(CurrentRow, 1) = Empty SplitNamesBasedOnSpace(CurrentRow, 2) = NamePart(1) ElseIf UBound(NamePart) = 2 Then SplitNamesBasedOnSpace(CurrentRow, 0) = NamePart(0) SplitNamesBasedOnSpace(CurrentRow, 1) = NamePart(1) SplitNamesBasedOnSpace(CurrentRow, 2) = NamePart(2) Else Dim Store As String 'Store comes into use when there are more than 2 (0 To 2) Name Parts (For Example: Florian Henckel Von Donner Smarck)
Dim Counter As Long
For Counter = 1 To UBound(NamePart) - 1 Store = Store & " " & NamePart(Counter) Next Counter
Store = Strings.Trim(String:=Store)
SplitNamesBasedOnSpace(CurrentRow, 0) = NamePart(LBound(NamePart)) SplitNamesBasedOnSpace(CurrentRow, 1) = Store SplitNamesBasedOnSpace(CurrentRow, 2) = NamePart(UBound(NamePart)) End If
Store = Empty CurrentRow = CurrentRow + 1 Next Cell
SpilledArrayNamesByRange = SplitNamesBasedOnSpace End Function
Wow that's amazing and a great idea! I look forward to seeing your solutions for all these other cases. I'm always amazed by the creative ways people use VBA.
😱 𝗧𝗨𝗥𝗡 𝗖𝗢𝗠𝗣𝗟𝗘𝗫 𝗗𝗔𝗧𝗔 𝗜𝗡𝗧𝗢 𝗦𝗜𝗠𝗣𝗟𝗘 𝗦𝗢𝗟𝗨𝗧𝗜𝗢𝗡𝗦 𝗪𝗜𝗧𝗛 𝗔𝗜 ▶ www.excelforfreelancers.com/AiDataAnalystYTPinnedComment
Arrays are the sickest and hardest elements 😂❤❤❤ can't wait!!!
Great, thanks so much and I am happy to help and share
Once you master Array you"ll need to learn Class for arrays (dynamic classes for arrays) , then Dictionary.
Thank you so much Randy for this amazing video.
For sure, you are very welcome Syed and thanks so much for your continued support
Arrays and loops are fundamental for advanced programming.
Absolutely, one of my favorites. Thanks so much for sharing
Thank you very much Randy Sir for the wonderful explanation on the arrays
For sure, you are very welcome and I am happy to help and share
I subscribed and purchased one of your courses. You have a lot to give. You are an example of an individual who loves doing good and helping others. Congratulations on your wonderful soul
Thank you so very much, I really appreciate that and I am really glad you enjoy the content and courses.
Thank for you Randy for this excellent Video, I enjoyed learning about arrays,, but I am about to ask a crazy question,,, what font do you use for your vba coding?
Hi and thanks for your support. Its a nice question. I was not sure, so I had to look it up. its Arial (Western) Font Size 16.
I hope this helps and thanks so much.
⚡ 𝗦𝗜𝗠𝗣𝗟𝗜𝗙𝗬 𝗖𝗢𝗠𝗣𝗟𝗘𝗫 𝗘𝗫𝗖𝗘𝗟 𝗧𝗔𝗦𝗞𝗦 𝗜𝗡𝗦𝗧𝗔𝗡𝗧𝗟𝗬 𝗪𝗜𝗧𝗛 𝗘𝗫𝗖𝗘𝗟 𝗔𝗜 𝗧𝗢𝗢𝗟𝗣𝗔𝗖𝗞 𝗔𝗗𝗗-𝗜𝗡 👉 www.excelforfreelancers.com/ai-toolpack
Thank you Randy I'll try them...
For sure, you are very welcome and happy to help and share
In the 2DArray example, why are we able to omit the '()' parenthesis in the 'Dim TwoDArr as Variant' line? I am thinking it has something to do with the data type, 'Variant'.
Yes that is correct, as a variant, its a much more flexible variable than the others. Strings, Long or other variables need to be specified. Thank you for your Likes, Shares & Comments. It really helps.
Great introduction to Arrays!!!😂❤❤❤ Thank you ❤
Thank you so very much, I really appreciate that and glad you enjoyed the training.
Thanks
For sure, you are very welcome
Excellent run through. I need to have a play with Arrays in VBA ... familiar with them in C/Python so this should be interesting.
One question (I'm probably being dim) - in Sub DynamicBoundsArray(), why is the RowNumb = 34? The array is length 30, first (Excel) row 4 and last 33 (so not a +1 because it is zero to ...) why isn't it 33 as this is the final row processed [RowNumb = 4 to 33]?
HI and thanks, the item in an array is counted as 0 if its not specified so therefore the rows should always be 1 more than the array value. I hope this helps and thanks so much.
Thanks Randy as always great content.
Possible to explain the filter array in Patreon?
Thanks so much. Yes its certainly possible. I checked it out and seemed a bit complicated for one of our Basic VBA tutorials but I will try to get it added into one of the more advanced lessons. Thanks for your continued support over the years.
Is t it faster to use a For Next loop rather than a For Each loop? 1:09
I am not sure if its faster, however I prefer the For Next simply because of the clearly defined data set. I use for Each when I loop through objects such as Shapes, Worksheets or Workbooks. I hope this helps and thanks so much.
amazing video THNX Randy
Thank you so very much, I really appreciate that and glad you enjoyed the training
Are you planning to do videos of applications that work with office scripts? There is a real need of using applications for excel for the web and also application that can be run by multiple users at the same time (vba workbooks that are used at the same time by multiple users can give errors), and office scripts is the tool to avoid these inconveniences
Hi and thanks. I would love to for sure, however Office Scripts are nowhere near being ready for that. For example worksheet/workbook change events and selection events do not exist in scripts as of yet, so as soon as the power is up to the VBA level I will be creating videos for scripts aw well. I hope this helps and thanks so much.
Great! May you make a file for QA manager in a factory 🏭
Hi and thanks so much. This would be a custom job since its very specific. If you would like some help with this, we have a group with 60,000 Excel experts who would love the opportunity to customize this for you. Please join our Excel For Freelancers Facebook Group here: bit.ly/groupexcel (if you have not done so yet)
You can then create a post and mark it as a PAID JOB, along with all of the details and requirements. Our members can then contact you and decide on how to best move forward with your project.
I hope this helps and thanks so much.
Dictionaries are another type of arrays.
Listarrays is another powerful type of arrays.
Absolutely, thanks so much for sharing
Learning arrays to optimize my VBA code
You must be very well organized from very begining to be able to use arrays. That's the trick.
Great, thanks so much. I am happy to help and share
Hi sir. I have a problem in excel
I have data that was downloaded from a software which is totally web based. In that excel all values are in text format including dates. If I give f2+enter that text will turn to proper date. But the problem is there are a lot of cells like in thousands. I tried converting to CSV. It works sometimes and sometimes it make the date to us format. Like 1/4/24 (1st April) will become 4th jan 24. Is there any VBA code that you can suggest to solve that issue
Hi , Thanks for your comment. It would be difficult for us to help unless we see the specific bug you are running into. Can you upload screenshots of your bug and sheet into our Excel For Freelancers Facebook Group here: bit.ly/groupexcel We have 60,000 Members who would love to help you with this. Thanks so much.
Once you master Array you"ll need to master Class for arrays (dynamic classes for arrays) , then Dictionary.
Thanks so much for your continued support.
Hi Randy, how are you? Your explanation is superb. No one can explain these topics so easily. Randy, recently I've come across a problem like this. Would you kindly make a video on this topic - "How to split Strings that contain no Delimiter using User Defined Function in Excel VBA?" For Example, - You have some client names on your Sheet1 from Range("A2") to Range("A16"):
Raw Data:
========
Names First Names | Middle Names | Last Names
------------ -------------------- ------------------------ --------------------
AlexanderWilliamJamesBrown
AmandaTaylor
AriaNguyen
AubreyFoster
AveryAnnPhillips
Beerus
BenjaminThomasHarris
CharlotteMitchell
ChristopherJackson
DanielJamesMiller
DavidAndrewJohnWilliams
Dende
DylanJamesWard
ElijahWatson
EllaSophiaRoberts
==========
Here, some clients have First Names, Middle Names and Last Names (For Example: Alexander William James Brown etc.); some have First Names and Last Names (For Example: Amanda Taylor etc.) and some have only First Names (For Example: Beerus etc.). Now, I want to delimitate each name based on the Capital Letter. Desired Output mentioned below:
Desired Output:
=============
Names First Names | Middle Names | Last Names
------------ -------------------- ------------------------ --------------------
AlexanderWilliamJamesBrown Alexander | William James | Brown
AmandaTaylor Amanda | | Taylor
AriaNguyen Aria | | Nguyen
AubreyFoster Aubrey | | Foster
AveryAnnPhillips Avery | Ann | Phillips
Beerus Beerus
BenjaminThomasHarris Benjamin | Thomas | Harris
CharlotteMitchell Charlotte | | Mitchell
ChristopherJackson Christopher | | Jackson
DanielJamesMiller Daniel | James | Miller
DavidAndrewJohnWilliams David | Andrew John | Williams
Dende Dende
DylanJamesWard Dylan | James | Ward
ElijahWatson Elijah | | Watson
EllaSophiaRoberts Ella | Sophia | Roberts
Hi and thanks very much. Yes it should be possible looping through the characters in a string and uusing the Asc code such as
Function SplitByCapitalLetters(inputStr As String) As String
Dim i As Integer
Dim result As String
result = Mid(inputStr, 1, 1) ' Start with the first character (always a capital letter)
' Loop through the rest of the characters in the string
For i = 2 To Len(inputStr)
If Asc(Mid(inputStr, i, 1)) >= 65 And Asc(Mid(inputStr, i, 1))
@ExcelForFreelancers Thank you Randy. You're the best VBA Trainer.
Thank you so very much, I really appreciate that
Thank you for your Likes, Shares & Comments. It really helps.
PLEASE SUBSCRIBE !! SUBSCRIBE TO RANDY'S HARD WORK, THE BEST VBA TEACHER ON RUclips. Thumbs up 👍to his passion & dedication to teach. Thanks Randy for another excellent, valuable tutorial. God bless you!
Thank you so very much, I really appreciate that Jojo, so very kind of you. Thank you for your Likes, Shares & Comments. It really helps.
Look Randy, I've created a Dynamic Spilled Array Formulae that can split any Names that contain any number of Spaces as a Delimiter. Thank you very much for your priceless training. Next, I will try to create splitting Names that contain No Delimiter, splitting strings with Multiple Delimiters, splitting Text and Numbers from AlphaNumeric Characters and last but not the least, splitting the Domain Names from URL. (I've tried my best to align the Data here on RUclips Comment Section. Please don't read this comment on your Mobile Phone as the alignments of text (Raw Data and Output) are not correct on Phone as they are on PC or Laptop.)
Raw Data:
----------------
Sr. No. Names First Names Middle Names Last Names
001 Goku
002 Amanda Taylor
003 Alexander William James Brown
004 Florian Henckel Von Donner Smarck
005 Frieza
006 Lily Grace Cooper
007 Vegeta
Output:
------------
Sr. No. Names First Names Middle Names Last Names
001 Goku Goku
002 Amanda Taylor Amanda Taylor
003 Alexander William James Brown Alexander William James Brown
004 Florian Henckel Von Donner Smarck Florian Henckel Von Donner Smarck
005 Frieza Friza
006 Lily Grace Cooper Lily Grace Cooper
007 Vegeta Vegeta
Code:
---------
Option Explicit
Function SpilledArrayNamesByRange(InputRange As Range) As Variant 'Here, I set the Return Type of Function as Variant since it delivers an Array of Values.
Dim SplitNamesBasedOnSpace() As String
Dim MaxNameRow As Long
MaxNameRow = InputRange.Rows.Count - 1
Dim MaxNameColumn As Long
MaxNameColumn = 2
ReDim SplitNamesBasedOnSpace(0 To MaxNameRow, 0 To MaxNameColumn)
Dim Cell As Range
Dim CurrentRow As Long
CurrentRow = 0
For Each Cell In InputRange
Dim NamePart() As String
NamePart = Strings.Split(Expression:=Strings.Trim(String:=Cell.Value), Delimiter:=" ")
If UBound(NamePart) = -1 Then 'Incase of Blank Cell, The UBound Array Function returns -1
SplitNamesBasedOnSpace(CurrentRow, 0) = Empty
SplitNamesBasedOnSpace(CurrentRow, 1) = Empty
SplitNamesBasedOnSpace(CurrentRow, 2) = Empty
ElseIf UBound(NamePart) = 0 Then
SplitNamesBasedOnSpace(CurrentRow, 0) = NamePart(0)
SplitNamesBasedOnSpace(CurrentRow, 1) = Empty
SplitNamesBasedOnSpace(CurrentRow, 2) = Empty
ElseIf UBound(NamePart) = 1 Then
SplitNamesBasedOnSpace(CurrentRow, 0) = NamePart(0)
SplitNamesBasedOnSpace(CurrentRow, 1) = Empty
SplitNamesBasedOnSpace(CurrentRow, 2) = NamePart(1)
ElseIf UBound(NamePart) = 2 Then
SplitNamesBasedOnSpace(CurrentRow, 0) = NamePart(0)
SplitNamesBasedOnSpace(CurrentRow, 1) = NamePart(1)
SplitNamesBasedOnSpace(CurrentRow, 2) = NamePart(2)
Else
Dim Store As String 'Store comes into use when there are more than 2 (0 To 2) Name Parts (For Example: Florian Henckel Von Donner Smarck)
Dim Counter As Long
For Counter = 1 To UBound(NamePart) - 1
Store = Store & " " & NamePart(Counter)
Next Counter
Store = Strings.Trim(String:=Store)
SplitNamesBasedOnSpace(CurrentRow, 0) = NamePart(LBound(NamePart))
SplitNamesBasedOnSpace(CurrentRow, 1) = Store
SplitNamesBasedOnSpace(CurrentRow, 2) = NamePart(UBound(NamePart))
End If
Store = Empty
CurrentRow = CurrentRow + 1
Next Cell
SpilledArrayNamesByRange = SplitNamesBasedOnSpace
End Function
Wow that's amazing and a great idea! I look forward to seeing your solutions for all these other cases. I'm always amazed by the creative ways people use VBA.
@@ExcelForFreelancers Thank you Randy, it's all your credit. Without you, I am nothing.
@@ExcelForFreelancers If you're in my country, I'll personally contact to you. You're a great teacher and above all you're a great human being.
Thank you so very much, I really appreciate that