CODEWITHSUNDEEP
excelvbaformat
Elis
Elis

Reputation: 41

Excel VBA macro to format cells

I've written an Excel sub that includes duplicate code, where the active range is formatted in a particular way, but I don't think it's possible to combine these cases into a loop. Is it possible to write a separate sub/function that takes an input range, formats it, and outputs that formatted range, like python would with definable functions?

EDIT: Here's some barebones pseudocode

function Colour_and_Merge(Input Range)
    Formatted range = *Input Range with text and background colour changed*

    Colour_and_Merge = Formatted Range
end function

sub Main_Code()

for y = 1 to 3
    if y <> 1
        Colour_and_merge(Range(Cells(1,y),Cells(5,y)))
    end if

Colour_and_Merge(Seperate_Range)

end sub

Upvotes: 1

Views: 1119

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You would do that like below.

Option Explicit

Public Sub ColorAndMerge(ByVal InputRange As Range)
    With InputRange
        .Interior.Color = vbRed  ' format range background red.
        .Font.Bold = True        ' format font bold
        'what ever you like to do with that range put it here
    End With
End Sub


Public Sub MainCode()
    Dim y As Long
    For y = 1 To 3
        If y > 1 Then
            ColorAndMerge Range(Cells(1, y), Cells(5, y)) 'make sure you specify in which workbook and worksheet your `Range` and `Cells` objects are!
        End If
    Next y

    ColorAndMerge SeperateRange
End Sub

Note that you don't need a Function but a Sub. It does not make any sense to return the range as it is the same range you sent in as InputRange. So for example if you call ColorAndMerge SeperateRange in your main procedure you don't need ColorAndMerge to return anything because it would only return the same as SeperateRange which you already know.

So if your main code does the following

Public Sub MainCode()
    Dim SeperateRange As Range
    Set SeperateRange = Range(Cells(1, y), Cells(5, y))

    ColorAndMerge SeperateRange 'if you call the procedure

    'here `SeperateRange` will be the formatted range. There is no need to return it in a function, the SeperateRange variable is just a reference to the real range in the worksheet that already got formatted by ColorAndMerge 
End Sub

Also note that calling procedures/subs has to be without parenthesis while functions are called with parenthesis:

ColorAndMergeSub SeperateRange    ' correct
ColorAndMergeSub (SeperateRange)  ' wrong! ... this syntax exists but does something entirely differnt than the first one
ReturnValue = ColorAndMergeFunction(SeperateRange)  ' correct
ReturnValue = ColorAndMergeFunction SeperateRange   ' wrong!

Upvotes: 1

Related Questions