Excel/VBA declaring variables

[Alan Beban]

here is my code:
# Function Blah(InputArray)

If TypeName(InputArray) Like "*()" Then
If TypeName(InputArray) = "Boolean()" then
Dim outputArray() As Boolean
Elseif TypeName(InputArray) = "Date()" then
Dim outputArray() As Date
End If
End if #

I get an error message on the Dim outputArray() As Date line: "Duplicate declaration in current scope"

How do I declare a variable as the same type as the input array--InputArray?

Thanks.

Edit / Delete Edit Post Quick reply to this message Reply Reply With Quote Reply With Quote Multi-Quote This Message

[AB33]

You have declared outputArray twice. You can not use the same variable name twice despite the types are different.
Dim outputArray() As Boolean
Dim outputArray() As Date

[Alan Beban]

I think you missed the point. The question I am asking is how do I declare a variable as the same Data Type as that of the input array--InputArray? Obviously it can't be done the way I tried; how can it be done?

[xlnitwit]

This is a duplicate of this post- https://www.excelforum.com/excel-pro...variables.html
and you still haven't used code tags, I'm afraid.

[Alan Beban]

I tried to. I put the # symbol before and after the code. I would very much like to but I'm afraid I just don't understand what's being asked of me. I think that despite my inadequacies my question to the forum is clear; can't someone address it? Or steer me to how to present the code in a satisfactory format?

[xlnitwit]

You aren't supposed to put # before and after the code. You either select the code and then press the # button on the toolbar above the reply box, or you manually add the code tags like this [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code].

If you review the moderator reply to your original thread, you'll see that we are not allowed to help until you comply. As soon as you do, we'll be delighted to assist you!

[Alan Beban]

I didn't see a # icon so I did it manually as suggested. Here is my code:

Please Login or Register  to view this content.

I get an error message on the Dim outputArray() As Date line: "Duplicate declaration in current scope"

How do I declare a variable as the same type as the input array--InputArray?

Thanks.

[xlnitwit]

I think a more important question is why do you think you need to? Your only real option, that I can think of, is to declare an array variable for all possible input types and then assign that to another variant variable, but I can't see any benefit to it at all.

[Alan Beban]

Thanks for replying. The function Blah is a function that will return the Transpose of a 1-D or 2-D array or a single-area range. Unless you're of the view that a function to return the Tranpose of an array is not useful--I'm not of that view--it seems to me that it would be desirable that the returned output, if it is an array that has been input rather than a range, be of the same Data Type as the array that was input to the function. In the code I provided there is no way that more than one declaration could ever be operative, since they are mutually exclusive, so I don't feel there's any real duplication. But that's Microsoft's call. Thanks for your suggestion of a different variable for each Data Type, e.g., outBoolean(), outDate(), outInteger(), outLong() etc. for all the built-in types. Cumbersome, but doable. Thanks again for replying.

Alan Beban, former Microsoft Excel MVP.

[jindon]

I don't understand what you are trying to do though,,, one way

Please Login or Register  to view this content.

[xlnitwit]

Unless you're of the view that a function to return the Tranpose of an array is not useful

I'm not sure how you could infer that from what I said, but that was not my meaning.

[Alan Beban]

Replying to xinitwit, I inferred it from your question "I think a more important question is why do you think you need to? " followed by a useful suggestion how to do it, followed by the comment "but I can't see any benefit to it at all." As I said in a previous reply, the benefit I see is to return the Transpose of an array with same Data Type as the array that was transposed. The only way I couldn't see that as a benefit is if the function itself is not useful.

Replying to jindon, thanks. I believe your code works. My previous post, #9 in this thread, will give you a better understanding of what I'm trying to do. Thanks again. How do I indicate "Problem solved"?

[Alan Beban]

Further reply to Jindon. Interestingly, your code doesn't seem to work without including the dimensions.

Please Login or Register  to view this content.

works, for example, though

Please Login or Register  to view this content.

throws a Syntax error.
I have to go back and see whether coding those dimensions at that point is more or less preferable than using a separate variable for each Data Type. Thanks again.

[xlnitwit]

Replying to xinitwit, I inferred it from your question "I think a more important question is why do you think you need to? " followed by a useful suggestion how to do it, followed by the comment "but I can't see any benefit to it at all." As I said in a previous reply, the benefit I see is to return the Transpose of an array with same Data Type as the array that was transposed. The only way I couldn't see that as a benefit is if the function itself is not useful.

My point was that I don't see the benefit to being able to declare an array of the relevant type. The input to your function is a Variant, which may or may not contain an array. If you simply declare a Variant in your function and assign the input argument to it, you will have a Variant that contains the same data type as the input, to which you can do whatever you need- resize, transpose, whatever. There is no need to attempt multiple declarations. Here is a very simplistic demonstration

Please Login or Register  to view this content.

[Alan Beban]

Thanks a heap! That's the answer to my original question--how do I declare a variable with the same Data Type as the input array.
By the way, the input to the function will not necessarily be a Variant, as you stated. It will be whatever the user inputs, and if it's not a range or 1-D or 2-D array an error message will be returned. In your example, for example, InputArray was a Double.

Also, by the way, if in your Sub test() v were a Range, the MsgBox would display Variant().

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

obviates the problem.

Thanks again.

[xlnitwit]

By the way, the input to the function will not necessarily be a Variant, as you stated.

As written the input to the function always comes in as a Variant- it doesn't matter what type of data you passed to it. Indeed, that is why you can pass any kind of data to it. Your InputArray argument has no declared type and is therefore a Variant, and your function has no declared return type so will always return a Variant.

The code I posted was merely to demonstrate a point- it was by no means intended as a robust solution!

[Alan Beban]

I don't understand.

Please Login or Register  to view this content.

n

The MsgBox displays Double. What am I misunderstanding?

[xlnitwit]

Typename returns the data type that is contained inside the Variant. Look at the variables in the Locals window while stepping through the code.