Pass a cell value from one macro to another one

[jbesclapez]

Hello Mr Excel,

I am trying to find some info on how to pass a cell value from one macro to another one.
The cell value of Range("A2") in Macro1 as to be used in a Macro2...

Obviously I googled and this but, I can not find anything simple like to understand.

If you can send me a macro with those example, I will then adapt it to my need.

Thanks for your help!

 

[dmt32]

Hi,
one way would be to give Marco2 a Range parameter and pass the range as an argument from Macro1 to it

something like following

Sub Marco1()
    Macro2 Range("A2")
End Sub

Sub Macro2(ByVal Target As Range)

    MsgBox Target.Value
End Sub

Dave

 

[Joe4]

See this here: Excel VBA Programming - Passing values to a Subroutine

However, in this case, can't Macro2 just get the value directly from Range("A2")?

 

[Fluff]

How about

Sub jbesclapez()
   Dim Txt As String
   
   Txt = Range("A2").Value
   Call Test2(Txt)
End Sub
Sub Test2(Something As String)
   MsgBox Something
End Sub

 

[Joe4]

dmt, I would advise you NOT to use "Target" as a variable in your procedure. It is strongly recommended that you not use Reserved words (name of functions, methods, etc) as the names of variables, procedures, or functions. Doing so may cause unexpected results and errors (as Excel cannot tell if you are referring to the innate names it uses, or one of your custom ones).

Target is a reserved word, used in Event Procedures in Excel.

 

[dmt32]

dmt, I would advise you NOT to use "Target" as a variable in your procedure. It is strongly recommended that you not use Reserved words (name of functions, methods, etc) as the names of variables, procedures, or functions. Doing so may cause unexpected results and errors (as Excel cannot tell if you are referring to the innate names it uses, or one of your custom ones).

Target is a reserved word, used in Event Procedures in Excel.

Really Joe4 - been using it for years without any problems - I would be interested if you can direct me to where it states Target is listed a reserved word that excludes its use in local code?

My understanding is that these are the Reserved or key words used by the compiler -

VBA Syntax - Keywords

description

bettersolutions.com

Target is not listed
Dave

 

[jbesclapez]

Waoh!!! So many replies. Please let me take time to catch up with you guys!
I started working on a script from my own research and I realised that actually it is 2 variables I need to pass. Sorry.

Here is the state I am in BEFORE reading all your answers... I will adjust it later obviously as I have not even tried it.

Sub GetDimProperties()

Dim i As Integer
Dim LastColumn As Integer
Dim DimName As String
Dim DimType As String

LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

If Cells(1, i).Value = "type" Then Cells(2, i) = DimType
Else
End If

If Cells(1, i).Value = "name" Then Cells(2, i) = DimName
Else
End If

AddDeleteXML (DimType)
AddDeleteXML (DimName)

End Sub

And the other macro where i will pass the value

Sub AddDeleteXML()
Dim LastRowA As Integer

Set sht = ActiveSheet
LastRowA = sht.Cells(sht.Rows.Count, "A").End(xlUp).row

    Range("D1").FormulaR1C1 = _
        "=""<member name=""&""""""""&RC[-3]&""""""""&"" action=""&""""""""&""Delete""&""""""""&"" />"""
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D" & LastRowA), Type:=xlFillDefault
    Range("D1").ClearContents
    
End Sub

 

[Joe4]

Really Joe4 - been using it for years without any problems - I would be interested if you can direct me to where it states Target is listed a reserved word that excludes its use in local code?

OK, I may have misspoke in that it is not an official "Reserved keyword". Still, it is the variable that Excel uses in some of their Event Procedure code:

Private Sub Worksheet_Change(ByVal Target As Range)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

You could get into trouble, say if you decide to try to make "Target" a global variable in a module with Event Procedures.

When advising users fairly new to VBA, I think it is best practice to try to avoid all the different terms that Excel uses to avoid any issues.
Why tempt fate, when there are literally and endless number of different names you could use?

 

[dmt32]

I'll take that as an apology -
I always try to avoid global variables preferring to pass values / objects to any called procedure but I accept others like to use them.
All programmers have their preferred style & what they consider best practice as clearly you do but I would not comment on their style or approach.
- Target is a common parameter name used when passing a range - I have have found overtime OPs understand its meaning & use so stick with it.
Its a choice I make some will agree, many others may not but its all part of the richness of people contributing to a problem - seeing how others would do it.

Dave

 

[Joe4]

All programmers have their preferred style & what they consider best practice as clearly you do but I would not comment on their style or approach.
- Target is a common parameter name used when passing a range - I have have found overtime OPs understand its meaning & use so stick with it.
Its a choice I make some will agree, many others may not but its all part of the richness of people contributing to a problem - seeing how others would do it.

Agreed.

Keeping straight what is reserved and what isn't it not that easy. I usually like to error on the side of caution, and if I know Excel uses it somehow, I will avoid it.
I have helped people before who had issues because they accidentally chose reserved words (mostly, choosing to name procedures or functions the same name as an existing Excel function).

I would advise noobies that if they aren't sure if a word is reserved or not, one tactic that I have saw a long time ago and still use often is to preface the name with the word "My" (i.e. MyTarget).
Do that, and you will never go wrong.