Ufdjrw

How to avoid supervisors with prejudiced views?

Point distance program written without a framework

What happened in Rome, when the western empire "fell"?

Which one is the true statement?

Where do students learn to solve polynomial equations these days?

Computationally populating tables with probability data

Defamation due to breach of confidentiality

It is correct to match light sources with the same color temperature?

Is fine stranded wire ok for main supply line?

Yu-Gi-Oh cards in Python 3

Help! I cannot understand this game’s notations!

Cannot shrink btrfs filesystem although there is still data and metadata space left : ERROR: unable to resize '/home': No space left on device

Strange use of "whether ... than ..." in official text

What is the process for purifying your home if you believe it may have been previously used for pagan worship?

What day is it again?

What CSS properties can the br tag have?

How do I fit a non linear curve?

Audio Conversion With ADS1243

Is there a difference between "Fahrstuhl" and "Aufzug"?

Getting Stale Gas Out of a Gas Tank w/out Dropping the Tank

Expressing the idea of having a very busy time

Is it ever safe to open a suspicious HTML file (e.g. email attachment)?

Help/tips for a first time writer?

Is a distribution that is normal, but highly skewed, considered Gaussian?

Passing two string arguments to Excel macro from button

0

I wrote a macro that originally had 0 arguments, and called the macro from a button on my Excel sheet. The design changed, and now I added two optional arguments to the macro, like this:

Function ButtonClick(Optional prop1 As String, Optional prop2 As String)
 `Do stuff here
End Function

I am now trying to pass both arguments to the macro from the button, but get an error each time.

First I tried:

Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))

as was suggested in this question, but got an error for That function isn't valid.

I noticed that in the question, the top answer also called for surrounding the entire macro name in single quotes, so I did so:

'Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))'

And this time got there error Formula is too complex to be assigned to an object.

What am I doing wrong?

excel vba

share|improve this question

edited Mar 8 at 7:16

Instant Breakfast

94211227

asked Mar 7 at 18:30

pavuxunpavuxun

1167

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Why have ButtonClick be a function rather than a sub? You are calling it as a sub (so using the outer parentheses in Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4")) is a syntax error, it should be Sheet1.xlsm!ButtonClick EVALUATE("A3"), EVALUATE("A4"). In general, it seems that you might benefit from researching the difference between functions, subs, and how they are called.
  
  – John Coleman
  Mar 7 at 18:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Dont make it a function, make it a subscript, make the optional a range, and only 1 not two. All you have to do is, select the two cells and open the macro menu and click run. thats one way around it.
  
  – Ricardo A
  Mar 7 at 18:34
  

  
  
  
  

add a comment | 

0

I wrote a macro that originally had 0 arguments, and called the macro from a button on my Excel sheet. The design changed, and now I added two optional arguments to the macro, like this:

Function ButtonClick(Optional prop1 As String, Optional prop2 As String)
 `Do stuff here
End Function

I am now trying to pass both arguments to the macro from the button, but get an error each time.

First I tried:

Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))

as was suggested in this question, but got an error for That function isn't valid.

I noticed that in the question, the top answer also called for surrounding the entire macro name in single quotes, so I did so:

'Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))'

And this time got there error Formula is too complex to be assigned to an object.

What am I doing wrong?

excel vba

share|improve this question

edited Mar 8 at 7:16

Instant Breakfast

94211227

asked Mar 7 at 18:30

pavuxunpavuxun

1167

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  Why have ButtonClick be a function rather than a sub? You are calling it as a sub (so using the outer parentheses in Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4")) is a syntax error, it should be Sheet1.xlsm!ButtonClick EVALUATE("A3"), EVALUATE("A4"). In general, it seems that you might benefit from researching the difference between functions, subs, and how they are called.
  
  – John Coleman
  Mar 7 at 18:33
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Dont make it a function, make it a subscript, make the optional a range, and only 1 not two. All you have to do is, select the two cells and open the macro menu and click run. thats one way around it.
  
  – Ricardo A
  Mar 7 at 18:34
  

  
  
  
  

add a comment | 

I wrote a macro that originally had 0 arguments, and called the macro from a button on my Excel sheet. The design changed, and now I added two optional arguments to the macro, like this:

Function ButtonClick(Optional prop1 As String, Optional prop2 As String)
 `Do stuff here
End Function

I am now trying to pass both arguments to the macro from the button, but get an error each time.

First I tried:

Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))

as was suggested in this question, but got an error for That function isn't valid.

I noticed that in the question, the top answer also called for surrounding the entire macro name in single quotes, so I did so:

'Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))'

And this time got there error Formula is too complex to be assigned to an object.

What am I doing wrong?

excel vba

share|improve this question

edited Mar 8 at 7:16

Instant Breakfast

94211227

asked Mar 7 at 18:30

pavuxunpavuxun

1167

Function ButtonClick(Optional prop1 As String, Optional prop2 As String)
 `Do stuff here
End Function

Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))

'Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))'

share|improve this question

edited Mar 8 at 7:16

Instant Breakfast

94211227

asked Mar 7 at 18:30

pavuxunpavuxun

1167

share|improve this question

edited Mar 8 at 7:16

Instant Breakfast

94211227

asked Mar 7 at 18:30

pavuxunpavuxun

1167

edited Mar 8 at 7:16

Instant Breakfast

94211227

edited Mar 8 at 7:16

Instant Breakfast

94211227

asked Mar 7 at 18:30

pavuxunpavuxun

1167

asked Mar 7 at 18:30

pavuxunpavuxun

1167

1 Answer
1

active

oldest

votes

2

This worked for me:

'Sheet1.ButtonClick Evaluate("A3"), Evaluate("A4")'

With ButtonClick as a Sub. The single quotes are required.

Note this also works:

'Sheet1.ButtonClick [A3], [A4]'

since [] is a shortcut for VBA's Evaluate()

share|improve this answer

edited Mar 8 at 1:07

answered Mar 7 at 18:52

Tim WilliamsTim Williams

89.1k97087

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried this: 'Sheet1.xlsm!ButtonClick EVALUATE("A3"), EVALUATE("A4")' with the same Formula is too complex to be assigned to an object error. When I tried 'Sheet1.ButtonClick EVALUATE("A3"), EVALUATE("A4")' there was no error message, but the macro was never called
  
  – pavuxun
  Mar 7 at 19:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I got it to work by using just 'ButtonClick EVALUATE("A3"), EVALUATE("A4")'. For some reason it didn't like the prefix of the sheet
  
  – pavuxun
  Mar 7 at 19:26
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Needs a period not !
  
  – Tim Williams
  Mar 7 at 20:01
  

  
  
  
  

add a comment | 

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55050572%2fpassing-two-string-arguments-to-excel-macro-from-button%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

This worked for me:

'Sheet1.ButtonClick Evaluate("A3"), Evaluate("A4")'

With ButtonClick as a Sub. The single quotes are required.

Note this also works:

'Sheet1.ButtonClick [A3], [A4]'

since [] is a shortcut for VBA's Evaluate()

share|improve this answer

edited Mar 8 at 1:07

answered Mar 7 at 18:52

Tim WilliamsTim Williams

89.1k97087

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried this: 'Sheet1.xlsm!ButtonClick EVALUATE("A3"), EVALUATE("A4")' with the same Formula is too complex to be assigned to an object error. When I tried 'Sheet1.ButtonClick EVALUATE("A3"), EVALUATE("A4")' there was no error message, but the macro was never called
  
  – pavuxun
  Mar 7 at 19:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I got it to work by using just 'ButtonClick EVALUATE("A3"), EVALUATE("A4")'. For some reason it didn't like the prefix of the sheet
  
  – pavuxun
  Mar 7 at 19:26
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Needs a period not !
  
  – Tim Williams
  Mar 7 at 20:01
  

  
  
  
  

add a comment | 

2

This worked for me:

'Sheet1.ButtonClick Evaluate("A3"), Evaluate("A4")'

With ButtonClick as a Sub. The single quotes are required.

Note this also works:

'Sheet1.ButtonClick [A3], [A4]'

since [] is a shortcut for VBA's Evaluate()

share|improve this answer

edited Mar 8 at 1:07

answered Mar 7 at 18:52

Tim WilliamsTim Williams

89.1k97087

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried this: 'Sheet1.xlsm!ButtonClick EVALUATE("A3"), EVALUATE("A4")' with the same Formula is too complex to be assigned to an object error. When I tried 'Sheet1.ButtonClick EVALUATE("A3"), EVALUATE("A4")' there was no error message, but the macro was never called
  
  – pavuxun
  Mar 7 at 19:07
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I got it to work by using just 'ButtonClick EVALUATE("A3"), EVALUATE("A4")'. For some reason it didn't like the prefix of the sheet
  
  – pavuxun
  Mar 7 at 19:26
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Needs a period not !
  
  – Tim Williams
  Mar 7 at 20:01
  

  
  
  
  

add a comment | 

This worked for me:

'Sheet1.ButtonClick Evaluate("A3"), Evaluate("A4")'

With ButtonClick as a Sub. The single quotes are required.

Note this also works:

'Sheet1.ButtonClick [A3], [A4]'

since [] is a shortcut for VBA's Evaluate()

share|improve this answer

edited Mar 8 at 1:07

answered Mar 7 at 18:52

Tim WilliamsTim Williams

89.1k97087

'Sheet1.ButtonClick Evaluate("A3"), Evaluate("A4")'

'Sheet1.ButtonClick [A3], [A4]'

share|improve this answer

edited Mar 8 at 1:07

answered Mar 7 at 18:52

Tim WilliamsTim Williams

89.1k97087

answered Mar 7 at 18:52

Tim WilliamsTim Williams

89.1k97087

answered Mar 7 at 18:52

Tim WilliamsTim Williams

89.1k97087