Solved: Variables in String Issue

[Mcygee]

Hello everybody. Thanks for taking the time to look at my issue.

Going to use just examples to get my general problem across. The problem is as such...

I have a public variable lets call "PublicVariable" that I'm using across two subs.

Dim PublicVariable as String

In Sub1 I have...

PublicVariable = """=(35*(5-H"" & LastRow2 & ""))-H"" & LastRow1"

LastRow2 and LastRow1 being variables from a previous calculation for the last row and the last row + 1. But that doesn't really matter. They are just variables.

In Sub2 I have...

Cells(2, 10).Formula = PublicVariable


What happens is, instead of Cells(2,10) getting this for a formula....

=(35*(5-H2))-H1

it gets this for a formula...

"=(35*(5-H" & LastRow2 & "))-H" & LastRow1

The exact text without the variables and such calculated.


I've also tried this...

PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1

but then all I get in Cells(2,10) is...

=(35*(5-H


Now if I manually plugged in the string into Sub2 like this...

Cells(2, 10).Formula = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1

then it works.

So I need to find a way to put my string into a variable form that allows it to be used later and properly come together to create the PROPER final result in Cells(2,10) like this...

=(35*(5-H2))-H1


Do I need to dim PublicVariable as something besides string?

Am I being clear? Let me know if I need to clarify anything.

Thanks!

[SamT]

[vba]
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
[/vba]

First part: =(35*(5-H
Second Part: LastRow2
Third part: ))-H
Fourth Part: LastRow1

If LastRow 2 = 5
If LastRow1 = 10

Equivalent to (35*(5-H5)) - H10

It is sometimes helpful to format your code like this
[vba]
Cells(2, 10).Formula = "=(35*(5-H" _
& LastRow2 _
& "))-H" _
& LastRow1

[/vba]

So you can quickly see that only the strings are enclosed in quotes.

[jtl]

This sample code worked on my system:

Sub sample()

Dim LastRow1 As Integer, LastRow2 As Integer, PublicVariable As String

LastRow2 = 10
LastRow1 = 2

PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
Cells(2, 10).Formula = PublicVariable

End Sub

[Mcygee]

Thanks for the replies!

I found the underlying issue after seeing what you guys posted. JTL's example works because everything is in one Sub. I've attached an example of mine. It is basically just an edited version of JTL's example but changed to how my real worksheet is set up. The problem with mine this...

In Sub1 I have...

[vba]Public PublicVariable As String

Sub sample1()

PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1

sample2

End Sub
[/vba]
In Sub2 I have...

[vba]Sub sample2()

Dim LastRow2 As Integer
Dim LastRow1 As Integer

LastRow2 = 10
LastRow1 = 2

Cells(2, 10).Formula = PublicVariable

End Sub
[/vba]
This doesn't work because it tries to find what LastRow1 and LastRow2 are in Sub1 when I specified PublicVariable =

Now if I change Sub1 to this...

[vba]Sub sample1()

Dim LastRow2 As Integer
Dim LastRow1 As Integer

LastRow2 = 10
LastRow1 = 2

PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1

sample2

End Sub
[/vba]
And Sub2 to this...

[vba]Sub sample2()

Cells(2, 10).Formula = PublicVariable

End Sub[/vba]
It all works fine.


So I guess what I need to do is find out if there is a way to move PublicVariable into Sub2 exactly as it is, and not try to calculate what LastRow1 and LastRow2 is before they are even given a value.

I could work around it by doing a loop inside Sub2 and have it change PublicVariable each time it loops to what I need. But what I was after is somewhat of a more organized approach and have Sub1 control it all like this....

[vba]Public PublicVariable As String

Sub sample1()

PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1

sample2

PublicVariable = "=(10*(3-B" & LastRow2 & "))-B" & LastRow1

sample2

PublicVariable = "=(5*(4-C" & LastRow2 & "))-C" & LastRow1

sample2

End Sub[/vba]
Basically changing my variable in Sub1 and running Sub2 afterward each time to fill in data in a new location.

So anybody have any ideas on moving the formula...

"=(35*(5-H" & LastRow2 & "))-H" & LastRow1

but make it calculate the whole thing only when I call PublicVariable in Sub2 not in Sub1?

[jtl]

I guess the quickest way to do this is to declare LastRow1 and LastRow2 as public variables. Like this:

[VBA]
Public PublicVariable As String
Public LastRow2 As Integer
Public LastRow1 As Integer
Sub sample1()
LastRow2 = 10
LastRow1 = 2
PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1
sample2
End Sub
[/VBA]

[VBA]
Sub sample2()
Cells(2, 10).Formula = PublicVariable
End Sub
[/VBA]

Then you can simple reassign LastRow1 and LastRow2 either in sub1 or sub2.

[Mcygee]

Well unfortunately using that method you must keep LastRow1 and LastRow2 in Sub 1 for it to work because they have to be given a value before PublicVariable is given it's string.

Here is the workaround I'm going to use for now unless somebody knows a better way. Marking this thread solved, but add more info if you'd like.

I'm going to add another sub that will create the PublicVariable string. In my example it looks pointless because there is only one PublicVariable, but in my real worksheet I'll use an if then/else if to change it to different strings based on what another variable is. Sample is attached but here it is as well...

Sample1 Macro
[VBA]Public PublicVariable As String
Public LastRow2 As Integer
Public lastrow1 As Integer


Sub sample1()

sample2

End Sub
[/VBA]

Sample2 Macro
[VBA]Sub sample2()

LastRow2 = 10
lastrow1 = 2

sample3

Cells(2, 10).Formula = PublicVariable

End Sub[/VBA]

Sample3 Macro
[VBA]Sub sample3()

PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & lastrow1

End Sub
[/VBA]

Also just to be clear I'm not moving LastRow1 or LastRow2 to Sample 1 because in my worksheet it is calculated each time differently in Sample2 and has to remain there for it all to work.

[Bob Phillips]

[vba]


Sub sample1()

Dim LastRow1 as long
Dim LastRow2 as long

LastRow2 = 10
LastRow1 = 2

PublicVariable = "=(35*(5-H" & LastRow2 & "))-H" & LastRow1

call sample2 (PublicVariable)

End Sub

Sub sample2(ByVal MyFormula as string)

Cells(2, 10).Formula = MyFormula

End Sub
[/vba]

[Mcygee]

That does work XLD, but unfortunately in my real worksheet LastRow1 and LastRow2 must be calculated in Sample2. It's a variable that you can't calculate until some data is pulled in a sorted, so I can't just move it into Sample1.

Only workaround I see right now is to have a separate module or sub that Sample2 can call to find out what PublicVariable is. Not a big deal, but it would be nice just to tell Sample2 what string to use without calculating the LastRow1 and LastRow2 variables right then.

[SamT]

Sounds like a case for Get, Set, and Let procedures.

[Bob Phillips]

[vba]
Sub sample1()

Dim LastRow1 As Long
Dim LastRow2 As Long

LastRow2 = 10
LastRow1 = 2

PublicVariable = 15
MsgBox PublicVariable

Call sample2 (PublicVariable)
MsgBox PublicVariable

End Sub

Sub sample2(ByRef myValue As Double)

myValue = 17

End Sub
[/vba]

[SamT]

Mcygee,

I really don't understand.

Do LastRows 1 and 2 have different values in Subs 1 and 2?

Or:

Does PublicVariable need to have different formulas in the two subs?

SamT

[jtl]

I think this is a case of what comes first, the chicken or the egg

If I understand correctly, PublicVariable needs LastRow1 and LastRow2 to to complete the string in sub1. However, the value of LastRow1 and LastRow2 is derived from sub2, which comes after the fact. Well, see if this one works for you.

[VBA]
Public PublicVariable1 As String, PublicVariable2 As String
Sub sample1()
PublicVariable1 = "=(35*(5-H"
PublicVariable2 = "))-H"
Call sample2
End Sub
Sub sample2()
Dim LastRow2 As Integer
Dim LastRow1 As Integer
' Get actual value
LastRow2 = 10
LastRow1 = 2
Cells(2, 10).Formula = PublicVariable1 & LastRow2 & PublicVariable2 & LastRow1
End Sub
[/VBA]

[Mcygee]

JTL you're a genius.

That should work fine for all the different equations I need.

Thank you everybody who took at shot at this. You all had good ideas, but unfortunately my issue was a little confusing and hard to explain so I know it was hard to figure out exactly what I was asking for.

Great people on these forums.