Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-19-2020, 07:33 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Dim statement for Split array Windows XP Dim statement for Split array Office 2007
Competent Performer
Dim statement for Split array
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default Dim statement for Split array


I don't understand why this code works:
Code:
Dim BlueLite As String          'Light blue RBG values
BlueLite = "219,229,241"        '.
Dim RGBVals As Variant          'Array for indivicdual RGB values
RGBVals = Split(BlueLite, ",")  '.
But this code gets a runtime error '13' (Type mismatch):
Code:
Dim BlueLite As String          'Light blue RBG values
BlueLite = "219,229,241"        '.
Dim RGBVals() As Variant        'Array for indivicdual RGB values
RGBVals = Split(BlueLite, ",")  '.
Aren't the parens required when declaring an array?
Reply With Quote
  #2  
Old 02-19-2020, 09:04 PM
macropod's Avatar
macropod macropod is offline Dim statement for Split array Windows 7 64bit Dim statement for Split array 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

If you declare an array, you need to populate it with code like:
Code:
Dim x As Long, y as long
Dim BlueLite As String          'Light blue RBG values
BlueLite = "219,229,241"        '.
Dim RGBVals() As Variant        'Array for indivicdual RGB values
x = UBound(Split(BlueLite, ","))
ReDim RGBVals(x)
For i = 0 To x
  RGBVals(y) = Split(BlueLite, ",")(y)  '.
Next
It's not apparent why you'd want to go down the array path for RGB values, though, whichever approach you take.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-19-2020, 10:28 PM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Dim statement for Split array Windows XP Dim statement for Split array Office 2007
Competent Performer
Dim statement for Split array
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
If you declare an array, you need to populate it with code like:
I thought the way to declare a dynamic array was with
Code:
Dim varname() . . .
But this code
Code:
Dim sLiteBlue As String
sLiteBlue = "219,229,241"
Dim RGBVals
RGBVals = Split(sLiteBlue, ",")
results in RGBVals being a 3-element array,
Code:
?rgbvals(0)
219
?rgbvals(1)
229
?rgbvals(2)
241
so that this code gets me the integer RGB value:

Code:
?RGB(RGBVals(0), RGBVals(1), RGBVals(2))
15853019
I don't understand why adding the "()" to the Dim statement causes an error.

Quote:
It's not apparent why you'd want to go down the array path for RGB values, though, whichever approach you take.
Because I want to declare the highlight color as a string ("219,229,241") and then convert it to the integer value:

Code:
Dim sLiteBlue As String
sLiteBlue = "219,229,241"
Dim iLiteBlue As Long
Dim RGBVals As Variant
RGBVals = Split(sLiteBlue, ",")
iLiteBlue = RGB(RGBVals(0), RGBVals(1), RGBVals(2))
If there is a better way to convert sLiteBlue ("219,229,241") to 15853019, I'd be happy to use it.
Reply With Quote
  #4  
Old 02-19-2020, 10:37 PM
macropod's Avatar
macropod macropod is offline Dim statement for Split array Windows 7 64bit Dim statement for Split array 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

So why not simply pass the RGB value as a long? That's what the code in https://www.msofficeforums.com/148862-post9.html does via:
Const w As Long = RGB(255, 255, 255)
and:
s = RGB(198, 217, 241)
for example.

Regardless, even if you want to pass the rgb values as a string, you don't need the array. You could, for example, use:
Code:
Dim r As Long, g As Long, b As Long, iLiteBlue As Long
Const sLiteBlue As String = "219,229,241"
r = Split(sLiteBlue, ",")(0)
g = Split(sLiteBlue, ",")(1)
b = Split(sLiteBlue, ",")(2)
iLiteBlue = RGB(r, g, b)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 02-20-2020, 06:03 AM
Jennifer Murphy's Avatar
Jennifer Murphy Jennifer Murphy is offline Dim statement for Split array Windows XP Dim statement for Split array Office 2007
Competent Performer
Dim statement for Split array
 
Join Date: Aug 2011
Location: Silicon Valley
Posts: 234
Jennifer Murphy is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
So why not simply pass the RGB value as a long? That's what the code in https://www.msofficeforums.com/148862-post9.html does via:
Const w As Long = RGB(255, 255, 255)
[/CODE]
When I saw this example, I was excited to be able to simplify the macro, but a little embarrassed that I missed it in the other thread. I never knew you could put an expression with a function call in a Const statement. But when I tried it, I got an error that it was an invalid statement. Does it really work?

I was able to get something almost as good to work.
Code:
Dim Blue As Long: Blue = RGB(219, 229, 241)
Dim White As Long: White = RGB(255, 255, 255)
Thank you very much
Reply With Quote
  #6  
Old 02-20-2020, 02:42 PM
macropod's Avatar
macropod macropod is offline Dim statement for Split array Windows 7 64bit Dim statement for Split array 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

Quote:
Originally Posted by Jennifer Murphy View Post
When I saw this example, I was excited to be able to simplify the macro, but a little embarrassed that I missed it in the other thread. I never knew you could put an expression with a function call in a Const statement. But when I tried it, I got an error that it was an invalid statement. Does it really work?
Sorry, that really should have been:
Dim w As Long: w = RGB(255, 255, 255)
rather like you're using. To use 'Const w As Long =', one would have to use the actual RGB value, not the RGB function
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dim statement for Split array 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
Converting a Select statement in Excel to an update statement shabbaranks Excel Programming 5 10-31-2018 11:47 PM
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
Dim statement for Split array Convert String Array to Integer Array from a User Input? tinfanide Excel Programming 4 12-26-2012 08:56 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:43 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