Hi, in your first dynamic array example (6:00 - 6:54), wouldn't it be simpler to form your data into a named table? If it was a named table, all you need to do is load the named table into memory. Is that correct? Or am I missing something?
Hi, Named table is definitely, one way to do it. But, I would say, it would apply when you are in control of the Input data source. However, dynamic arrays would have a more generic use-case. e.g. suppose you are picking up data from a csv file. or, from a excel file coming from a third party source, which doesn't have a table in it. Also, using tables would be a design choice e.g. do we want to maintain 100% logic within our VBA code, or do we want to share the logic with other Excel elements such as tables, formulas? what is the estimated size of data? is our data connected to another data sourcce such as SQL table? etc. Besides that, VBA is notorious for having alternative solutions to the same problem/ task. Hence, in my videos, I will tend to try and accomplish every task using the topic for the video i.e. in this case, dynamic array. Cheers
In the real world, nobody works with ranges anymore. We create tables for our data and use power query and power pivot. And still there are situations where vba is the only way to go. It would be interesting to watch a video using dynamic arrays in this scenario
Hi, Do you mean creating dependent dropdown lists? Short answer, Yes. If that is all you want, you could achieve it using activeX combo boxes and populate the next combo box, based on change event of the first. If you wanted to achieve it without combo boxes, you could dynamically populate the second data validation list each time Range.Validation.Add xlValidateList, Formula1:=sComments -> where sComments is string of comma separated filtered values based on the selected value in previous list, and tie this macro to an event or button trigger.
@@SkillsandAutomation Brilliant thanks, I think dynamically populate a second data validation list sounds good. I'll try and figure out how to do that. thanks
Hi, Thanks for your comment. No paid course. But, I will be releasing a detailed zero to hero VBA series on RUclips starting April 2024. Please check it out if it interests you.
Sir, is there any way to apply Strings.Filter method in 2 dimensional array in Excel VBA? I want to filter array on single or multiple conditions. Or, you can say, I want to store data into The Final Array (ActionFilmDetails) that meet the Condition (Single or Multiple). I've written the below-mentioned code. But, I can't understand, why this code gives me "Run-time Error'13': Type Mismatch" Error? Raw Data: ID Title Released Date Length Genre 001 Marvel's The Avengers 04-May-12 143 Action 002 The Dark Knight Rises 20-Jul-12 165 Action 003 The Hunger Games 23-Mar-12 142 Adventure 004 Skyfall 09-Nov-12 143 Action 005 The Hobbit: An Unexpected Journey 14-Dec-12 169 Fantasy 006 The Twilight Saga: Breaking Dawn Part 2 16-Nov-12 116 Awful 007 The Amazing Spider-Man 03-Jul-12 136 Action ============================ Code: Option Explicit Sub CopyActionFilmsOnly() ThisWorkbook.Save
Dim LastRow As Long LastRow = Sheet3.Range("A1").Offset(RowOffset:=0, ColumnOffset:=1).End(xlDown).Row
Dim LastColumn As Long LastColumn = Sheet3.Range("A1").End(xlToRight).Column
Dim FilmDetails As Variant FilmDetails = Sheet3.Range("A1").Offset(RowOffset:=1, ColumnOffset:=0).Resize(RowSize:=LastRow - 1, ColumnSize:=LastColumn)
Dim RowCounter As Long Dim ColumnCounter As Long
Dim ActionFilmDetails As Variant
For RowCounter = LBound(FilmDetails, 1) To UBound(FilmDetails, 1) For ColumnCounter = LBound(FilmDetails, 2) To UBound(FilmDetails, 2) If FilmDetails(RowCounter, 5) = "Action" Then ActionFilmDetails(RowCounter, ColumnCounter) = FilmDetails(RowCounter, ColumnCounter) Else Rem Nothing is needed to do End If Next ColumnCounter Next RowCounter End Sub
Hi, This code uses the Worksheet Code Name e.g. wsOut. If your code names don't match as per the video, the code will not run for you. I briefly mention the code name setup at the 04:29 mark. Cheers
Hello, Links for the code and macro file are given within the description. See you in the next video!
Sir , do you have a paid course on VBA , I will buy that right away !! Kindly suggest
Please post weekly. I love this channel, extremely informative examples
VERY USEFULL
Nice Explained.
great video
Hi, in your first dynamic array example (6:00 - 6:54), wouldn't it be simpler to form your data into a named table? If it was a named table, all you need to do is load the named table into memory. Is that correct? Or am I missing something?
Hi,
Named table is definitely, one way to do it. But, I would say, it would apply when you are in control of the Input data source. However, dynamic arrays would have a more generic use-case. e.g. suppose you are picking up data from a csv file. or, from a excel file coming from a third party source, which doesn't have a table in it.
Also, using tables would be a design choice e.g. do we want to maintain 100% logic within our VBA code, or do we want to share the logic with other Excel elements such as tables, formulas? what is the estimated size of data? is our data connected to another data sourcce such as SQL table? etc.
Besides that, VBA is notorious for having alternative solutions to the same problem/ task. Hence, in my videos, I will tend to try and accomplish every task using the topic for the video i.e. in this case, dynamic array.
Cheers
Sir i need your help for create a cutting optimization program.
please upload video regularly
In the real world, nobody works with ranges anymore. We create tables for our data and use power query and power pivot. And still there are situations where vba is the only way to go. It would be interesting to watch a video using dynamic arrays in this scenario
Can you use VBA to take data from drop down box and filter it from a table dataset to populate anothe drop down next to it and so on?
Hi, Do you mean creating dependent dropdown lists? Short answer, Yes. If that is all you want, you could achieve it using activeX combo boxes and populate the next combo box, based on change event of the first. If you wanted to achieve it without combo boxes, you could dynamically populate the second data validation list each time
Range.Validation.Add xlValidateList, Formula1:=sComments -> where sComments is string of comma separated filtered values based on the selected value in previous list, and tie this macro to an event or button trigger.
@@SkillsandAutomation Brilliant thanks, I think dynamically populate a second data validation list sounds good. I'll try and figure out how to do that. thanks
Sir Make Video On Listbox having more than 10 columns and also explained listbox formatting . Thanks in advance.
Hi. Thanks for all your comments. I will pick up this request as well. But, video will take a while. Will update once uploaded. Cheers, Ash
Sir , do you have a paid course on VBA , I will buy that right away !! Kindly suggest
Hi, Thanks for your comment. No paid course. But, I will be releasing a detailed zero to hero VBA series on RUclips starting April 2024. Please check it out if it interests you.
Sir, is there any way to apply Strings.Filter method in 2 dimensional array in Excel VBA? I want to filter array on single or multiple conditions. Or, you can say, I want to store data into The Final Array (ActionFilmDetails) that meet the Condition (Single or Multiple). I've written the below-mentioned code. But, I can't understand, why this code gives me "Run-time Error'13': Type Mismatch" Error?
Raw Data:
ID Title Released Date Length Genre
001 Marvel's The Avengers 04-May-12 143 Action
002 The Dark Knight Rises 20-Jul-12 165 Action
003 The Hunger Games 23-Mar-12 142 Adventure
004 Skyfall 09-Nov-12 143 Action
005 The Hobbit: An Unexpected Journey 14-Dec-12 169 Fantasy
006 The Twilight Saga: Breaking Dawn Part 2 16-Nov-12 116 Awful
007 The Amazing Spider-Man 03-Jul-12 136 Action
============================
Code:
Option Explicit
Sub CopyActionFilmsOnly()
ThisWorkbook.Save
Sheet3.Range("G1").CurrentRegion.Offset(RowOffset:=1, ColumnOffset:=0).ClearContents
Dim LastRow As Long
LastRow = Sheet3.Range("A1").Offset(RowOffset:=0, ColumnOffset:=1).End(xlDown).Row
Dim LastColumn As Long
LastColumn = Sheet3.Range("A1").End(xlToRight).Column
Dim FilmDetails As Variant
FilmDetails = Sheet3.Range("A1").Offset(RowOffset:=1, ColumnOffset:=0).Resize(RowSize:=LastRow - 1, ColumnSize:=LastColumn)
Dim RowCounter As Long
Dim ColumnCounter As Long
Dim ActionFilmDetails As Variant
For RowCounter = LBound(FilmDetails, 1) To UBound(FilmDetails, 1)
For ColumnCounter = LBound(FilmDetails, 2) To UBound(FilmDetails, 2)
If FilmDetails(RowCounter, 5) = "Action" Then
ActionFilmDetails(RowCounter, ColumnCounter) = FilmDetails(RowCounter, ColumnCounter)
Else
Rem Nothing is needed to do
End If
Next ColumnCounter
Next RowCounter
End Sub
hello , I tried to add the wsOut.Cells.Clear into my code but it says"The Varible is not defined" what might be the poblem?
Hi, This code uses the Worksheet Code Name e.g. wsOut. If your code names don't match as per the video, the code will not run for you. I briefly mention the code name setup at the 04:29 mark. Cheers