Beginner's Guide: Learning VBA Excel Step by Step - Tips, Examples & Department Transition

Good morning everyone.

Sorry to renew a topic that has been rewritten many times but is very important to me. I would like to learn vba in Excel.
Probably one of you asks why this for me? I was moved from one department to another, where I work a lot in Excel. I have no problem with the commands. However, in the field of VBA I am totally green.

I have read many pages on this subject. The more I read, the less I understand. Strange should be the opposite. For me it's black magic. I don't know where to start? I realize that I won't teach quickly, but I don't know where to start. I don't know if I'm so dumb !?

I will write as if I saw my teaching, which does not mean that it must be so!

A specific example with a macro, described step by step why the code was constructed in this way. Preferably many examples with a description.

I would not want to teach the code by heart, it makes no sense to me, just understand why it works.

I don't even know where to reach a list of all formulas, variables.
What some people have fun at me, but probably there is no such thing or I can not find.

In practice, I met with two ways to learn programming. Other issues are also solved by such methods.

- "Textbook".
You need to find a textbook that suits your personality, which is an art in itself. Reading the description of the language, you can perform practically the examples in order. That's how I learned (chronologically): Fortran, BASIC, ECDL, HTML + CSS (basics), Visual Basic Script and Java Script (also basics). Then I tried to write my own programs using the acquired knowledge and ready examples.

- "Deep water".
One immediately tries to write a program "taken from life", initially knowing the environment very cursory. That's how I did Linux shell scripts.

I am currently using a mixture of both methods with variable proportions of individual components.

When it comes to VBA, I have no experience. Due to some ad hoc need, I bought a tiny Excel object function dictionary, wrote an easy one, then forgot about it and I don't do it to this day, because there was more demand for ease in MS Access.

My writing may not be useful to you, but I am in line with the current rule "I do not know, so I will speak."

Krzysztof Podstawa

There are plenty of such courses on the web, you just have to read and see if it suits you e.g. http://www.mielk.pl/pl/kursy/kurs_vba.php

leonov wrote:

There are plenty of such courses on the web, you just have to read and see if it suits you e.g. http://www.mielk.pl/pl/kursy/kurs_vba.php

I read many times and I still know nothing.

Not looking far, I pasted a friend's JRV macro and here the link link


I guess r is variable, but why is equal to 1, but I don't understand this code content: With Sheets ("report")
While .Cells (r, 1) ""
rb = Application.Match (.Cells (r, 1), Sheets ("base"). Columns (1), 0)
Cells (r, 2). Resize (, 5). Copy Sheets ("base"). Cells (rb, 2).
r = r + 1
Wend
End with

Every next line is not understood by me.

Without 'With Sheets ("report")'


Added after 1 [minutes]:

kasia188 wrote:

Every next line is not understood by me

cursor on the word and press F1

1. Start, however, with reading, books - it comes out cheaper, and then courses if you have ... here a few items:
https://www.elektroda.pl/rtvforum/topic3389070.html

2. A friend's macro JRV :

kasia188 wrote:

r is variable, but why is equal to 1

a) r = 1, output / start value of the row variable - you have to start counting something off something

b)

kasia188 wrote:

With Sheets ("report") ... End With

Shortening the record so as not to write the "address" of the object every time

c)

kasia188 wrote:

rb = Application.Match (.Cells (r, 1), Sheets ("base"). Columns (1), 0)

Variable storing the result of the function 'Enter.item', for which the arguments are:
- .Cells (r, 1) => (abbreviation, without the '.Value' property) sought value of the next cell from column 1, i.e. 'A'
- Sheets ("base"). Columns (1) => in the range of column 1, i.e. 'A' from the sheet 'base'
- 0 => exact search (as opposed to approximate), sorting irrelevant

d)

kasia188 wrote:

Cells (r, 2). Resize (, 5). Copy Sheets ("base"). Cells (rb, 2).

This means: "Starting from the cell of column 'B' (No. 2) from row 'r', set a new range to copy ('.Resize') with a size of 1 row and 5 columns (Resize (, 5), a shortcut without specifying row (s), if it is the same), i.e. from column 'B' to 'F' inclusive. Copy this range to the 'base' tab, to the cell in column 'B' (No. 2) and row 'rb', where ' rb 'is the result of the' Pass.item 'function, which is a number value.

e)

kasia188 wrote:

r = r + 1

Increase the row counter by 1

f) Everything above is done in a loop, such as:

kasia188 wrote:

While .Cells (r, 1) ""
Wend

in the range of r = 1, until the value of the cell encountered is "", i.e. until there are some cells filled in column 'A' (No. 1).

Edition:
And I got into the JRV parade ... ...

gentlemen JRV and lanzul thanks for explaining the code. I did not expect such a specific explanation. I don't know if I will cover this topic. He feels like an elephant in a china shop or worse, as a boxer in gloves, sent to operate on a patient with a heart disease. Colleagues, how did you get it? Full respect . The worst thing is that I don't know what 1 step to take.

Added after 1 [hours] 52 [minutes]:

Julitta Korol - "Excel 5.0 next steps"
Julitta Korol - "Visual Basic for Excel applications"
Łukasz Tatarkiewicz - "Excel 5.0 Visual Basic and functions"

My friend Lanzul Which of these books would you recommend the most?

kasia188 wrote:

The worst thing is that I don't know what 1 step to take.

First step - in my opinion - open some xlsm sheet and press Alt + F11 keys . This will open the MVB editor vba.JPG Download (97.52 kB)

and here you need to spend some time to learn how to fully use this basic tool for creating and testing VBA code

In a very "light" form about VBA scripts you can read and learn a lot based on detailed explanations from Microsoft TechNet, e.g. link

kasia188 wrote:

what 1 step to do

Begin with the books, those who describe most ... and record activities in the macro recorder, and then organize them, arrange, shorten ... dry "knowledge" is not enough.

kasia188 wrote:

which of these books

All three in the same order as above (now they are probably already "newer" xls). But all in all it doesn't matter that they describe macros in xls 5.0, the point is to get started, and there it is very clearly described, understandably ... I think so ... but I can be wrong ...: / .. .
Helion's books are also good, Nakom publishing books were once very popular and accessible (I don't know if it still exists) ... "long ago" some colleges sold their own studies in the form of scripts for students, you can check how it looks now ... now it should be easier as it used to be ... dude was inakaj ...