Language translation formula (Excel)

Excel formula that can translate From any language To any language.
Example: getGoogleTranslation("Do you speak English?";"en";"sv")

However this code has one limitation; it can only translate one sentence.


--
Public Function getGoogleTranslation(strSource As String, strSourceLang As String, strDestLang As String) As String
    Dim strURL As String, x As String

    strURL = "http://translate.google.com/translate_a/t?client=t&text=" & _
             Replace(strSource, " ", "%20") & _
             "&hl=en&sl=" & strSourceLang & _
             "&tl=" & strDestLang & "&multires=1&pc=0&rom=1&sc=1"

    With CreateObject("msxml2.xmlhttp")
        .Open "get", strURL, False
        .send
        x = .responseText
    End With

    getGoogleTranslation = Replace(Replace(Split(x, Chr(34) & "," & Chr(34))(0), "[", ""), """", "")

End Function
--

Source: link

Extract hyperlink formula

Example:
=HLINK(A1:A10;FALSE)

Function HLink(rng As Range, Optional RtnSub As Boolean) As String
 If rng(1).Hyperlinks.Count Then
    If RtnSub = False Then
       HLink = rng.Hyperlinks(1).Address
        Else
       HLink = rng.Hyperlinks(1).SubAddress
    End If
 End If
End Function

Source: link, subaddress property

Picture into Excel from imageurl

Dim url_column As Range
Dim image_column As Range

Set url_column = Worksheets(1).UsedRange.Columns("A")
Set image_column = Worksheets(1).UsedRange.Columns("B")

Dim i As Long
For i = 1 To url_column.Cells.Count

  With image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value)
    .Left = image_column.Cells(i).Left
    .Top = image_column.Cells(i).Top
    image_column.Cells(i).EntireRow.RowHeight = .Height
  End With

Next

Reference: link

Prime Number

Function to create prime number
Syntax =PRIME(Starting Number, Ending Number)

Private Function PRIME(St, En As Long)
Dim Ans As String
For x = St To En
    For y = 2 To x - 1
        If x Mod y = 0 Then GoTo 20:
    Next y
    Ans = Ans & x & ","
20:
Next x
PRIME = Ans
End Function

Link: ExcelForum

Update to verify a Prime:
=AND(MOD(A2;ROW(INDIRECT("2:"&INT(SQRT(A2))))))
Link: Chandoo.org

VBA Basics

How to select Cells

Range("A1:A10").Activate
Range("A1:A10").Select
Range("A1").Activate
Cells(10,1).Select
Range(Cells(1, 1), Cells(10, 1)).Select
[A1:A10].Select

Note:
Activate and Select is the same thing (as far as I understand)
Cells(10,1) is the same as Range("A10") and is only cell.
[] is the same as Range. Notice the range is without " ".


Write to a Cells

Cells(1,1).Value=10
Range("A2:A10").Value = 10
Range("A11").Formula = "=SUM(A1:A10)"

Selected cells
Selection.Value = 10
ActiveCell.Formula = "=SUM(A1:A10)"


Copy/paste
Range("A12").Value = Range("A11").Value
Range("A1:A11").Copy Range("B1")
Range("Sheet1!A1:A11").Copy Range("Sheet2!A1")


Sub EndOfRangeCopy()
Range("A1").Activate

Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select ' Rows.Count = 65536

Range("A1", ActiveCell.Address).Copy

End Sub



Help pages
Press F2. Search for a formula. We are now in something called the Object Browser (box icon)
Mark the found formula. Press F1 to find the help page, or right click and select help.
Close Search page (Object browser) by right click and select hide --> get you back to your module.
New finding: Double click on the word in code and press F1 will go directly to the help, or F2 to object browser.

Alt+F11 will toggle between VBA window and Excel window. In the beginning it can be a good idea to be working with 2 window halves, side by side.


Procedures: Function and Sub
Functions are used to write formula (my formulas) or UDF's=UserDefinedFuntions.
Sub's or Subroutines are everything else.

Functions pass values in and out:

Function ValueOut(ValueIn)
ValueOut = ValueIn*2
End Function

=ValueOut(100) --> 200


Where do I put the code?
To the left there is a list that looks like Windows Explorer. It is called Project Explorer (it should be called the VBA explorer if you ask me). You can reach it from the Flying windows button.
There is Module1
- if not you can add it by selecting from the menu: Insert Module or the Icon with the glare (it has 4 alternatives)
There is Sheet1...to x
There is ThisWorkbook

Normally all codes goes in Module1...to x (like sheet1...to x)
You can put code in sheet1 if you only (simply said) want it to run there.


How to get started
Best way is copy code from somewhere and test, test and test. It's like learning to ride a bike.
You can not learn to ride a bike by watching someone doing it, or reading about it or looking at a video. You have to do it yourself. No way around that - unless you are an exception and learned to swim that way.

For instance copy the code above/below and learn what works and what don't.
My way is to writing about it. Trying to teach someone.


How do I avoid using static ranges?
One way is to Name a range, in Excel.
That will let Excel take care of the problem.

Static: Range("A1:A10")
Dynamic: Range("myRange")
We have defined myRange in Excel as A1:A10


How to change the Cell Properties?
How to change Background color and font (two of many properties). In Excel you would say you want to 'Format Cells'.

Sub Changeproperties()
Range("A11").Interior.ColorIndex = 5
Cells(11, 1).Font.Name = "Courier"
End Sub

Note: Cells() don't have property ColorIndex. Range() do.

Sub Changeproperties()
With Cells(11, 1).Font
.Name = "Courier"
.Bold = True
End Sub

Sub Changeproperties()
Range("A11").Select
With Selection.Borders(xlEdgeTop)
' or Borderaround
.ColorIndex = xlAutomatic
.LineStyle = xlContinuous
.Weight = xlThin
End With
End Sub


Write to cells
Fill all selected cells

Sub FillSequence()
Dim cell As Range
Dim i As Long
For Each cell In Selection
i = i + 1
cell.Value = i
Next cell
End Sub


VBA Shortcuts: :
Alt+F11, to get to the Visual Basic Editor
Ctrl+R, to bring up the Project Window (should be on leftside)
F7, brings up the Code
Ctrl+G, brings up the Immediate window


Stand on the sub name and
- press F5 to run you code
- press F8 to stepwise go through it you code





Excel shortcuts:

Alt+F8 (program function key 8) [Picture Alt+F8 -- Macro]

The F8 -- macro list does not show macros that have arguments, nor will you see User Defined Functions. You can however call a those macros with another macro, without (...) arguments.

The Function macros (UDF's) can be found under the fx button. All UDF's are found under the Category.


Your personal.xls file is where you store your own macros and User Defined Functions (UDF), that you want to be available from any workbook. For macros you only want available in a single workbook you would install macros in that workbook.




Intermediate Window
Can be used to test code.
Ctrl+G, brings up the Immediate window


Write for instance
?Cells(14,1).SpecialCells(xlLastCell).row --> reply 22


Camera tool

Sub ChangeShading()
With ActiveSheet.DrawingObjects("Picture 1")
For intWrk = 1 To 14
.Formula = "A" & intWrk
Application.Wait Now() + 0.0000007
Next intWrk
End With
End Sub

Note: "Picture 1" refers to the name of the Camera tool picture


Sources: David McRitchie (Join, Get Started), Xl-logic, Lacher

What is ByVal & ByRef ?

Aha, now I get it.
ByVal allows a variable value to pass in but not out - in the same name.
In the example below. X comes in to Sub Add2 as 2 and is used inside as 2 (X=X+2). X= 2+2 = 4, but X never leaves Add2 as 4. I came to think of it. If you don't use ByVal you could accidentally change the same variable outside. It is quite likely you have several Sub's running, with the same variable names (i, x, ...).


Post
ByVal means To Pass By Value, meaning you you pass something ByVal you are passing the Value of the Variable not a Pointer to the Variable itself, ie.

Code:

Private Sub Command1_Click()
Dim iTemp As Integer
iTemp = 2
Call Add2(iTemp)
Debug.Print iTemp
End Sub

Private Sub Add2(ByVal X As Integer)
X = X + 2
End Sub

Calling this code would always Print "2" in the Debug Window.If you modify the Add2 Routine to be ByReference, it would Print "4".


Article
Did you know that you can pass a parameter to a Sub or Function in two different ways? You can either do it ByVal (By Value) or ByRef (By Reference). So what's the difference?
Well when you pass a variable ByVal a new instance of the variable is created and given to the routine being called. Any changes made to the value of this variable have no effect on the value of the original variable that was passed in.
If you instead pass in a variable ByRef, any changes you make to this variable also change it's value outside the routine. (Note: This is often considered bad practice because it makes code difficult to follow and even harder to understand.)
So you want an example huh? Take a look at the following piece of code.

---------
Dim iFirst, iSecond
iFirst = 1
iSecond = 2
' Call our Sub.
SwapValues iFirst, iSecond

' Both values are now 1!

Response.Write "First: " & iFirst &
Response.Write "Second: " & iSecond &

------------
Sub SwapValues(ByVal iFirst, ByRef iSecond)
Dim iTemp
iTemp = iFirst
iFirst = iSecond
iSecond = iTemp
End Sub
------------

' iSecond was changed because it was passed ByRef
' while iFirst was not since it was passed ByVal.
' In order to actually swap the values you'd need
' to make both parameters ByRef.

So which should you use? Well that depends! (How did you know I would say that?) The default is ByRef and for the most part that makes sense. Why use the additional memory to make a copy of the variable? When you're passing variables to components however, ByVal should really be the method of choice. It's much easier to just give the component a copy of the value then to have it need to worry about sending back any changes it might make to the variable. Imagine if this component was on a different computer and the point becomes clear pretty quickly. Every time a change was made to the value, the component would have to go back to the first computer to change the value!

UDF's (MyFormulas)

Tip:

AndrewsExcelTip.net has a Excel file full of simple UDF's. It is great for learning VBA.

For Common Forums questions: Toolbox of ExcelFormulas & UDF's

VBA Lesson 2 - Sort & UniqueFilter

Second lesson has been to learn how to Sort a range and to take that sorted range and Filter out only the Unique values (text and numbers).

I still struggle with ranges. I don't yet have a clear grip on how to handle them.
For instance if I have a range r1 = Range("B1:B10"), how can assign r2 = Range("B1").first cell ?


I just found this: Remove Duplicates

VBA Lesson 1 - Cell Color

This is my first attempt to learn VBA.
I' struggling with 4 books and a forum.

My first lesson has been to read InteriorColor, and handle Ranges.