MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem Executing Subroutine

  Asked By: Jimmy    Date: Feb 11    Category: MS Office    Views: 1327

I wrote a subroutine in Excel VBA, and it accepts arguments. I can't
figure out how to execute it from my spreadsheet. When I run it
through Tools-Macros, it says "Argument not optional." How do I pass
the needed arguments to it?



6 Answers Found

Answer #1    Answered By: Faiza Mian     Answered On: Feb 11

Did you write the procedure as a Sub or a Function?

Answer #2    Answered By: Muhammad Evans     Answered On: Feb 11

The sub will need to find its own data. Or you need a parent sub that asks
the user for the information.

It is common to select a cell or range before running the sub and have the
sub look at that.

Answer #3    Answered By: Kian Evans     Answered On: Feb 11

I've gotten a little further, just can't quite get my sub to read
named arrays. For instance, this works:

Sub MySub1()
Msgbox Range("MyNamedCell")
End Sub

...but this doesn't:

Sub MySub2()
Dim MyRange as Range
MyRange = Range("MyNamedRange")
End Sub

I get "Run time error 91: Object Variable or With object variable not
set." Debug highlights the 3rd line in my second subroutine.

When I try something simpler:

Sub MySub3()
Dim MyRange as Range
MsgBox Range("MyNamedRange")
End Sub

I get a type mismatch 13. Is there some syntax I need to use to
access a named range of more than one cell?

Answer #4    Answered By: Tomas Thompson     Answered On: Feb 11

To assign to an object reference you need to use Set.

So use

Set MyRange = Range("MyNamedRange")

Answer #5    Answered By: Madeeha Malik     Answered On: Feb 11

A message box needs a string as an argument. If you give it a one cell
range it will take the text or value of that cell as the string. If you
give it a multi-cell range it does not know how to convert it to a string.
You would need to do that first (perhaps by stepping through the cells in
the range and concatenating their values) and then pass the string to the
message box.

Answer #6    Answered By: Aaminah Khan     Answered On: Feb 11

If you want to read arrays, instead of MsgBox try
Application.InputBox which allows you to specify the type of
information to collect from the user.

Everything is the same as the standard InputBox command except there
is an extra argument  "Type" at the end. This argument allows lets you
pick the data type. Type:=64 is used for an array.

Didn't find what you were looking for? Find more on Problem Executing Subroutine Or get search suggestion and latest updates.