What programming language is Excel? How to write a macro in Excel using the VBA programming language

Did you know that Excel has a tool for translating text into another language. With Mini Translator you can translate a phrase, paragraph or entire file written in another language. This item uses functionality Microsoft programs Translator, which is included by default in office Microsoft applications. In today's article I will describe how to use this functionality. In addition, we will look at how to use the WEBSERVICE and FILTER.XML functions to implement Yandex translator in our Excel workbook.

Translation using Microsoft Translator

So, you have a text that needs to be translated into another language. In our case, I want to translate the text located in cells A1:A3 from Russian to English. Go to the tab Review to the group Language, click on the button Translation. A panel will appear on the right side of the book Reference materials, in which it is necessary to indicate the text to be translated and the source languages and into which to translate.

There are two ways to specify the text to be translated. The first, banal one, we type the text into the field with pens. Search, indicate the translation language, click the arrow button to the left of the field, wait a couple of seconds while Microsoft Translator selects the right words and enjoy the result of the translation in the same panel.

The second option is more technologically advanced and assumes that the text that needs to be translated is already on the sheet. To do this, hold down the Alt key and click on the cell with the text to be translated in the field Search panels Reference materials you will see the phrase from the selected cell, and below it is its translation. If you press the button Insert, located under the translation, Excel will replace original text in the cell to translated.

This tool is easy to use and especially helpful if you come across unfamiliar words in an Excel workbook.

Implementation of Yandex translator in Excel

The method described above does not require any special Excel skills; you just need to use it once and everything will become clear. The method described below, more complicated and even more interesting, will require some skill from users.

So, as I already wrote in the article about, Excel 2013 implements a tool for importing data into XML format, using the function . The first function uses the web service API to download data from the Internet, the second one returns specific information from the downloaded XML. To implement our idea in Excel, we will use the Yandex translator, which has its own API. Go to the Yandex Translation API page, where you can read all the documentation and rules for formatting translation results, find the item Get freeAPI-key and click on it (ATTENTION! To receive the key, you must have a Yandex profile).

Next, you need to fill out the form for obtaining an API key, where you need to indicate which service you need the key from, read Terms of use, check the read box and press the button GetAPI-key. It looks like this.

This key will be used as one of the required arguments in the URL request sent to Yandex services. The next required argument is the text that needs to be translated and the third argument is the translation direction, which can be specified as a pair of language codes (for example, ru-en), or as a target language code (for example, ru). You can read more about the request URL syntax on the Yandex website.

Thus, you will have two mandatory arguments that are practically unchanged - the key and the translation language, and the translation text will be a variable argument.

An important point that you should pay attention to is that before sending a request to Yandex, you need to change the encoding of the request text to a language understandable for the web service. To do this, you need to use the ECODE.URL function, which returns a string encrypted as a URL. For example, text Samara will look like %D0%A1%D0%B0%D0%BC%D0%B0%D1%80%D0%B0.

Having added a little gloss to our file, our translator took on a completely readable form.

To write computer program Firstly, you need to know some programming language. Secondly, you must have installed on your computer special program, which will compile the code you write. Thirdly, you will need to develop and write down on paper the algorithm of your program so that there is always a clear goal in front of your eyes of what we are going to (at least, this is what I always do when writing more complex programs).

Programming language Any will do, but when working in Excel and writing programs for it, I recommend using Visual Basic for Application (VBA), since Excel already has a built-in special editor for creating programs in Excel.

I think you and I can easily master the programming language, I succeeded, and you can do it too. Believe me, programming in Excel is much easier than in some C++ or java. Because it is created for everyone, no matter if you are an engineer or an accountant, VBA was created for people who use Excel. And if you have mastered Excel, then the next logical step is to master VBA.

I will show you with an example how a program is created in VBA.

We are not inventing any algorithm; instead, we will write the following phrase ““Our code”.

1. Launch Excel.

2. Execute the blitz command (hold down two keys) “Alt” and “F11”.

3. Create a template and save it.

4. Then we write down three lines, and the line that begins with an apostrophe is a comment. The word "Sub" indicates the beginning of the program, "program()" is the name of the program, which can be changed, for example, to "Macro()".

The phrase “End Sub” indicates that the program ends here. Instead of the line ““Our code”, you can write our algorithm in the future.

Programming in Excel is not as difficult as it might seem, you need to have a clear algorithm, preferably written down on paper, which we will soon learn how to develop, and some VBA basics (input and output of data into the program, conditions and loops), this will be enough to get started .

Problem statement and preliminary settings

So that we can use R functions from VBA must be opened in Excel "Visual Basic editor" (Alt+F11). After this, you need to add a module to the project RExcelVBAlib, you can do this by going to Tools->References and put a tick on the desired item.

This module contains the class rinterface, through which the interaction of the components of our bundle occurs.
For demonstration, I took the initial data on dollar quotes from the Finam website for the period 12/16/2011 to 03/20/2014. For example, based on them, using R, we will construct a graph of changes in the monthly opening price ( "OPEN").

Basics of working with rinterface from RExcel

First, let's write a code in R that will perform the task and save it to a file, for example agg_price.R (we will need it later).

Library(zoo) agg_price_func<- function(x) { y <- zoo(x$OPEN, as.Date(as.character(x$DATE),"%Y%m%d")) new_y <- aggregate(y, as.yearmon, mean) plot(new_y) return (new_y) }

Let's figure out what this code does.
First, we load the zoo library, which we will need to work with time series.
Then we create a function that does the following:

Converts our dataset into a time series. The indexes of which will be the column values "Date", converted to date. The values of the levels of this row will be equal to the column " OPEN".
With the following line, we aggregate our data by month using the aggregate function. This step is needed because our source data contains daily data, but we need to move on to months.
display a graph based on monthly values
we return an array with monthly values on the basis of which the graph was built.

So, we wrote the code in R. Now let's see how to call it from VBA.
There are several ways to do this, which will be shown below.

Method 1. Line-by-line execution of function commands.

This method is the easiest to understand and the longest in terms of the number of lines of code. The procedure code for it is as follows:

Sub call_r_func() RInterface.PutDataframe "open_price", Range("USD!A1:C535") RInterface.RRun "library(zoo)" RInterface.RRun "price<- zoo(open_price$OPEN, as.Date(as.character(open_price$DATE),""%Y%m%d""))" RInterface.RRun "agg_price <- aggregate(price, as.yearmon, mean)" RInterface.RRun "plot(agg_price)" RInterface.InsertCurrentRPlot Range("OPEN_PRICE!A1"), widthrescale:=0.5, heightrescale:=0.5, closergraph:=True End Sub

As you can see in this procedure, 3 functions from rinterface:

PutDataframe
InsertCurrentRPlot

The PutDataframe function is used to load data into an R language variable of type dataframe. As the first parameter, it is passed the name of the future variable that the interface will use. The second parameter will be the range of values that the variable will contain.
RRun is used to execute a command from the R interpreter. As a parameter, it is passed the string that will be executed by it.
The last one in the list, InsertCurrentRPlot, outputs R graphics to a given MS Excel sheet. As the first parameter, it is given the cell in which the graph will be displayed. Options widthrescale And heightrescale are used to scale the output graph (in our case, 50%). closergraph signals that the graph produced using the plot() function needs to be closed.
After the functions described above, the operation of the procedure call_r_func() should not raise any questions. In short, this procedure simply executed the R script described above line by line.
The advantage of this method is that all code is concentrated in VBA macro , which can be convenient for small tasks.
The disadvantage may be inconvenient debugging of R code.

Method 2. Using an external function to display a graph.

The procedure for this method is as follows:

Sub call_r_impotr_func_without_print() RInterface.RunRFile "D:/agg_price.R" RInterface.RunRCall "agg_price_func", AsSimpleDF(Range("USD!A1:C535")) RInterface.InsertCurrentRPlot Range("OPEN_PRICE!H1"), widthrescale:= 0.5, heightrescale:=0.5, closergraph:=True End Sub

There are 3 new functions in this code:

RunRFile
AsSimpleDF
RunRCall

The first in this list, RunRFile, allows you to execute the code located in the file .r
AsSimpleDF converts the range selected in the parameter to a dataset type ( dataframe).
The RunRCall function calls a procedure and does not return its result ( return (...) ignored). The name of the procedure or its code in R is passed as the first parameter. All subsequent parameters pass the parameters defined in the procedure (in our case there is only one).
From the description above, it becomes clear that our procedure call_r_impotr_func_without_print() executes external file first agg_price.R. Then it calls the function agg_price_func for a set of data from a given range. And at the end it displays the graph on the given sheet.
The advantage of this method is ease of debugging code in R , because it can be written in any editor or IDE.
Of the minuses, it should be noted that at the output, instead of one file, we will get 2: xls and r.

Method 3. Using an external function with data output and graph.

Procedure code:

Sub call_r_impotr_func_with_print() RInterface.RunRFile "D:/agg_price.R" RInterface.GetRAApply "agg_price_func", Range("OPEN_PRICE!A19"), AsSimpleDF(Range("USD!A1:C535")) RInterface.InsertCurrentRPlot Range(" OPEN_PRICE!D19"), closergraph:=True End Sub

This procedure has only one small difference from the previous one, namely, instead of
RunRCall is called by GetRApply.
The difference between this function is that it can return a result ( return (...) is not ignored), and not just execute some code. Although for those who are familiar with the definition of procedure and function, this difference is clear.
In addition, in GetRAApply, after the parameter with the name of the function, you also need to specify the range of cells where the values resulting from the function will be displayed.
The pros and cons of this approach are the same as the previous one.

Conclusion

After executing these VBA procedures, our test sheet looks like this:

In the article I tried to show the most useful options for executing the code R from VBA. In addition, the advantages and disadvantages of all these methods were described in a concise form. It should also be said that RExcel can work without VBA.
It should be noted that not all interface functions are shown. rinterface, but this material will make it possible to master undescribed functions with minimal time.
The file with the procedures can be obtained from me on GitHub.

Tags: Add tags

After installing the add-on, a new feature will appear =TRANSFER(TEXT;LANGUAGE). The function has only 2 arguments:

TEXT- The actual text that needs to be translated. The maximum text length is 10,000 characters
LANGUAGE- Direction for translation. Can be set in one of the following ways:

In the form of a pair of language codes (“from which” - “to which”), separated by a hyphen. For example, "en-ru" stands for translation from English into Russian.
As a final language code ( for example "ru"). In this case, the Yandex.Translation service tries to determine the source language automatically.

After installing the add-on, the function can be used in the same way as the built-in one. This is what the standard window for entering parameters for the TRANSLATION function looks like. The following are various examples of using translation functions.

Example 1

As already mentioned, the LANGUAGE variable can be set in two ways. The easiest way is to specify the target language code. In this case, Yandex itself will try to determine the source language.

Example 2

A more complex way is to explicitly specify the source language and the language into which you need to translate.

Comparing translation results with and without explicit indication of the translation source. They seemed identical to me. The speed of the function has not changed either.

Simplified translation functions

As a rule, we use two languages: Russian and English. To make life easier for myself and my users, I made 4 more types of functions:

=TranslationRu(TEXT) - Translates text from almost any language into Russian using the Yandex.Translation service (no need to specify the source language)

Example

Example 3

=TranslationEn(TEXT) - Translates text from any language into English using the Yandex.Translation service (no need to specify the source language).

Example

Example 4

You only need to specify the text you want to translate. The Yandex.Translation service will automatically try to determine the source language of the text.

=TranslationEnRu(TEXT) - Translates text from English into Russian using the Yandex.Translation service.

Example

Example 5

A simplified function for translating from English into Russian. You only need to specify the text you want to translate.

=TranslationRuEn(TEXT) - Translates text from Russian into English using the Yandex.Translation service.

Example

Example 6

A simplified function for translating from Russian into English. You only need to specify the text you want to translate.

Language		Code
Albanian		sq
English		en
Arab		ar
Armenian		hy
Azerbaijani		az
Belorussian		be
Bulgarian		bg
Bosnian		bs
Vietnamese		vi
Hungarian		hu
Dutch		nl
Greek		el
Georgian		ka
Danish		da
Hebrew		he
Indonesian		id
Italian		it
Icelandic		is
Spanish		es
Catalan		ca
Chinese		zh
Korean		ko
Latvian		lv
Lithuanian		lt
Malay		ms
Maltese		mt
Macedonian		mk
German		de
Norwegian		no
Polish		pl
Portuguese		pt
Romanian		ro
Russian		ru
Serbian		sr
Slovak		sk
Slovenian		sl
Thai		th
Turkish		tr
Ukrainian		uk
Finnish		fi
French		fr
Croatian		hr
Czech		cs
Swedish		sv
Estonian		et
Japanese		ja

Transfer restrictions

Unfortunately, Yandex allows you to translate no more than 1 million characters for free per day. Due to the growth of users, this volume began to be achieved quite often. At 00:00 Moscow time, the restriction will be lifted and the function will work again. If the volume is exceeded, the function returns the value "Text cannot be translated."