Forum Discussion

CPBExcelnovice's avatar
CPBExcelnovice
Copper Contributor
Oct 18, 2022

VBA Calling a Macro - So basic and yet difficult for a novice!

I have a spreadsheet that has a variable number of rows of data and I want to select the area and set it as the new print area.   After looking around I came across the following code which can run...
excel
Macros and VBA
CPBExcelnovice's avatar
CPBExcelnovice
Copper Contributor
Oct 24, 2022
Thanks for your help. I messed around with the code for a while but couldn't quite force it to cooperate!

The code puts lines around all of the cells, rather than around the border of the selected range.

I changed
ActiveSheet.UsedRange.Borders.LineStyle = xlContinuous
to
ActiveSheet.UsedRange.BorderAround LineStyle:=xlContinuous
which worked.
But I then got clever and tried to add a line weight by adding
, Weight:=xlThick
but the code was rejected.



SnowMan55's avatar
SnowMan55
Bronze Contributor
Oct 25, 2022

CPBExcelnovice 

That should work, if you are adding the comma and named Weight parameter on the same line.  If you are splitting it to another line, you need to include continuation characters (a space and underscore) at the end of the first line:

    ActiveSheet.UsedRange.BorderAround LineStyle:=xlContinuous _
            , Weight:=xlThick
    '...I prefer that style of splitting, but others prefer:
    ActiveSheet.UsedRange.BorderAround LineStyle:=xlContinuous, _
            Weight:=xlThick

 You can continue a statement on up to 10 lines, IIRC; include continuation characters on all but the last line.

  • CPBExcelnovice's avatar
    CPBExcelnovice
    Copper Contributor
    Oct 25, 2022
    This will seem like the dumbest question so far, but it has me baffled. I have a sub named "DynamicRange()" which determines the last row and column (so that I can dynamically determine the range that has data). It finishes up selecting the range as per the following line of code:

    'Select Range
    sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

    What I cannot work out is how to return that range to my main VBA program so that I can set the borders. My static code is:
    Sheets("Report").Range("C2:L80").BorderAround _
    ColorIndex:=25, Weight:=xlThick

    To dynamically alter the last column and row I tried the following:

    Sheets("DIT Report").Range(StartCell, sht.Cells(LastRow, LastColumn)).BorderAround _
    ColorIndex:=25, Weight:=xlThick

    It didn't work presumably because the value of the variables are not transferring from the sub routine to the main code.
    What am I missing here?
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 25, 2022

      CPBExcelnovice 

      Instead of declaring LastRow and LastColumn in the macro itself, declare them at the top of the module, above all procedures (subs) and functions:

       

      Dim LastRow As Long

      Dim LastColumn As Long

       

      Sub ...

       

      If you have macros/procedures in more than one module that should use LastRow and LastColumn, use

       

      Public LastRow As Long

      Public LastColumn As Long

Resources