Coming to the dark Side

[annonymous33]

Hello,

Attachment here, no idea what it is embedded>> 26407

New to the forums, originally Australian but now live in sunny London.

I am reasonably proficient with excel, but only with native formulas.

I am keen to jump into VBA but keep getting stuck

To convert the below formula into a loop to continue until the rows are empty, what would the code look like?

IFERROR(INDEX(Inputs!$C$1:$C$7,MATCH('COVER Summary'!A4&'COVER Summary'!B4,Inputs!$A$1:$A$7&Inputs!$B$1:$B$7,0)),"MISSING")

[Logit]

.
If I correctly understand what you are wanting to do ...

Click on C4 on the "COVER Summary" sheet. Now notice how a very small dot appears in the lower right corner of that cell ?
Double click on that dot and the formula that is in C4 will automatically paste itself in all the cells below ... AND ... will
automatically stop when it comes to the last entry in Col B.

[Paul_Hossler]

Here's a simple VBA approach. Added comments, but I suggest you get a good book or find a tutorial on the web




Option Explicit


Sub ExecSummary()
'define some variables
Dim wsSummary As Worksheet, wsInputs As Worksheet
Dim rSummary As Range, rInputs As Range
Dim collInputs As Collection
Dim iSummary As Long, iInput As Long

'initialize them
Set wsSummary = Worksheets("COVER Summary")
Set wsInputs = Worksheets("Inputs")


Set rSummary = wsSummary.Range("A3").CurrentRegion ' all cells around A3
Set rInputs = wsInputs.Range("A1").CurrentRegion


Set collInputs = New Collection


'add the number to a collection with country1#country2 as the key
For iInput = 1 To rInputs.Rows.Count
On Error Resume Next
collInputs.Add rInputs.Cells(iInput, 3).Value, rInputs.Cells(iInput, 1).Value & "#" & rInputs.Cells(iInput, 2).Value
On Error GoTo 0
Next iInput


'go down the summary using country1#country2 as the key to get the number out of the collection
For iSummary = 1 To rSummary.Rows.Count
rSummary.Cells(iSummary, 3).Value = "MISSING" ' just in case
On Error Resume Next
rSummary.Cells(iSummary, 3).Value = collInputs.Item(rSummary.Cells(iSummary, 1).Value & "#" & rSummary.Cells(iSummary, 2).Value)
On Error GoTo 0
Next iSummary


End Sub

[annonymous33]

Paul,

Thanks for your help and the feedback. Many years ago I ran a scene release group and found it frustrating when people would just appear to sap information, which is what i've done!

I've actually read two books on it, and spent countless hours trying to work it out, but i am just "missing something". I found it the same with native formulas and lookups. IT clicked one day and ive been able to do anything i want from that point. I think the same will be for VBA but im missing "that link"

I normally get frustrated with the "VBA for dummies" etc as its 300 pages on creating a message box, and I dont care for message boxes.

Could you recommend a source or a place to go? How did you start?

[annonymous33]

.
If I correctly understand what you are wanting to do ...

Click on C4 on the "COVER Summary" sheet. Now notice how a very small dot appears in the lower right corner of that cell ?
Double click on that dot and the formula that is in C4 will automatically paste itself in all the cells below ... AND ... will
automatically stop when it comes to the last entry in Col B.

Thanks for your feedback Logit, much appreciated.

I see what you mean, as in "autofill".

I was looking for the VBA equivalent of that lookup.

I do a lot of spreadsheets, and some are so big they are now bordering databases. Some are analysis on millions of rows, so I am looking to move over to VBA. It takes WAY too long with formulas.

Paul provided a response, next step, convert it to English :D

I can read it actually, which is kind of a start! But as everyone codes differently i cant follow a set pattern!

How did you get into VBA?

[Logit]

.
Glad you found an answer.

Yes, using VBA is much better if you are dealing with that amount of data.

Cheers.

[annonymous33]

How did you learn VBA? Online or?

[Logit]

.

"How did you learn VBA? Online or?"

Online.

I wanted to create a personal project that assists with a hobby I pursue. I had the basic idea in my mind and a general layout in the workbook.
Some of the simple stuff I already had working. The remainder I found the answers by posting questions on this Forum and others. The main
FORUM was Excel.com .

Anyway, from my own errors and having to drop back and redo so many times, I learned from my mistakes and by the assistance of others.
Once I gathered enough knowledge to put together some rudimentary stuff, I forced myself to begin volunteering answering the questions
others posted on the FORUMS. That expanded my knowledge a great deal.

At this point I am comfortable with my currently knowledge level, but it is no where near what it should be to program with ease. I still
struggle with a lot of stuff and continue to return to the FORUMS seeking assistance.

I guess coding is a life long pursuit.

Glad you found an answer. Cheers. Stay Healthy !

[Zack Barresse]

A good way to learn coding is to look at others posts and try to implement their solutions. You have the advantage of seeing others post solutions and testing them. Learn to step through code, debug, and test. A set of good books never hurt either. While I mostly agree with this assessment (https://analystcave.com/top-3-best-excel-vba-books-recommended-vba-books/), I would also add Professional Excel Development 2nd Edition (https://smile.amazon.com/Professional-Excel-Development-Definitive-Applications/dp/0321508793/ref=smi_www_rco2_go_smi_b8217842112?_encoding=UTF8&%2AVersion%2A=1&%2Aentries%2A=0&ie=UTF8).

[snb]

And... do not complicate things unnecessarily in VBA:


Sub M_snb()
sn = Sheets("Cover summary").Cells(1).CurrentRegion.Columns(1).Resize(, 3)
sp = Sheets("inputs").Range("A1:C7")

For j = 4 To UBound(sn)
sn(j, 3) = "missing"
For jj = 1 To UBound(sp)
If sn(j, 1) & sn(j, 2) = sp(jj, 1) & sp(jj, 2) Then Exit For
Next
If jj <= UBound(sp) Then sn(j, 3) = sp(jj, 3)
Next

Sheets("Cover summary").Cells(1).Resize(UBound(sn), UBound(sn, 2)) = sn
End Sub

1. reduce reading/writing from/to worksheets to a minimum
2. use arrays as much as possible (for speed's sake)


steps in the code
- ' read range "A1:Cn" into array sn
- ' read range "A1:C7) into array sp
- ' start a loop in the array corresponding to cell A4, end where no data can be found = ubound()
- ' loop through all 7 'rows' of array sp
- ' if a matcht is found, leave the loop
- ' if a match is found, then put the result in the third 'column' of array sn
- ' write the results into sheet 'cover summary'

[Zack Barresse]

I still hate your variable naming @snb, but nice bit of coding there. :clap:

[Logit]

And... do not complicate things unnecessarily in VBA:

[CODE]Sub M_snb()
sn = Sheets("Cover summary").Cells(1).CurrentRegion.Columns(1).Resize(, 3)
sp = Sheets("inputs").Range("A1:C7")

For j = 4 To UBound(sn)
sn(j, 3) = "missing"
For jj = 1 To UBound(sp)
If sn(j, 1) & sn(j, 2) = sp(jj, 1) & sp(jj, 2) Then Exit For
Next
If jj <= UBound(sp) Then sn(j, 3) = sp(jj, 3)
Next


Beautiful bit of code ... BUT ... I don't understand a single thing that is there. (Well .. some of it but certainly not the majority.)
Wish I knew more about VBA.

[Zack Barresse]

@Logit, his explanation under the code should take care of the understanding part. Understand the logic first, the coding/syntax will follow. The former is the most important.

[snb]

@Logit

Basics of Arrays: https://www.snb-vba.eu/VBA_Arrays_en.html

@zack

The variable names:

- are inherently non-interfering with reserved VBA/Excel/Office terms
- are systematic: first loop with j, first nested loop with jj, third nested loop with jjj, etc.
- arrays are 2-letter variablels, always starting with an 's'
- strings are 3 letter names starting with a 'c' followed by a number '00', '01',, '02'. The number is given the first time the variable appears in any procedure
- if all non-English coders would stick to 'meaningful' names all monolingual English coders would see very long unmeaningful names. I don't like the hidden assumption that 'meaningful' applies to 'english' exclusively.
- I prefer the mathematical international conventions to abstain from 'meaningfulness' and to focus on exclusivity/enequivocality.

[Zack Barresse]

Hello snb,

Good to see you again, btw.

To your points [the variable names]...


- are inherently non-interfering with reserved VBA/Excel/Office terms
Moot point. This can be said of almost any naming convention.


- are systematic: first loop with j, first nested loop with jj, third nested loop with jjj, etc.
While the child loops are systematic in their naming convention, this is more about style than functionality, which speaks to my premise that naming variables meaningful names has far more of an advantage to this method.


- arrays are 2-letter variablels, always starting with an 's'
Same as above reasoning.


- strings are 3 letter names starting with a 'c' followed by a number '00', '01',, '02'. The number is given the first time the variable appears in any procedure
Same as above reasoning.


- if all non-English coders would stick to 'meaningful' names all monolingual English coders would see very long unmeaningful names. I don't like the hidden assumption that 'meaningful' applies to 'english' exclusively.
While you have a fair point, to me "meaningful" means just that - meaningful to the coder. If that is in English, or French, or German, or Russian, so be it. IMO it should all be in English as that is the language of most coding languages. It then behooves us to stay in-line with the language itself to purport meaning in our variable names.


- I prefer the mathematical international conventions to abstain from 'meaningfulness' and to focus on exclusivity/enequivocality.
Exclusivity, while it has its place, has no place in naming conventions. This is because a variable is simply a placeholder for something. That something needs meaning and is inherently exclusive (to itself, parent, child, whatever). The names we give it are the de facto exclusivity you desire, while the names themselves lead credence to maintainability/sustainability, debugging, maintenance, and generational iteration.

While it is clear your naming conventions are simplistic, they are not robust and difficult to decipher with anything longer than a handful of variables. I posit it's (overall) easier to code with meaningful names than a short schema, albeit derived from a place of meaning.

As always, your contributions are appreciated and I thank you for the conversation.

@Logit: Apologies for the thread hijack.

[Paul_Hossler]

Hijacking a little bit more, but I already know that I won't change anyone's mind :)

2/3 down the page the good stuff starts

https://www.joelonsoftware.com/2005/05/11/making-wrong-code-look-wrong/



Look for coding conventions that make wrong code look wrong. Getting the right information collocated all together in the same place on screen in your code lets you see certain types of problems and fix them right away.


The interesting part is "Apps Hungarian" notation which got bastardized into System Hungarian

We see things like this all the time:



Dim iNumLines as Long
Dim iLastCol as Long
Dim sMsg as String


(which is Systems Hungarian, the prefix "i", "s", etc.)

Silly since the compiler knows that iNumLines is a Long


Apps Hungarian would use



Dim cntNumLines as Long ' some kind of count
Dim colLast as Long ' a column number
Dim msgStatus as String ' a message


so that if I saw something like



colLast = cntNumLines


I'd at least look harder at it since it looks 'funny'

I try to use it as much as I can on 'big' projects

My 2 cents

[Zack Barresse]

Modern IDE's more or less negate the need for investigation. We no longer have a strong need for any type of Hungarian notation, for things, specifically to VBA, like CTRL+I, CTRL+J, Hover, CTRL+Hover, Locals/Watch Windows, and others. Modern IDE's are vastly more powerful and informative than they've ever been. Just take one look at Visual Studio Code.

The linked article is great. I've read it before and I'll probably read it again. The pointing out of Simonyi’s intent was very well articulated. In the end, if the system is knowable and easy to consume/maintain/debug, then the purpose is clear and job well done. Far better to name a variable iIndex or Index or hell, even a crazy lngIndex, as opposed to just plain ole i, which doesn't tell us squat. At least there's some context and reasoning to the naming convention.

[SamT]

Zack,

i and j, (and k and l, when used subsequently to i and j,) are conventional variable names as loop counters/indices

I don't believe that VBA has any need for Simonyi's convention as his intent was to make very long procedures self documenting. Procedures where the variable declaration might be 1000 lines above the usage instance and the variable's purpose needed to be known.

I try to follow Spolsky's advice to make wrong code look wrong, sometimes appearing like RPN. For instance: Given a Data Entry form, a Data Base sheet with Headers, an Enum, and the code for them. Example:
Form Label.Caption:=Header Name, Control.Name:=fcHeaderName, Enum Value Name:=colHeaderName


With Sheets("XXX").Rows(NextEmptyRow)
Cells(colHeaderName) = fcHeaderName
.
.
.
End With


IMO, Excel is probably Joel Spolsky's greatest contribution to Programmers and mankind in general.

[Paul_Hossler]

@SamT --



i and j, (and k and l, when used subsequently to i and j,) are conventional variable names as loop counters/indices



Agreed since the scope of i is very local and small in a For/Next.

j, jj, jjj, jjjj etc. would be hard to me to keep streight

In nested loops I frequently use a named loop variable



For iRow = 1 to 50
For iCol = 2 to 25

...
...






I don't believe that VBA has any need for Simonyi's convention as his intent was to make very long procedures self documenting. Procedures where the variable declaration might be 1000 lines above the usage instance and the variable's purpose needed to be known


I believe (MHO) that his intent was to help prevent errors by emphasizing the use of a variable as opposed to how it was declared. Any self documenting was a side benefit. So for example in VBA/Excel terms, the programmer/macro maker could easily tell that colStartData and colLastData were Longs used for column numbers, whereas i and i, or start and last, were not as 'obvious'

So code like




colLastData = rowLast + 1



would probably require a little more looking


Side note:

Comments should explain why something is being done, not what is being done, so the above might be correct, but the right comment will prevent a lot of head scratching 6 months later



'data is transposed and offset to the destination range
colLastData = rowFirst + 1



instead if



'add 1 to column number
colCurrent = colCurrent + 1


Still just my 2 cents

[Zack Barresse]

Good points all around. I think we mostly agree - "well written" code should be easy to consume, test, debug, etc. :thumb

[SamT]

:thumb:thumb

[snb]

'easy' is only between the ears of the beholder ....

[annonymous33]

And... do not complicate things unnecessarily in VBA:


2. use arrays as much as possible (for speed's sake)

'

I have just learnt about the "arrays". I lifted this code from somewhere else, and got it working. This code takes 40 mins to run. How am i able to include an array to reduce the time it takes?

Thanks for your help



Sub lngvol()
'You don't actually need to declare variables in VBA but I'll do it just to make life easier
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim DataImportCounter, RowCounter, ColumnCounter, ExShipCount, ImShipCount, LngSum, LastRowDataImport, LastRow As Integer
Dim ExportCountry, ImportCountry As String
Dim Exportdate As Date

LastRowDataImport = FindLastRow("REUTERS IMPORT")
LastRowSummary = FindLastRow2("SUMMARY DATA EXPORT VIEW")

For ColumnCounter = 10 To 33
Exportdate = Sheets("SUMMARY DATA EXPORT VIEW").Cells(1, ColumnCounter).Value
For RowCounter = 81 To LastRowSummary
ExShipCount = 0
ImShipCount = 0
LngSum = 0
ExportCountry = Sheets("SUMMARY DATA EXPORT VIEW").Cells(RowCounter, 1).Value
ImportCountry = Sheets("SUMMARY DATA EXPORT VIEW").Cells(RowCounter, 2).Value

For DataImportCounter = 1 To LastRowDataImport
If Sheets("REUTERS IMPORT").Cells(DataImportCounter, 8) = ExportCountry Then
If Sheets("REUTERS IMPORT").Cells(DataImportCounter, 10) = ImportCountry Then
If Exportdate = Sheets("REUTERS IMPORT").Cells(DataImportCounter, 16) Then
ExShipCount = ExShipCount + 1
LngSum = LngSum + Sheets("REUTERS IMPORT").Cells(DataImportCounter, 7).Value
End If
End If
End If
Next DataImportCounter

Sheets("SUMMARY DATA EXPORT VIEW").Cells(RowCounter, ColumnCounter) = ExShipCount
Sheets("SUMMARY DATA EXPORT VIEW").Cells(RowCounter, ColumnCounter + 39) = LngSum
Next RowCounter
Next ColumnCounter

'Num = Workbooks.Count
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


Function FindLastRow(ShtName) As Integer
'Goes through each cell from A1 through A50000. If the value in that cell is blank, it ends the function
'The last row is the one prior to the blank row

For X = 1 To 20000
If Sheets(ShtName).Cells(X, 1) = "" Then
Exit For
End If
Next X
FindLastRow = X - 1
End Function


Function FindLastRow2(ShtName) As Integer
'Goes through each cell from A1 through A50000. If the value in that cell is blank, it ends the function
'The last row is the one prior to the blank row
For X = 81 To 50000
If Sheets(ShtName).Cells(X, 1).Value = "x" Then
Exit For
End If
Next X
FindLastRow2 = X - 1
End Function

[snb]

Instead of:


Function FindLastRow(ShtName) As Integer
'Goes through each cell from A1 through A50000. If the value in that cell is blank, it ends the function
'The last row is the one prior to the blank row

For X = 1 To 20000
If Sheets(ShtName).Cells(X, 1) = "" Then
Exit For
End If
Next X
FindLastRow = X - 1
End Function

Use

Sub M_snb()
msgbox sheet1.cells(1).currentregion.rows.count
end sub
or applied to arrays:

Sub M_snb()
sn= sheet1.cells(1).currentregion
Msgbox Ubound(sn)
end sub


But I suspect you'd better use a pivottable in this case.
Post a sample workbook.

[SamT]

The Last Row functions you are using are Minor, but significant bottlenecks. Use these instead and see how big a difference they make


Function FindLastRow(ShtName) As Long
'Returns the Row number of the last used cell in column A

FindLastRow =Sheets(ShtName).Cells(Rows.Count, "A").End(xlUp).Row
End Function


Function FindLastRow2(ShtName) As Long
'Returns the Row number of the last used cell in column A

FindLastRow2 =Sheets(ShtName).Cells(Rows.Count, "A").End(xlUp).Row
End Function

Although it will only make a microscopic difference in speed, the following is a common mistake

Dim DataImportCounter, RowCounter, ColumnCounter, ExShipCount, ImShipCount, LngSum, LastRowDataImport, LastRow As Integer
Dim ExportCountry, ImportCountry As String
You have only declared LastRow and ImportCountry as specific Types; All other Variable therein are being declared as Variants.
All counters should be declared as Longs since Integers can only hold up to 32k.

There are two ways to declare a list of variables as specific Types

Dim DataImportCounter As Long, RowCounter As Long, ColumnCounter As Long,etc
And two styles of the second method

Dim DataImportCounter As Long
Dim RowCounter As Long
Dim ColumnCounter As Long
etc
And

Dim DataImportCounter As Long
Dim RowCounter As Long
Dim ColumnCounter As Long
etc

For the last bottleneck, still without using an Array. Note that this is only using one of the LastRow functions

Dim Reuters As Worksheet: Set Reuters = Sheets("REUTERS IMPORT")
Dim Summary As Worksheet: Set Summary = Sheets("SUMMARY DATA EXPORT VIEW")

For ColumnCounter = 10 To 33
With Summary
Exportdate = .Cells(1, ColumnCounter)
For RowCounter = 81 To FindLastRow(Summary.Name)
ExShipCount = 0
ImShipCount = 0
LngSum = 0
ExportCountry = .Cells(RowCounter, "A")
ImportCountry = .Cells(RowCounter, "B")

With Reuters
For DataImportCounter = 1 To FindLastRow(Reuters.Name)
With .Rows(DataImportCounter)
If .Cells(8) = ExportCountry And .Cells(10) = ImportCountry And .Cells(16) = Exportdate Then
ExShipCount = ExShipCount + 1
LngSum = LngSum + .Cells(7)
End If
End With
Next DataImportCounter
End With

.Cells(RowCounter, ColumnCounter) = ExShipCount
.Cells(RowCounter, ColumnCounter + 39) = LngSum
Next RowCounter
End With
Next ColumnCounter

[Zack Barresse]

Keep the find row function from the loop (as you last posted it). Otherwise it will calculate every iteration.

Take out the inner loop. You're only using it to calculate items which you can do with a function. In fact, at a cursory glance, you might be able to use functions to replace the whole bit.

Regarding your question about arrays here, you can use them in any place you 'touch' a Cell/Range object. Every time you access an object it loads that object into memory. The same can be said about placing values onto the grid - stuff them into an array and then touch the grid once when placing. Since you're not using ranges much here I doubt it'll add too much speed to your routine. Removing the loops would help with speed here.