I have been looking at various VBA code snippets for something I am trying to do.
I am taking a user-defined range and then passing it to a function which will apply a calculation to all the values in that range (say n ^ 2).
What I see in the snippets is often code that looks like this:
y = .Transpose(.Transpose(y))
Where y is the data range passed to the calculation function.
.Transpose would convert the columnar range to a row array (yes?). But why yet another .Transpose?
My only guess is that this takes a range of data (passed to this calculation function) to make it an array of data with the same dimensionality as the original.
Is that correct? What does .Transpose have to do with making ranges into arrays in VBA?
Thank you for your help.
Transposing twice converts the data from a 2D array (the uniform result in a variant receiving data from a multi-cell range) to a 1D array if the range is a row or column vector.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thank you.
What is the result of a single transpose and how does one convert a two-dimensional range into a two dimensional array?
Possibly use Evaluate ?I am taking a user-defined range and then passing it to a function which will apply a calculation to all the values in that range (say n ^ 2)
above is based on contiguous range which may include both numerics & non-numerics, for non-contiguous range you would need to iterate the range areasCode:Selection.Value = Evaluate("IF(ROW(" & Selection.Address & ")*ISNUMBER(" & Selection.Address & ")," & Selection.Address & "^2,TEXT(" & Selection.Address & ",""?""))")
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks