delaing wrote:... is it simply having an eagle eye or do you have a trick for catching these types of road hazards?...
I lack the experience unfortunately but have managed to get some long tricky strings and multiple loop codes correct...syntaxly
I find it helps partially to have a “paired” mentality... inpairing.... Think a bit like Code tag “Pairs” I guess – I think some older Professionally are used to that.. or people doing a lot of HTML code and similar.
But Pairing seems to work well in
VBA also
For Next, Do Loop , If End IF etc. .. Indenting Stuff
I never ever write something like a
___For
without hitting the Enter a few times
then writing a
___Next
That also has the advantage of ensuring you get the same Offset – and I am pretty pedantic about my Offsets, ( so is the Wife ) .
Dim X, Y, Z
_Set X
_Let Y =
___ For Z = 1
To
____ Stuff
____ Stuff
______ If something
Then
_______ Stuff
_______ Stuff
__________ Do While
___________ Stuff doing
___________ stuff doing
__________ Loop
______ Else
_______ Other stuff instead
______ End If
___ Next Z
Note: - on this Point:
VBA is not too helpful if you Mess up – It will likely tell you “Error For without Next” for any error coming from a missing line, whether it is in the
If , the
Do or any other Loop or conditional bit that you messed up
http://www.mrexcel.com/forum/excel-ques ... ost3869039" onclick="window.open(this.href);return false;
_.....
Bracket Stuff
I never Type a start bracket
( without clicking a few spaces
_________ and adding a stop bracket
). Same goes for a start quote
“ and a stop quote
“ . I always do that in pairs, - I do not think there are many situations where these things do not come in some sort of paired sequence....
_...
Quote Stuff
Quotes in VBA are about the trickiest things I have ever come across when it comes to missing bits_...
In addition then to Pairing things, I really need a careful bit by bit development....
I just did something that really strained my brain.... I will give just a simplified shortened example:
Say I a have ( as simple text string values ) a
Biscuit in a Spreadsheet cell, the 4th cell in any Worksheet will do , and I put a
Barrel in the 5th Cell next to it.
Then say I want to change those to Formulas like
= ”Biscuit” , say in the next Row
Biscuit.JPG
http://imgur.com/CKGLp72" onclick="window.open(this.href);return false;
Biscuit.JPG
( I have a very good reason for doing this. I can’t for the life on me remember what it is just now, but never mind..)
The final solution I come up with involves a long complicated string
"=" & """=""" & "&" & """""""""" & "&" & Range("D1:E1").Address & "&" & """"""""""
I still do not fully understand how it works, but without a “pairing mentality” I would never even get close to it.
Here the final Code:
Code: Select all
Sub OLEWigyWamBiscuitBarrelMollyWollyModPod_Formula() ' http://www.eileenslounge.com/viewtopic.php?f=27&t=25298&sid=ef6015598182c24ebf1dff35b2d7747a#p196216
10 Range("D1:F2").ClearContents ' Clear any previous Values
20 Let Cells(4).Value = "Biscuit": Let Cells(5).Value = "Barrel"
30 'Let Cells(6).Value = "=" & """=""" & "&" & """""""""" & "&" & Range("$D$1").Address & "&" & """"""""""
40 Dim strEval As String
50 Let strEval = "=" & """=""" & "&" & """""""""" & "&" & Range("D1:E1").Address & "&" & """"""""""
52 'Let Range("F1").Value = strEval
55 Debug.Print strEval ' Ctrl+G shows ="="&""""&$D$1:$E$1&""""
60 Dim DottyFormulaValues() As Variant ' Evalute below will retunn a field ( Array ) of Variant types
70 Let DottyFormulaValues() = Evaluate(strEval)
77 ' Put Stop in ... see below ... to see in
80 Let Range("D2:E2").Value = DottyFormulaValues() 'Field ( Array) of values can be applied to a Spreadsheet Range
'77a) Immediate Window
90 Debug.Print DottyFormulaValues()(1) ' Ctrl+G shows ="Biscuit"
100 Debug.Print DottyFormulaValues(2) ' Ctrl+G shows ="Barrel"
End Sub
'77b) WatchWindow: ..... (click left hand margin reveals Brown circle) at End Sub -- run Code -- Highlight DottyFormulaValues() -- Hit Shift+F9 -- Add variable to Watch window -- click on + to show Array values reveals
' DottyFormulaValues.JPG -- http://imgur.com/iwEHAFV
' DottyFormulaValues2.JPG -- http://imgur.com/KfpoYY7
' http://www.excelforum.com/showthread.php?t=1154829&page=8#post4521059
...
The VBA evaluate ( Full version ) takes a string argument of what you actually would type into a Spreadsheet Cell, in other words it “pseudo” puts the string into the Cell, then give you back the result that you would of had in the cell. _..( People talk about there being a “Evaluate Engine”, ? ).....
_..Like....
Evaluate(“Here what you would write in a Spreadsheet cell”)
Evaluate(“SpreadsheetCellStuff“)
Evaluate(“SCS“)
This use of a String allows us to build that string in the usual way that we do in
VBA
So like I can do
Evaluate(“SCS“ & VBA Code Stuff & “ OtherSCS“ & “OtherSCS“),
and while I am at it add a string that has no length
Evaluate(“SCS“ & VBA Code Stuff & “OtherSCS“ & “OtherSCS “ &””),
So my final solution in that code looks like this, ( I confess sometimes I cheat a bit sometimes and get the answer quickly by trial an error.. but I always go back and start again carefully )
"=" & """=""" & "&" & """""""""" & "&" & Range("D1:E1").Address & "&" & """"""""""
I still do my head in occasionally trying to figure out exactly how it works.. But I would usually Build it up like:....
My start is / was the String in one of the Cells as I would have to write it to actually get ="Biscuit". That is then the formula string I want to put finally in the Cell. (
The point of the exercise here if I can remember is getting that from the Cell value information and doing that neatly for a multi cell Range all at once without looping ). In other words I want the Evaluate(“ “) to give me a string formula (
actually an Array of String Formulas ) of the form ="Biscuit"
I physically with my fingers ( I find that works best ) would have to write this, say in my 6th Cell
="=" & """" & $D$1 & """",
which would return in the Cell,
="Biscuit"
The only thing of technical difficulty there is getting a single quote “out” in a final string. I think we know this
=
”” gives us nothing / null string
and this
=
”Somefink
” gives us Somefink
I never found anyone to explain the following , so my theory is as good as anyone’s: As long as we keep the “
Paired Mentality Partially Impaired” Idea then it all ties up.
This is syntaxly accepted
=
”Some
””fink
”
But I think Excel gets a bit confused, somehow seeing simultaneously a quote then string end which it both takes as a literal quote and a string start resulting in a single quote at that point in the final given string within the total string. .. I read this from some Microsoft VBA Code Documentation once...
... can lead to a recursive evaluation process if the returned has a further default member. Recursion through this chain of default members may be implicit if evaluating to a simple data value and each default member has an empty parameter list, or explicit if index expressions are specified that specifically parameterize each default member...The implementation may determine such an evaluation to be invalid statically or may raise error 9 (Subscript out of range) or 13 (Type mismatch) during evaluation at runtime.... or just chuck up a string literal ’’ instead.
I do not think that has any relevance but I like the sound of it.
( Building a literal string within a
VBA “ “ string instruction is the same to get a single Quote
“ so this form
“ “” “ )
Anyway... If I write physically_..
="=" & """" & $D$1 & """"
_..in say the 6th cell, then what comes up in the Cell is like my original that I want finally to look like in the formula bar
="Biscuit"
Biscuit2.JPG
Biscuit2.JPG
It is plain sailing now.. I want to put this ,
="=" & """" & $D$1 & """" , in the 6th Cell using
VBA, .. and I think if I can remember I want to get to get that string (
Array of strings ) using Evaluate(“ “), like
DottyFormulaValues() = Evaluate(strEval)
So I think in pairs and build the final string
all in VBA in bits, sequentially.. logically like..... starting from the left...
_.....
= is string
“ “ with
= in it so is
“=” ( my in paired thinking makes me write
“ “ first , then
= in it )
& ( This
& is VBA “ joining glue concatenating thingy” )
“=” is string
“ “ with
“” and
= and
“” in it, enit? – so is
“””=””” ( my im paired thinking makes me write
“ ____ “ then
“” then
= then
“” )
&
& is string
“ “ with
& in it, enit? – so is
“&”
&
“””” is string
“ “ with
“” and
“” and
“” and
“” in it enit? – so is
“”””””””””
&
& is string
“ “ with
& in it, enit? – so is
“&”
&
$D$1 is a string but can be got by a VBA thing that is
Range("$D$1]").Address ( I do it this way as I may use a Range Object variable, like rng, in place of Range(“ “) in a final application )
&
& is string
“ “ with
& in it, enit? – so is
“&”
&
“””” is string
“ “ with
“” and
“” and
“” and
“” in it enit? – so is
“”””””””””
So transposing all that we have ( for the multi cell range ) we have
"=" & """=""" & "&" & """""""""" & "&" & Range("D1:E1").Address & "&" & """"""""""
The above is the string in a VBA code line , such as this form:
strEval = "=" & """=""" & "&" & """""""""" & "&" & Range("D1:E1").Address & "&" & """"""""""
required to return into the string variable, strEval, the following ( seen in a message box output or in the immediate window using Debug.Print):
="=" & """" & $D$1:$E$1 & """"
This is similar in form to what I physically wrote into Cell F1 to get-..
="Biscuit"
_..shown in that Cell, so when strEval is put in a cell or used as the string in the
Evaluate( ) this will return us that Formula
which can be pasted out to a Spreadsheet Range
http://www.excelforum.com/showthread.ph ... ost4521059" onclick="window.open(this.href);return false;
_...
I know there are some geniuses around that could just write such a code line off the top off their head.. But I can’t. I am not sure I would want to.. once you do it from instinct you forget I think why and how the stuff came about. That is bad.
Just IMHO...
Alan
P.s Just to clarify a Bit of convention stuff I use: These:
“ and
“ are all
VBA things,
but Blue is a command type thing like the glue
& or code statement
Range or the paired start and stop
“ “ instruction or the break of such a string
“ & “
Green is a literal string either within a
VBA start and stop
“ “ instruction or as I physically type in a Cell.
Purple is a bit more subtle: Within a
VBA start and stop
“ “ string indication instruction I need to double up a quote pair
“” to get a returned single
“ literal string into a String variable as seen pasted out or inn a message box output or in the Immediate Window ( The Watch window will add the extra paired start and stop
“ “ String indication instruction )
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
