Solved

how do we get the size of input range in Excel VBA?

Posted on 2015-01-30
1
192 Views
Last Modified: 2015-01-30
Hi experts,

I try to do something and need to have a range as input in VBA function. But when I use the function, it always gives me an error, what could be wrong.

Function getSize(arr)
    rowSize = UBound(arr, 1) - LBound(arr, 1) + 1
    colSize = UBound(arr, 2) - LBound(arr, 2) + 1
    MsgBox ("input is a " & TypeName(arr) & "with size of " & rowSize & " X " & colSize & ".")    
End Function

Please take a look.
RDB
0
Comment
Question by:ResourcefulDB
1 Comment
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 40580825
Arr is not an array, but a range, so you can not use UBound and LBound.

2 options

Load the range into an array like this
Function getSize(arrIn)
    arr = arrIn
    rowSize = UBound(arr, 1) - LBound(arr, 1) + 1
    colSize = UBound(arr, 2) - LBound(arr, 2) + 1
    MsgBox ("input is a " & TypeName(arrIn) & "with size of " & rowSize & " X " & colSize & ".")
End Function

Open in new window


Or get the size directly on the range like this
Function getSize(arr As Range)
    Dim rowSize As Long, colSize As Long
    rowSize = arr.Rows.Count
    colSize = arr.Columns.Count
    MsgBox ("input is a " & TypeName(arr) & " with size of " & rowSize & " X " & colSize & ".")
End Function

Open in new window

0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Hiding information in Excel 16 70
Excel format formula for currency 15 22
Clear Filter 8 32
Excel filter on tab not showing any entries? 5 18
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question