PDA

View Full Version : [SOLVED] How to DIM script itself?



thk12205
03-01-2018, 06:30 PM
Excel Version: MS Office Professional Plus 2013

I am writing a macro that uses the same function "Cells(firstRow + j - 1, firstColFinal)" multiple times.
firstRow and firstColFinal are dynamic values, and j is from a For loop.

Is there a way to define a part of the script as "DIM X as Script" and simply put "X" in the code instead of this longish Cell(#,A)?
Or would I need to type this out each time I need to use it?

georgiboy
03-02-2018, 04:31 AM
Maybe look at using something like:


With Cells(firstRow + j - 1, firstColFinal)
.Font.Bold = True
.Interior.Color = vbRed
End With

thk12205
03-02-2018, 09:36 AM
Not quite what I'm looking for, but thank you.

To clarify, what I'm going for is for the vba script itself. Just to visually clean it up.

Currently, the Module looks something like this:



Sub Button()
firstRow = 1
firstColFinal = 2

for j = 1 to 10
If Cells(firstRow + j - 1, firstColFinal).value < 3 & Cells(firstRow + j -1, firstColFinal) => 1 Then
Cells(firstRow + j - 1, firstColFinal).value = Cells(firstRow + j - 1, firstColFinal).value + 1
Else
Cells(firstRow + j - 1, firstColFinal).value = 3
End If
End Sub


What I'd like to go for:



Sub Button()
firstRow = 1
firstColFinal = 2

Dim X as Script
X = Cells(firstRow + j - 1, firstColFinal).value

for j = 1 to 10
If X < 3 & X => 1 Then
X = X + 1
Else
X = 3
End If
End Sub


The intent would be to clean up the macro and make it easier to keep track of each idea in the module.

georgiboy
03-02-2018, 10:16 AM
Sub Button()
firstRow = 1
firstColFinal = 2

For j = 1 To 10
With Cells(firstRow + j - 1, firstColFinal)
If .Value < 3 & .Value >= 1 Then
.Value = .Value + 1
Else
.Value = 3
End If
End With
Next j
End Sub
or

Sub Button()
Dim rng As Range
firstRow = 1
firstColFinal = 2

For j = 1 To 10
Set rng = Cells(firstRow + j - 1, firstColFinal)
If rng.Value < 3 & rng.Value >= 1 Then
rng.Value = rng.Value + 1
Else
rng.Value = 3
End If
Next j
End Sub

thk12205
03-02-2018, 10:24 AM
Ahh I see!

Thank you georgiboy. :yes

georgiboy
03-02-2018, 10:47 AM
You are welcome.

When you put it’s not what you are looking for I thought, it is but you just don’t know it yet.

thk12205
03-02-2018, 01:59 PM
Haha, true. Thanks for spelling it out.

Out of curiousity, would VBA be able to input raw code into a defined variable like I'd previously thought?

georgiboy
03-02-2018, 07:25 PM
Not that I know of, if it’s a big piece of code then you could write your own functions and call them from a sub.