LinuC（Linux技術者認定資格）＆リナックスサーバ構築設定事例

【VBA】セル範囲を配列化する処理の関数

Option Explicit

'▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲
'■【メイン処理】
'▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼
Sub main()

    Dim myRange As Range
    Dim F_Array() As String
    Erase F_Array()         '■ 配列初期化■

    Set myRange = ActiveSheet.Range("B2", "C20")    '■ セル範囲を格納■

    '***************************************
    '■【セル範囲を配列化する処理】
    '***************************************
    Call Func_03(myRange, F_Array())    '■ 関数呼び出し■
    Cells(22, "B") = F_Array(0)        '戻り値をセルに反映
    Cells(23, "B") = F_Array(1)        '戻り値をセルに反映
    Cells(24, "B") = F_Array(2)        '戻り値をセルに反映
    '***************************************

    Set myRange = Nothing

'★ myRange.Cells.SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(255, 128, 128)

End Sub
'▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼

'************************************************
'■【セル範囲を配列化する処理】
'************************************************
Function Func_03(R_Range As Range, R_Array() As String)

    Dim myArray As Variant
    myArray = R_Range

Dim Row_max, Column_max As Long '■ 行／列の変数■

    Row_max = UBound(myArray, 1)    '■ 行要素番号の最大値を取得(第二引数は省略可)■
    Column_max = UBound(myArray, 2) '■ 列要素番号の最大値を取得■

    Dim Row_i, Column_i As Long     '■ 行／列のカウント用変数■
    Dim Array_1, Array_2 As String '■ １列／２列の文字列を格納する変数■
    Dim Array_S As String           '■ １列＋２列を繋げて格納する変数■
    ReDim R_Array(Row_max - 1)

    '---------------------------------------
    '■ 配列の中身を確認
    '---------------------------------------
    For Row_i = 1 To Row_max
        Array_1 = myArray(Row_i, 1)
        Array_2 = myArray(Row_i, 2)
        Array_S = Array_1 & Array_2
        Cells(Row_i, "N") = Array_S '■ セルに転記する■
        R_Array(Row_i - 1) = Array_S    '■ 戻り値として配列に格納■
    Next
    '---------------------------------------

    Set myArray = Nothing

End Function
'************************************************

PR

【VBA】最終行の取得方法（関数有り／関数無し）

Option Explicit
'▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼
Sub main()

    Dim Fa As Long      '関数の引数１／戻り値１
    Dim Fb As Long      '関数の引数２／戻り値２
    Dim Fc As Long      '関数の引数３／戻り値３

    Fa = 0 　　　　　'ゼロクリア
    Fb = 0 　　　　　'ゼロクリア
    Fc = 0 　　　　　'ゼロクリア

    '*** *** *** *** *** *** *** *** *** ***
    '■ 最終行の取得方法【関数有り】
    '*** *** *** *** *** *** *** *** *** ***
      Call Func_02(Fa, Fb, Fc) 　　'関数呼び出し
      Cells(1, "K") = Fa    　　　'戻り値１を指定
      Cells(2, "K") = Fb    　　　'戻り値２を指定
      Cells(3, "K") = Fc    　　　'戻り値３を指定
    '*** *** *** *** *** *** *** *** *** ***

    '--- --- --- --- --- --- --- --- --- ---
    '□ 最終行の取得方法【関数無し】
    '--- --- --- --- --- --- --- --- --- ---
      Dim Etc_Row As Long    　'不要な行数用変数
      Dim Last_Row As Long 　'最終行数用変数

      Etc_Row = 1    　　　'不要な行数の初期値
      Last_Row = 0 　　　'最終行数の変数をゼロクリア

      Last_Row = Cells(1, "A").End(xlDown).Row       '最終行の数を取得
      Last_Row = Last_Row - Etc_Row    　'最終行数から不要な行数をマイナス

      Cells(1, "G") = Last_Row 　　　　　'結果を反映
      Cells(2, "G") = Etc_Row    　　　　　'結果を反映
      Cells(3, "G") = Cells(1, "A").End(xlDown).Row 　'結果を反映
    '--- --- --- --- --- --- --- --- --- ---

End Sub
'▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼

'************************************************
Function Func_02(Ra As Long, Rb As Long, Rc As Long)

    Dim Result_01 As Long 　'戻り値用変数１
    Dim Result_02 As Long 　'戻り値用変数２
    Dim Result_03 As Long 　'戻り値用変数３

    Result_01 = Ra 　　　　　'引数１を格納
    Result_02 = Rb 　　　　　'引数２を格納
    Result_03 = Rc 　　　　　'引数３を格納

    '--- --- --- --- --- --- --- --- --- ---
    Dim i As Long                   'ループカウンター用変数
    i = 2 　　'ループ開始行を設定
    '--- --- --- --- --- --- --- --- --- ---

    '--- --- --- --- --- --- --- --- --- ---
    Do While Cells(i, "A") <> ""    　　'空欄になるまでループ

i = i + 1 　　　　　'ループカウントアップ
Result_01 = Result_01 + 1 　'行数を加算

Loop
'--- --- --- --- --- --- --- --- --- ---

    Result_02 = i - Result_01
    Result_03 = i

    '--- --- --- --- --- --- --- --- --- ---
    '□ 戻り値が複数ある場合の書き方
    '--- --- --- --- --- --- --- --- --- ---
    Ra = Result_01 　　'戻り値を格納１
    Rb = Result_02 　　'戻り値を格納２
    Rc = Result_03 　　'戻り値を格納３
    '--- --- --- --- --- --- --- --- --- ---

End Function
'************************************************

【VBA】簡単なエクセルマクロでの関数の記述例

※以下のような記述で関数の呼び出しと戻り値の利用が可能となる

=== === === === ===【以下は関数の記述例】=== === === === ===
Option Explicit
'▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼
Sub main()

    Dim Fa As Long
    Dim Fb As Long
    Dim Fc As Long

    Fa = 0
    Fb = 0
    Fc = 0

    '--- --- --- --- --- --- --- ---
    '□ 値をセット
    '--- --- --- --- --- --- --- ---
     Fa = 2
     Fb = 3
     Fc = 10
    '--- --- --- --- --- --- --- ---

    '*** *** *** *** *** *** *** *** *** ***
    '■ 関数の結果をセルに反映
    '*** *** *** *** *** *** *** *** *** ***
     Cells(1, "A") = Func_01(Fa, Fb, Fc)
    '*** *** *** *** *** *** *** *** *** ***

End Sub
'▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼▲▼

'************************************************
Function Func_01(Ra As Long, Rb As Long, Rc As Long)

Dim Result_01 As Long

    MsgBox Ra
    MsgBox Rb
    MsgBox Rc

    '--- --- --- --- --- --- --- ---
    '□ 引数を使って計算
    '--- --- --- --- --- --- --- ---
     Result_01 = Ra + Rb + Rc
    '--- --- --- --- --- --- --- ---

    '*** *** *** *** *** *** ***
    '■ 結果を戻り値とする
    '*** *** *** *** *** *** ***
     Func_01 = Result_01
    '*** *** *** *** *** *** ***

End Function
'************************************************

【LPIC-3 Specialty LPI-304 合格】

【報告】
　・2016-09-30（金）
LPIC-3 Specialty LPI-304　Virtualization & High Availability
認定されました！

【得点】
　・640点

【感想】
　・黒本の問題と類似している問題が多い為、回答に迷わずクリア（余裕）

【その他】
　・次の試験を模索中・・・

【試験に役立つアドバイス】
　・問題は黒本、知識は仮想化関連の書籍を一冊熟読を推奨☆

【Linux】サーバーの状態確認（６）

疎通確認
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# ping 192.168.0.10
Pinging 192.168.1.10 with 32 bytes of data:
Reply from 192.168.1.10: bytes=32 time<10ms TTL=128
Reply from 192.168.1.10: bytes=32 time=20ms TTL=128
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

経路情報を表示する
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# traceroute 192.168.1.1
traceroute to itpro.nikkeibp.co.jp (202.214.174.88), 30 hops max, 38 byte packets
1 192.168.1.1 (192.168.1.1) 0.386 ms 0.363 ms 0.322 ms
2 218.xxx.xxx.xxx (218.xxx.xxx.xxx) 3.426 ms 4.137 ms 5.710 ms
3 218.xxx.xxx.xxx (218.xxx.xxx.xxx) 7.711 ms 3.820 ms 3.123 ms
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

DNSサーバーへ問合せ（名前解決テスト）
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# nslookup 192.168.0.1
サーバー: xxxxx.example.com
Address: 192.168.0.11

名前: XXX.example.com
Address: 192.168.0.1
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

【Linux】サーバーの状態確認（１）

メモリやCPUの使用状況（5秒間隔で2回チェック）
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# vmstat 5 2
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id wa st
0 0 300 51472 166104 116804 0 0 0 0 2 2 0 0 100 0 0
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

現在動作中のプロセスを表示する
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# ps aux
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root         1 0.0 0.2 19360 1388 ?        Ss   Mar08   0:04 /sbin/init
root         2 0.0 0.0      0     0 ?        S    Mar08   0:01 [kthreadd]
root         3 0.0 0.0      0     0 ?        S    Mar08   0:00 [migration/0]
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

物理メモリ，仮想メモリの使用状況を表示する
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# free
             total       used       free     shared    buffers     cached
Mem:        502360     450904      51456          0     166104     116768
-/+ buffers/cache:     168032     334328
Swap:      1048568        300    1048268
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

ハードディスクのパーテション情報などを表示する
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# df -h
Filesystem            Size Used Avail Use% マウント位置
/dev/sda3             6.7G 3.4G 3.0G 54% /
tmpfs                 246M     0 246M   0% /dev/shm
/dev/sda1             248M   37M 199M 16% /boot
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

【Linux】サーバーの状態確認（５）

プロセスの状態表示
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# ps aux
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root         1 0.0 0.2 19360 1388 ?        Ss   Mar08   0:04 /sbin/init
root         2 0.0 0.0      0     0 ?        S    Mar08   0:01 [kthreadd]
root         3 0.0 0.0      0     0 ?        S    Mar08   0:00 [migration/0]
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

パケットフィルタリングの状態表示
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# iptables -L
DROP       all -- anywhere             255.255.255.255
DROP       all -- anywhere             224.0.0.1
ACCEPT     all -- 192.168.0.0/24       anywhere
ACCEPT     all -- anywhere             anywhere            state RELATED,ESTABLISHED
ACCEPT     tcp -- 172.20.0.0/24        anywhere            tcp dpt:ssh state NEW
ACCEPT     udp -- 172.20.0.0/24        anywhere            udp dpts:snmp:snmptrap
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

TCPラッパー (/etc/hosts.allow) によるアクセス制限①
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# cat /etc/hosts.allow
sshd: 192.168.0.16
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

TCPラッパー (/etc/hosts.allow) によるアクセス制限②
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# cat /etc/hosts.allowdeny
ALL: ALL
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

ホスト名とIPアドレスの対応付け
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

【Linux】サーバーの状態確認（４）

サービスの自動起動 on/off をリスト表示
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# chkconfig --list
NetworkManager 0:off 1:off 2:off 3:off 4:off 5:off 6:off
abrt-ccpp      0:off 1:off 2:off 3:off 4:off 5:off 6:off
abrtd          0:off 1:off 2:off 3:off 4:off 5:off 6:off
acpid          0:off 1:off 2:on 3:on 4:on 5:on 6:off
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

サービスの状態確認①
--- --- --- --- --- --- --- --- --- --- --- --- ---
# cat /etc/services
# service-name port/protocol [aliases ...]   [# comment]
tcpmux          1/tcp                           # TCP port service multiplexer
tcpmux          1/udp                           # TCP port service multiplexer
rje             5/tcp                           # Remote Job Entry
rje             5/udp                           # Remote Job Entry
echo            7/tcp
echo            7/udp
--- --- --- --- --- --- --- --- --- --- --- --- ---

サービスの状態確認②
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# service --status-all
NetworkManager は停止しています
abrt-ccpp hook is not installed
abrt-dump-oops は停止しています
abrtd は停止しています
acpid (pid 1909) を実行中...
atd (pid 2221) を実行中...
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

【Linux】サーバーの状態確認（３）

NICの設定
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# ifconfig -a
eth0      Link encap:Ethernet HWaddr 00:0C:29:28:80:7C
          inet addr:192.168.0.190 Bcast:192.168.0.255 Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
          RX packets:9084557 errors:0 dropped:0 overruns:0 frame:0
          TX packets:5672669 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:1702563285 (1.5 GiB) TX bytes:1512798167 (1.4 GiB)
eth1      Link encap:Ethernet HWaddr 00:0C:29:28:80:86
          inet addr:172.20.0.10 Bcast:172.20.0.255 Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
          RX packets:0 errors:0 dropped:0 overruns:0 frame:0
          TX packets:294575 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:0 (0.0 b) TX bytes:38004181 (36.2 MiB)
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

DNSの設定
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# cat /etc/resolv.conf
# Generated by NetworkManager
search cloud.local
nameserver 127.0.0.1
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

ネットワークの状態を表示する①
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# netstat -r
Kernel IP routing table
Destination     Gateway         Genmask         Flags   MSS Window irtt Iface
172.20.0.0      *               255.255.255.0   U         0 0          0 eth1
192.168.0.0     *               255.255.255.0   U         0 0          0 eth0
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

ネットワークの状態を表示する②
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# route
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
172.20.0.0      *               255.255.255.0   U     0      0        0 eth1
192.168.0.0     *               255.255.255.0   U     0      0        0 eth0
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

【Linux】サーバーの状態確認（２）

インストール済みパッケージを表示
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# rpm -qa
xorg-x11-drv-acecad-1.5.0-6.el6.x86_64
sg3_utils-1.28-4.el6.x86_64
net-snmp-utils-5.5-44.el6.x86_64
libXi-1.6.1-3.el6.x86_64
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

稼動状況をタイムリーに表示（「Shift+o」でソート）
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# top
top - 15:32:49 up 191 days, 6:47, 2 users, load average: 0.00, 0.00, 0.00
Tasks: 102 total,   1 running, 101 sleeping,   0 stopped,   0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem:    502360k total,   452400k used,    49960k free,   166108k buffers
Swap: 1048568k total,      300k used, 1048268k free,   118104k cached

PID USER      PR NI VIRT RES SHR S %CPU %MEM    TIME+ COMMAND
11477 root      20   0 15028 1096 840 R 1.9 0.2   0:00.03 top
    1 root      20   0 19360 1388 1140 S 0.0 0.3   0:04.02 init
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---

SELinuxが有効か無効かを調べる
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
# getenforce
Enforcing
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---