Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-07-2020, 01:00 PM
BobBridges's Avatar
BobBridges BobBridges is offline Conflict between Dim, ReDim and Split Windows 7 64bit Conflict between Dim, ReDim and Split Office 2010 32bit
Expert
Conflict between Dim, ReDim and Split
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default Conflict between Dim, ReDim and Split

I'm not having much luck getting answers to my questions here recently. Weird set of questions, maybe. Here's another one, a problem I ran into today while attempting something I thought would be pretty conventional:

Stage 1: I calculate the number of rows and columns I need, populate the array and then dump it into a worksheet. This works:

Code:
Dim ar()
ReDim ar(1 to rZ, 1 to cZ)
'blah, blah, blah
Range(ows.Cells(1, 1), ows.Cells(rZ, cZ)).Value = ar
Stage 2: Now I find I need a quick array, just for a couple of statements before I get started. No reason, as far as I can see, not to use the same variable name. So somewhere above this code I add this statement:



Code:
ar = Split(vm)
Error: duplicate declaration. Oops—of course if I use ar before the "Dim ar" statement, that's a duplicate declaration. But I don't really need the Dim statement, since I'm already defining ar as an array by assigning it with the Split function. So I simply removed the Dim statement.

Didn't work. When I remove the Dim statement, the VBA interpreter complains at "compile" time that the ReDim statement is invalid.

Ok, apparently I still need "Dim ar()", it just needs to be above the first use of ar. So (Stage 3) now my program has the statements in this order (though not all together):

Code:
Dim ar()
ar = Split(vm)
ReDim ar(1 to rZ, 1 to cZ)
'blah, blah, blah
Range(ows.Cells(1, 1), ows.Cells(rZ, cZ)).Value = ar
Now the program complains, at execution time, that using Split to assign an array to ar is an "invalid assignment".

I'll have to use a different variable name, it appears. But why? I looked up Split and ReDim in the language reference and don't see anything there to indicate where the limitation comes in. Does anyone know?
Reply With Quote
  #2  
Old 04-15-2020, 08:40 PM
Guessed's Avatar
Guessed Guessed is offline Conflict between Dim, ReDim and Split Windows 10 Conflict between Dim, ReDim and Split Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

You've got this post marked as solved - perhaps that is why you aren't getting answers.

As far as I know, you use Split to create a one dimensional array of substrings from a string by supplying the string and the character used to separate the substrings. Therefore your commands to dim and populate the array can only populate one row or column and might look like
Code:
Sub aTest()
  Dim ar() As String, vm As String
  vm = "a,b,c,d,e,f"
  ar = Split(vm, ",")
  Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, UBound(ar) + 1)).Value = ar
End Sub
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 04-15-2020, 09:53 PM
macropod's Avatar
macropod macropod is offline Conflict between Dim, ReDim and Split Windows 7 64bit Conflict between Dim, ReDim and Split Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Cross-posted at: Conflicts between Dim, ReDim and Split | Chandoo.org Excel Forums - Become Awesome in Excel
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 04-16-2020, 03:35 PM
BobBridges's Avatar
BobBridges BobBridges is offline Conflict between Dim, ReDim and Split Windows 7 64bit Conflict between Dim, ReDim and Split Office 2010 32bit
Expert
Conflict between Dim, ReDim and Split
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Partly right. First I use Split to turn a string of space-delimited words into an array; I needed the array for only a few lines, one "paragraph", so I used a throwaway variable:
Code:
' Let's say vm contains "AR GA MC".  Then:
ar = Split(vm)
' Now ar(0) contains "AR", ar(1)="GA", ar(2)="MC"
But I'm not using that array to paste into a worksheet. Further down I tried to use ReDim like this:
Code:
ReDim ar(1 to rZ, 1 to cZ) 'max row and column
For jr = 2 to rZ
  ar(jr, 1) = Column1Value
  ar(jr, 2) = Column2Value
  ' ...and so on
  Next jr
Range(ows.Cells(1, 1), ows.Cells(rZ, cZ)).Value = ar
As I wrote in my original post, when I didn't Dim ar(), the VBA interpreter complained at compile time that the ReDim statement is invalid. When I declare ar(), it complains at execution time that the Split function is an invalid assignment.

After the question had languished here for a while, I gave up and posted it over at another forum. After I started getting answers over there I marked this one "solved" so as not to waste anyone's time here. Eventually someone there got me to try declaring ar() As String, and it worked! Still not sure why letting it stay Variant didn't work as well, but I'll keep it in mind.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conflict between Dim, ReDim and Split Mail Merge - split merged documents and rename each split document based on text in header FuriousD Word VBA 1 05-12-2019 04:06 AM
Split word file into several PDF using the bookmarks as split positions and name Fixxxer Word VBA 7 10-08-2018 01:10 AM
Split function in Excel (split the screen) Officer_Bierschnitt Excel 1 07-05-2017 07:02 AM
Folders in your mailbox have name conflict baclava Outlook 0 10-19-2016 08:50 AM
Template conflict b0x4it Word 0 05-21-2011 07:44 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft